Simple Rule Of Thumb To Determine the Number of Tables In A Relationship

More on Database Design Mistakes to Avoid

Here is my rule of thumb for the number of tables, for each relationship.

Relationship Number Of Tables
Many to Many M:N 3
One to Many 1:M 2
One to One 1:1 1

You would think this would be obvious.  But so many database people I’ve spoke to had never heard this concept.  Once I was in an interview, and mentioned this idea.  One of the interviewers took out a piece of paper, and made a note of it.

One exception would be supertypes and subtypes.  Different fields required for each subtype do not apply to the others.  Ie.

Supertype:  Phone_Call.
Subtypes:  Cell_phone_call, Land_line_call, Pay_phone_call, VOIP_Call, Skype_Call, Calling_Card_call

Roaming does not apply to a call from a land line.

Another exception would be putting large objects LOBs in a separate table.  Covered in another post.

Again, don’t go blindly following rules.  Read the The Dreyfus Model of Skills Acquisition.  At the other end, don’t go overboard on skepticism, speculating, and questioning everything.

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: