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.
Improve this question
7
  • 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
    Commented Apr 3, 2016 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
    Commented Apr 4, 2016 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?
    – nicholaswmin
    Commented Apr 4, 2016 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.
    – nicholaswmin
    Commented Apr 4, 2016 at 2:08
  • 1
    @NicholasKyriakides you can use check constraints in other dbs, but yes, it's a problem in mysql.
    – Neil McGuigan
    Commented Apr 4, 2016 at 2:24

1 Answer 1

Reset to default
0

wow, 8 years since I've posted the above question. I'll post an update in case someone stumbles on this.

This turned out to be great way to model the system bearing the constraints. It's been in use in a couple major banks plus more smaller organisations for many years now. We've build a significant amount of functionality on top of it with 0 issues whatsoever 1.

In fact this was one of the few good design choices that I've done on that goddamn piece of ... work. Live and learn, I guess.

There is no best way. This is exactly what Class Table inheritance is.

I don't recall any problems with the people who were writing analytics reports on top of them. One might have asked some clarifications, others knew the pattern already.

--

1: This is of course a lie. There were many issues, just not with this part of the system.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.