Re: (OT) Database design: omitted partially filled fields.

I would go for Option 1! KISS. I don't see any problem having 30 cols in a RDBMS.

Option 1
+ fast, you only need one select and one update to write or read one record
+ easy to understand for everybody

Option 2 + 3

- slow
- not intuitive
If you have some required fields and 0...many optional fields for your records, you could also write all the optional field serialised in one row, but then you can't access the optional fields simple with sql.

ciao, knoebi

Comment on Re: (OT) Database design: omitted partially filled fields.

Replies are listed 'Best First'.
Re^2: (OT) Database design: omitted partially filled fields. by exussum0 (Vicar) on Jul 21, 2004 at 15:24 UTC
You seem to have forgotten the bad problems with issue 1 and the good of options 2 and 3. 1. If you have 1e6 rows already, and decide to introduce a new column, that's 1e6 null entries you have to create. It takes up space depending on the implementation. If your table is thick, it may be a sign of poor organization. BUU suggested 2 tables, 1 for required, 1 for unrequired. It could be grouped even further. 1 for primary info, 1 for say, addresses information (if it is some of the data) and so forth... 2. It's probably negligible on what's being done. If you want to update all data, yes, it's slow. If you want to select, it may not be bad. It all depends on the frequency of operations. And tell me, what database IS intuitive? W/o an ERD, looking at a db is a pain unless someone explains things. Bart: God, Schmod. I want my monkey-man.	[reply]

Replies are listed 'Best First'.

Re^2: (OT) Database design: omitted partially filled fields.
by exussum0 (Vicar) on Jul 21, 2004 at 15:24 UTC

1. If you have 1e6 rows already, and decide to introduce a new column, that's 1e6 null entries you have to create. It takes up space depending on the implementation.

If your table is thick, it may be a sign of poor organization. BUU suggested 2 tables, 1 for required, 1 for unrequired. It could be grouped even further. 1 for primary info, 1 for say, addresses information (if it is some of the data) and so forth...

2. It's probably negligible on what's being done. If you want to update all data, yes, it's slow. If you want to select, it may not be bad. It all depends on the frequency of operations.

And tell me, what database IS intuitive? W/o an ERD, looking at a db is a pain unless someone explains things.

Bart: God, Schmod. I want my monkey-man.

[reply]