When to use Table per class hierarchy over Table per subclass?
database-design, hibernate, java, web-applications
Solution
Note for future readers: the question was heavily modified since this answer was published. The second point might not make sense anymore
There are two answers to your question:
- You misunderstood the concepts of table-per-hierarchy and table-per-subclass. These deal with inheritance, not associations. So I can explain what really these concepts are.
- The database design you consider can be advised regardless of the first point.
1) what is table-per-hierarchy and table-per-subclass?
These concepts are not related to having a one-to-one or many-to-one association, they are related with the way you handle inheritance in your class design and translate in your database design. At one moment, you are briefly speaking of permanent employees and contract employees. So the dilemma for you would surely be:
I have classes `PermanentEmployee` and `ContractEmployee` that extend class `Employee`. Do I need a single table for both types of employees (table per hierarchy) or do I need a table for `ContractEmployee` and another for `PermanentEmployee` (table per subclass) ?
With this dilemma, yes, the existence of not-null constraints has an importance. With the first strategy, you get a single table with all the common columns, all the columns specific to contractors, all the columns specific to permanents, and an additional column for discrimination (e.g. it would contain "CON" for contractors and "PER" for permanents). If you store a permanent, you will set all columns null for the contractor-specific columns. So you can't enforce a rule such as "Contractors have a mandatory end_contract_date column", as it will be null for permanents.
Other arguments for or against would be performance: first strategy uses more spaces, with plenty of empty columns, while in the second one you duplicate the common columns in the two tables, and if ever you want to select all employees (regardless of contractor/permanent) you will have to make an SELECT UNION.
2) what with your design?
One-to-one and Many-to-one are related to your tables and joins. If you have a one-to-one, it means you have two tables with a restricted foreign key (unique). If you have a many-to-one, same thing but the fk is not unique.
For your many-to-one example you are correct, normalization requires your two tables. For your one-to-one you should use two tables also, but you might instead use a component for your EmployeeDetails and then have only one table. See this hibernate documentation which will explain it (be aware that from a vocabulary point of view it will not be a one-to-one anymore).
Problem
In any webapplication we come across the scenarios where we can o ahead either with Table per class hierarchy or table per subclass. But important thing is to take decision which one is better for your use case. I have come up with my basic understanding that which one is better when? `Scenario :-` Employee . Permanent and Contract Employee extends Employee. Two option exist :- `Option 1:-` Table per class hierarchy where we represent all fields in single table. good thing about it you can get all the details from single table and get rid of joins between Employee and Permanent/Contract Employee. So improved performance. But yes you can not declare not null constraint even on fields which you think can not be null for permanent employees(as we are catering both permanent and contract employess in single table). This is the downside. `Option 2:-` Table per subclass where we have foreign key reference to parent table. Joins here degrades the performance. But yes you can go head with putting constraing like no null which you could not do in first option. `My take :-` Go ahead with option 1 for this kind of scenario if putting contraint is not required for you as Getting rid of join is good for performance pov. would like to hear if there are more sides to it or my understanding is correct?