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!!
I want to call attention to one of your business rules: Representative is Customer and is Company.
This sounds like a superclass/subclass situation to me, and I think you have the EER diagram well in hand, as far as ER modeling goes. Where things start to get interesting (and a little messy) is when you try to switch over from an ER model (which is abstract and implementation neutral) to a relational model (which is not).
At this point, you'll have to choose between a few well known techniques that make up for the fact that the relational model doesn't incorporate inheritance. You may wish to include this tag in your original question: subtypes
Here are three of the techniques in a nutshell: single table inheritance, class table inheritance, and shared primary key.
Single table inheritance lumps all the subclasses together in one table. Data that does not pertain to a given instance is left NULL.
Class table inheritance has one table for the superclass and one table for each subclass. Generalized data goes in the superclass table, and specialized data goes in the appropriate subclass table.
Shared primary key enforces the one to one nature of the relationships between each subclass and the superclass. It also allows a foreign key elsewhere in the database to reference the superclass without knowing or caring which subclass it belongs to.
For a good treatment of this subject, look up Martin Fowler's treatment on the web.
Edit based on feedback
The question of which ER model is "correct" is usually determined by the database design team by analyzing the subject matter, and coming up with a model that depicts the way it works in "the real world". This presumes that the database is yet to be designed, but the business itself has already been designed, and the business design is not to be changed. Your comment makes me think that you are designing (or redesigning) the business itself, not just the database. If so, then the question is outside the expertise of database design.
To deal more specifically with the questions you asked:
- Is supertype / subtype needed here (since subtype Entities have no unique attributes and since it gives me a headaches)
If there are no attributes that are specialized to the subtypes , then no inheritance (from a data perspective) is needed. So it won't make any difference to the relational design. Don't give yourself headaches. Pick a model that works for your business, and let it go.
- How to implement inheritance with one entity in recursive relationship - if for instance Customer / Representative is in recursive relationship (relational table example needed)
If Customer and Representative are the same entity, give them a common name. One entity with two names is confusing. If they are different entities, rephrase your question. From my answer to question 1, why is implementing inheritance called for?
Are "recursive relationship" and "reflexive relationship" the same thing? If not, please clarify your question.
- Can you give me relational solution (EER to relational) with some examples (like table with data) so I can wrap my mind around it.
That's what I thought I was answering the first time. Fowler's treatment of inheritance/relational issues is available on the web. Here and here.
His examples are about players and cricketers, not customers and representatives, but the issues concerning implementing inheritance (really pseudo inheritance) in a relational system are not case dependent.
Best Answer
It's not always carried on automatically. Depends on the ORM you are using (if any ) on the app side it may or may not be implemented . For instance, it works for Hiberanate (personally I use
@Inheritance(strategy=InheritanceType.JOINED)in main entity).If you don't use any, then you need to do carry PK of main entity yourself - again, depends on RDMS engine, you can use
RETURNING(Oracle, Postgres) orOUTPUT(SQLServer ) or some form ofLAST_INSERT_ID(mysql, and maybe others ) to get value of auto-generated primary key in master table.Also, as a side note I would recommend to have discriminator column in all tables (for instance,
computer_type_id) to ensurecomputercan have exactly one sub-type . E.g.computer :
Computer(computer_id, computer_type_id (FK to computer_type), PK (computer_id), UNIQUE(computer_id,computer_type_id)....)laptop :
Laptop (computer_id, computer_type_id DEFAULT LAPTOP_TYPE_ID, FK (computer_id, computer_type_id), CHECK (computer_type_id = LAPTOP_TYPE_ID))Note : Unique constraint on (computer_id,computer_type_id) may see redundant (and it is from pure theoretical point of view), but it's added for a reason - thus you can refer to it in child tables. In some RDMS , for example Oracle, both constraints (PK and UNIQUE) can share the same index , so no overhead from performance point of view...