Mysql – Inheritance – What’s better: three “equal” tables or one with a type column

database-designMySQLsubtypes

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

Best Answer

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.

Related Solutions

Sql-server – How to represent class table inheritance (current DBMS-specific way please)

Ultimately, I implemented ypercube's suggestion from comments:

The "type" column can be defined as a computed (but constant) column in SQL-Server. I think it has to be PERSISTED though so it can participate in the Foreign Key constraint.

This worked well both for performance and compatibility with with my tools (Entity Framework <= 6.0):

CREATE TABLE [dbo].[Account](
    [Id] [int] IDENTITY(1000,1) NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole] [int] NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
CONSTRAINT [UX_Derived_Relation] UNIQUE ([Id], [CommunityRole]))

CREATE TABLE [dbo].[Recruiter](
    [Id] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole]  AS ((1)) PERSISTED NOT NULL,
    [RecruiterSpecificValue]  [int] NOT NULL,
FOREIGN KEY ([Id], [CommunityRole]) REFERENCES Account([Id], [CommunityRole]))

CREATE TABLE [dbo].[Candidate](
    [Id] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole]  AS ((2)) PERSISTED NOT NULL,
    [CandidateSpecificValue]  [int] NOT NULL,
FOREIGN KEY ([Id], [CommunityRole]) REFERENCES Account([Id], [CommunityRole]))

This mapped well to my implementation of multiple discrete account types, Recruiter and Candidate, on my Job Board.

Sql-server – How to enforce data consistency across one-to-one relationship in an inheritance hierarchy

Add a student/teacher attribute to Person. Since this attribute is dependent of the key in Person (whatever that is), no Person can be both a Teacher and a Student. Now it is a matter of guaranteeing that a person whose type is student is not added to Teacher etc.

For DBMS:s that support queries in check constraints you can do something like:

ALTER TABLE Student Add constraint ...
         CHECK ( (select type 
                  from person p
                  where p.<key> = <key>) = 'Student' )

If you DBMS does not support this type of construction you can add a super key in Person consisting of the primary key + the type attribute. Add the type attribute to Teacher and Student, add a check constraint that guarantees the type in those "subtables", and a foreign key that includes the type attribute:

ALTER TABLE Person ADD COLUMN type_attribute varchar(..) not null;
ALTER TABLE Person ADD CONSTRAINT ... UNIQUE (<key>, type_attribute);

ALTER TABLE Student ADD COLUMN type_attribute varchar(..) not null;
ALTER TABLE Student ADD CONSTRAINT ... CHECK (type_attribute = 'Student')
ALTER TABLE Student ADD CONSTRAINT ... 
     FOREIGN KEY (<key>, type_attribute)
     REFERENCES Person (<key>, type_attribute);

Now it is not possible to add a student as a teacher and the other way around. That there really is a student/teacher for a person with that attribute has to be guaranteed through the transaction that adds the information.

Best Answer

Related Solutions

Sql-server – How to represent class table inheritance (current DBMS-specific way please)

Sql-server – How to enforce data consistency across one-to-one relationship in an inheritance hierarchy

Related Question