Inheritance or lot of relationship?

Question

I have to transpose a class Diagram into a Physical Data Model (more precisely MySQL database structure).

In my class diagram, I have a lot of entities (A, B, C, D) which inherit from an other entity, named PARENT.

PARENT entity have a relationship PARENT_OTHER of cardinality (0.* - 0.*) with an entity named OTHER.

I had thought of two methods :

• My first method is to use "Concrete Table Inheritance". I don't transposed the PARENT entity into a table but I have one concrete table for each children entities : T_A, T_B, T_C, T_D. However I must create the PARENT_OTHER relationship for each tables T_A, T_B, T_C, T_D.

Schema :

So, if I have N childrens entities, I have 2 * N Tables... (without the table T_OTHER).

• The second method is to conserve the inheritance (Class Table Inheritance).I have one table by children : T_A, T_B, T_C, T_D and one table for PARENT entity : T_PARENT.

Schema :

So, if I have N childrens entities, I have N + 2 Tables... (without the table T_OTHER).

Out of the number of tables, the second solution offer a bit more of abstraction... I can look directly in the table T_PARENT_OTHER for all children without joins.

Some people say me that I should avoid inheritance in RDBMS (Relational Database Management System), like MySQL DBMS, and I suppose that it's true.

I think this is common problem in database modelisation.

I would like to know what is the best way in this kind of situation ?

Are there other ways ?

Thanks in advance and please excuse my english.

Answer 1

This is just a common answer.

Avoid MYSQL for OOP Concepts for the time being. OODBMS stil under research in some areas.If you are Maping OOP business/program logic to a RDBMS(ORDBMS) and still expecting open source go for PostgreSQL(PGSQL). PGSQL is not a full OODBMS. But Inheritance can be achieved there.

Your methods may result in unusual processing cycles thus results in excessive workload to the system. If you are going with your methods (In between Helper Tables ), make sure you using correct Indexes.

And, still if you are preferring MYSQL, Use High level of Abstraction to achieve Specialization down the Inheritance hierarchy and link via Foreign keys with parents. Do Normalization up to 3NF correctly. Yes! this is the usual Relational Way!!