3

I currently have a database that uses Class table inheritance model. Three different tables inherit from this table. The child tables have all a FK to the parent table and the fields are properly indexed.

We hare currently having some issues at the application level because it is difficult to figure out what child table we are referencing from the parent. In addition to this, a query where we organize by type seems costly.

One developer argues that we should just join with all 3 tables to figure out what child the parent record corresponds to, and as long as the tables have the proper index queries shouldn't be costly.

In the other hand we could avoid a lot of confusion and problems if we add a discriminator field in the parent table, that way we would know without having to do any joins the 'type' of record we are dealing with. Also grouping by type would be much simpler.

  • Should we add a type in the parent table?
  • What would be the disadvantages?
  • Is it really not costly to join with multiple tables (as long as they are properly indexed) compared to having a discriminator field?
  • Other thoughts?

We are currently using an implementation of active record for PHP, and it doesn't support Class Table Inheritance.

Improve this question

1 Answer 1

Reset to default
3

I see these advantages of using a type in the base table:

  • You'll know exactly which other tables to read.
  • You'll know directly which concrete class to instantiate when hydrating.
  • You know what type of data you have just by looking at the parent table row.

And this disadvantage:

  • If you retrieve the instance via the application, you'll have to issue two queries: one to retrieve the base table data, and another to retrieve the children data given the found type. (This can be alleviated by using a stored procedure that encapsulates that logic.)

On the other hand, I don't think that a three-table join should be a problem, but remember that they need to be outer joins if you're not using a discriminator type in the parent table. You simply don't know what type of data you're looking at. But, as usual, profile accordingly.

Improve this answer
1
  • 2
    Adding a type is the choice of most of ORM like Doctrine, Propel or Hibernate. This type is well known as a discriminant.
    – csblo
    Commented Apr 23, 2015 at 9:24

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.