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.