2

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.


Current situation:

Here's my example schema:

mySQL ERD with a <code>Customer</code> parent table and 2 "child tables" connected by referencing <code>idCustomer</code> which is the primary key on "parent table". The child tables are <code>CustomerPerson</code> and <code>CustomerOrganization</code>

To break this down:

  • Customer is the parent table which contains common fields for all Customers.

  • CustomerPerson/CustomerOrganization are child tables and contain all fields of Customer + the specific ones depending whether Customer is Organization or Person.

  • I'm using a flag (column type on Customer) to differentiate between Person or Organization.

    • If I'm not mistaken, that "flag" would be called a discriminator in this context.

The problem.

  • I think having a flag/discriminator column on Customer is not the ideal and another table needs to be added, e.g CustomerType which 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?


Notes:

  • 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 type column on every table that deals with this differentiation will just make things more messy as time passes and the schema gets more complex.
  • You don't need a type column. Hibernate doesn't use one, for example. You can figure out the type from the table. You're probably better off w Single Table Inheritance though, as it's faster and simpler, and works better with other tools (like Hibernate, Jackson, Spring Data) – Neil McGuigan Apr 3 '16 at 21:29
  • Classes and objects did not exist until decades after SQL was well established. I think "inheritance" does not fit will with SQL. – Rick James Apr 4 '16 at 0:22
  • @NeilMcGuigan I already considered & rejected Single-table inheritance as it won't allow me to enforce NOT NULL columns which are really important here. Also adding new Customer Types won't be really a breeze. - On the first part of your comment can you expand on it in an answer? – Nik Kyriakides Apr 4 '16 at 2:06
  • @RickJames I don't think it really matters which one came first. If you think there's a better, more SQL-y way of modelling what I'm trying to do feel free to post it an answer. – Nik Kyriakides Apr 4 '16 at 2:08
  • 1
    @NicholasKyriakides you can use check constraints in other dbs, but yes, it's a problem in mysql. – Neil McGuigan Apr 4 '16 at 2:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.