The answer is 'depends'.
1.) Depends upon how big your data set is going to get
2.) Depends on how the data is going to be retrieved (through occasional big queries, or short small ones.
3.) Depends on the type of data the fields are (null Integers don't take up a lot of room, for example, so no reason to worry about them being empty).
I've gone different ways, depending on the above
In general, though, if you're 30+ columns are a normalized set of data, and the 'sometimes' values aren't that long, then you really can't go wrong with KISS. It's the best way to ensure the most long-term variability of use.
If you know that you're rarely going to do 'long batch queries', and this is more for a fast-retrieval of single records, then Option 2 isn't a bad choice. Option 3 even works as long as your '5 or so fields' are the one you match on most often.
A variation on Option 3 that I've used *once* in my life, for a very specific design, is to create two tables, with a one-to-one required relationship. I put the smaller, searchable subset in table one, and the rest (mostly message headers, various textual fields, etc...) into the other table.
That served two purposes:
1.) The main table was small, compact, and highly indexed, so searching, exporting, debugging, etc, was a snap.
2.) The second table, after some period of time, was determined to not be necessary, except for two fields, so it was easy to move the two fields to main table and just drop the second one.
Hope some of that's useful
Trek
In reply to Re: (OT) Database design: omitted partially filled fields.
by TrekNoid
in thread (OT) Database design: omitted partially filled fields.
by BUU
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |