I am trying to implement proper Class Table Inheritance (aka Table Per Type Inheritance) in mySQL.
AFAIK it's not offered out of the box so some hacking around is needed to make it tick.
Here's my example schema:
To break this down:
Customeris the parent table which contains common fields for all Customers.
CustomerPerson/CustomerOrganizationare child tables and contain all fields of
Customer+ the specific ones depending whether Customer is Organization or Person.
I'm using a flag (column
Customer) to differentiate between Person or Organization.
- If I'm not mistaken, that "flag" would be called a discriminator in this context.
I think having a flag/discriminator column on Customer is not the ideal and another table needs to be added, e.g
CustomerTypewhich connects the child tables with the parent tables.
Do I need another table or am I overthinking this? If yes how should I connect them?
- The above example works just fine but I'd like to hear ideas on how to do this in the best way possible.
- Best-way possible is meant in terms of scalability and DRY-ness. Having a
typecolumn on every table that deals with this differentiation will just make things more messy as time passes and the schema gets more complex.