Inheritance - What's better: three "equal" tables or one with a type column?

Question

I'm having an existencial doubt: I have three conceptual entity types on my logic:

- distributors
- business_groups
- clients

All of three are "companies", and will have the same columns. The relationships are the following:

- 1 distributor => N business_group
- 1 business_group => N clients

In reverse:

- 1 business_group => 1 distributor
- 1 client => 1 business_group

My question

Is it better to create three "equal" tables, one for each entity type, or a single one for the three with distributor_id and business_group_id foreign keys (referencing the same table) that sometimes will be NULL?

Considerations

• First approach (three tables): Separation of different concepts, tables with less entries, foreign keys without NULLs, three "equal" tables (with the same columns all)
• Seconds approach (one table): Only one table but less intuitive, some NULLs on foreign key columns, one column more for type

Answer 1

This is a very, very common question; people just do not know what to search to get good results. You need to see suggestions on implementing table inheritance (sometimes mentioned as polymorphic associations) for a relational database.

There is no general answer for that problem, there is a few stategies like the ones you mention, and it all depends on your needs, and the focus of later queries (writes and reads). Each option has its advantages and disadvantages- for example, you can sacrifice consistency for better read performance. But you should identify this pattern in order to know the tools to solve it.

You can read what other people has written about it at:

• Sql Antipatterns Strike Back (Bill Karwin)- Polymorphic Associations: https://www.slideshare.net/billkarwin/sql-antipatterns-strike-back/32-Polymorphic_Associations_Of_course_some/32
• Previous answers on stackexchange: https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database

Check literature by Bill Karwin and Martin Fowler for a more in depth analysis.