3

If you have a design of a "master table" and several "subtables", is it better* that the master table "link" to each of the sub tables, the subtables link to the master, or for bi-directional links be established? The data present in the subtables COULD be not present (i.e. it might not be relevant to the particular entry, so a linked entry in the subtable won't be created). Basically data will be related in a 1-to-(zero or one) fashion. There will never be a reason to link between the subtables WITHOUT involving the master table.

Specifically I'm concerned about better* with respect to MS SQL Server (2008R2+)

Example Design 1: Master Table links to Sub-tables

CREATE TABLE tMaster (
  PK_TM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,IntrinsicField1 datetime NOT NULL
  ,IntrinsicField2 int NOT NULL
  ,IntrinsicField3 nvarchar(255) NULL
  ,index_subA int NULL --index created on this value. links to PK_SA of appropriate entry if it exists
  ,index_subB int NULL --index created on this value
  ....
  ,index_subM int NULL --index created on this value
)
CREATE TABLE tSubA
  PK_SA int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,ExtraField1 int NOT NULL
  ,ExtraField2 varchar(24) NOT NULL
) 
CREATE TABLE tSubB
  PK_SB int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,ExtF1 NUMERIC(5,3) NOT NULL
  ,ExtF2 NUMERIC(5,3) NOT NULL
) 
CREATE TABLE tSubM
  PK_SM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,EField1 int NOT NULL
  ,EField2 nvarchar(100) NOT NULL
)

Example Design 2: Sub-Tables link to Master Table

CREATE TABLE tMaster (
  PK_TM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,IntrinsicField1 datetime NOT NULL
  ,IntrinsicField2 int NOT NULL
  ,IntrinsicField3 nvarchar(255) NULL
)
CREATE TABLE tSubA
  lMaster_ID int NOT NULL PRIMARY KEY CLUSTERED -- value of PK_TM of cross-linked entry
  ,ExtraField1 int NOT NULL
  ,ExtraField2 varchar(24) NOT NULL
)
CREATE TABLE tSubB
  lMaster_ID int NOT NULL PRIMARY KEY CLUSTERED
  ,ExtF1 NUMERIC(5,3) NOT NULL
  ,ExtF2 NUMERIC(5,3) NOT NULL
  ,Master_ID int NOT NULL --index created on this value. links to PK_TM of appropriate entry
) 
CREATE TABLE tSubM
  lMaster_ID int NOT NULL PRIMARY KEY CLUSTERED
  ,EField1 int NOT NULL
  ,EField2 nvarchar(100) NOT NULL
)

Example Design 3: Bidirectional links

CREATE TABLE tMaster (
  PK_TM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,IntrinsicField1 datetime NOT NULL
  ,IntrinsicField2 int NOT NULL
  ,IntrinsicField3 nvarchar(255) NULL
  ,index_subA int NULL --index created on this value. links to PK_SA of appropriate entry if it exists
  ,index_subB int NULL --index created on this value
  ....
  ,index_subM int NULL --index created on this value
)
CREATE TABLE tSubA
  PK_SA int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,ExtraField1 int NOT NULL
  ,ExtraField2 varchar(24) NOT NULL
  ,lMaster_ID int NOT NULL
) 
CREATE TABLE tSubB
  PK_SB int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,ExtF1 NUMERIC(5,3) NOT NULL
  ,ExtF2 NUMERIC(5,3) NOT NULL
  ,lMaster_ID int NOT NULL
) 
CREATE TABLE tSubM
  PK_SM int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
  ,EField1 int NOT NULL
  ,EField2 nvarchar(100) NOT NULL
  ,lMaster_ID int NOT NULL
)

*better could be

  1. faster
  2. less likely to bork up over time as DB entries are added and/or occasionally deleted
  3. easier for other DBAs to quickly comprehend
  4. more intuitive for the average end-user
  5. more easily extensible by adding additional sub-table types
  6. more data-compact
  7. more easily modified to be a one master to many copies of a subtable
  8. more easily modified to be a many masters to a single copy of a subtable
  9. insert your own definition here

(less emphasis on options 7&8 here)

migrated from dba.meta.stackexchange.com Apr 22 '17 at 2:30

This question came from our discussion, support, and feature requests site for database professionals who wish to improve their database skills and learn from others in the community.

3

I personally prefer option 2 and I have used this design many times in the past - especially when I am trying to model a hierarchy similar to how inheritance works in code.

The reason I prefer option 2 is because it keeps the master table clean. When implementing inheritance in Object-Orientated-Programming, the base class should know nothing about the classes that derive from it. Knowing this, we can immediately discount option 1 and 3 because you would need to add references for every possible type of subclass. This would make it a huge pain to maintain depending on how often changes are required. Using option 2 makes adding further subclasses extremely simple as you are less likely to break existing queries.

Additionally I would discount option 3 because you would have to store the keys more times than necessary and if you were to place FOREIGN KEY constraints on those columns (as you should) - the database engine would prevent you from doing so because it would cause a circular reference.

Also if you set your FOREIGN KEY constraints to ON DELETE CASCADE it would ensure that no subclass record could exist without it's parent record. I normally do not use this feature and recommend against it in general but this is a good case where it actually does work for you and makes maintenance of dead records much more simple.

If you keep all of your "shared" properties as columns on your master table and only include properties specific to the subclass within the subclass table then you will minimize the storage required.

In terms of speed, it is subjective not knowing hardware and data size/row count - but joining to a single table (subclass to master) will make your code easier to understand, maintain and optimize than it would if you were to go with a more complex example such as option 3.

  • Thanks for the tie to OOP. I often have a more procedural mindset than OOP, so pointing out base class and inheritance issues helps me become a bit more conventionally wise;) – mpag Apr 25 '17 at 18:28
3

There is not really much to talk about here. At least, the conventional wisdom is very clear. Only option 2 should be considered unless you have a very, very good reason, borne out of practical experience with your database, to do otherwise.

Neither option 1 nor option 3 have a parent table in first normal form (1NF) because they include repeating columns, i.e. the foreign keys to the sub-tables.

In a transactional system, you should start from the assumption that your tables should be in 3NF at least. Back away from that only for a good, well considered reason.

One such reason might be that there are lots of sub-tables and you don't know which one(s) might need to be joined to the parent for a particular parent record. It could happen that a performance issue results, depending on many factors. One compromise that you could use if this problem did occur, which you might consider Option 4 is to have bit flags on the parent table indicating the presence of each type of child. This is still begging for an eventual data inconsistency problem, but it at least will be less inconsistent than option 3, because it risks only pointing you to an empty join rather than to an incorrect record.

  • thanks for the information. Pointing out 1NF & 3NF got me to do some reading of those terms (and 4NF/5NF), which I think helped to steer me on the right path. – mpag Apr 25 '17 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.