3

I have a table (X) with 10 columns, 6 of which are nullable foreign keys (bigint data type) and in every row 5 of them will be NULL.

SOLUTION: Divide table (X) into 2 tables: (X) and (XType) so that (X) includes a big int column (not foreign key) for insert one of six ID and a XType_id column for determining types of 6 data.

Is this solution optimal or the first table with 10 columns is better?

David Ferenczy Rogožan
  • 23,966
  • 9
  • 79
  • 68
FS-DBA
  • 41
  • 4
  • Check this question, too: [Supertype-subtype database design](http://stackoverflow.com/questions/12255731/supertype-subtype-database-design) – ypercubeᵀᴹ Dec 16 '12 at 08:36
  • And this one: [Defining multiple foreign keys in one table to many tables](http://stackoverflow.com/questions/4050784/defining-multiple-foreign-keys-in-one-table-to-many-tables/4051523#4051523) – ypercubeᵀᴹ Dec 16 '12 at 08:38

2 Answers2

3

I prefer your first option.

When you have those 6 individual foreign key columns, then you can enforce referential integrity by having actual foreign key constraints to those 6 referenced tables.

If you have a super-smart approach with a single ID and an ID_Type, you cannot enforce referential integrity anymore.

The benefit of being able to truly enforce referential integrity for me far outweighs the "benefit" of having just a single ID column; having a few columns with NULL values isn't bad

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

Marc_s's answer is absolutely correct, let me just add one more tidbit: you are unlikely to save space (and by extension, improve caching) with your second approach. MS SQL Server will encode the "NULL-iness" of fields in a bit-field and 6 NULL-able columns can be represented by a single byte. The "type" field in your second approach will not be less than a byte.

BTW, if your FKs were of varying types, your second approach would require storing the FK values as the "lowest common denominator" type (probably string) and doing the type conversions manually, so you'd also lose the integrity of domain.

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167