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.
