2

I am developing a site that will save data into an SQL database that describes events. One of the events being stored in the database has several variations. Some of these variations are fairly uncommon and i am trying to limit the number of blank fields in the records. I am debating between these two styles:

option A:

report_primary_key
.
.
.
standard_info
.
.(17 fields)
.
uncommon_values (8 fields)

or B:

report_primary_key
.
.
.
standard_info
.
.(17 fields)

second table

report_primary_key
uncommon_values (3 fields)

Option A would have all possible information stored in the one table but 95% of the records would have 10+ null values (out of 25). Option B would have a trimmed version of the main table. Since the supplemental table would use the same primary key I would just need to search the supplemental tables for up to three occurrences of that key to include in the output.

So, that leaves me with my questions. Is the extra query worth saving space in the table? Any other thoughts as to one of these being a better idea than the other?

Thanks for the help/advice.

Aaron Luman
  • 635
  • 1
  • 10
  • 29

2 Answers2

3

As a general rule, if you have "uncommon" events that occur rather rarely, e.g. less than 5% or 10% of the time, then I would definitely put those extra fields into a separate table.

It makes for a cleaner design for the 90-95% case without all those empty fields, and the little overhead you have from creating an extra table with an extra primary key is neglible.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • awesome, thanks for confirming that for me. I'm still new at this and didn't want to do something totally stupid trying to save on space and such. – Aaron Luman Nov 07 '09 at 10:40
  • And just to confirm, there won't be any problem if the supplemental table uses the same values in its primary key as the main table? – Aaron Luman Nov 07 '09 at 10:48
  • No, no problem - it's a totally separate primary key on the second table2, so those don't interfere with one another. – marc_s Nov 07 '09 at 12:02
0

Two tables -- this is a "standard" example of supertype/subtype relationship. See also this question: and the matching model (picture). In your example you have a REPORT and a SPECIAL_REPORT which is a type of a report.

Community
  • 1
  • 1
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71