When to use a table for each class hierarchy, a table for each subclass?

Question

In any web application, we are faced with scenarios in which we can advance either using a table for each class hierarchy or a table for each subclass. But it’s important to decide which is best for your use case. I came up with my basic understanding of what is best when?

Scenario :-Employee A permanent and contracted employee applies to the employee. There are two options: -

Option 1:-A table for a class hierarchy, where we present all the fields in one table. The good thing about this is that you can get all the details from one table and get rid of the associations between Employee and the permanent / contract employee. This improves performance. But yes, you cannot declare a non-zero restriction even for fields that, in your opinion, cannot be null for regular employees (since we serve both permanent and contract workers in one table). This is a flaw.

Option 2:-A table for each subclass where we have a foreign key reference to the parent table. Consolidation here degrades performance. But yes, you can go to the head with a restriction, like no zero that you could not make in the first option.

My take :- We go further with option 1 for such a scenario if you do not need a contrast, since getting rid of the connection is useful for pov performance.

I would like to hear if there are other sides in it or is my understanding correct?

Answer 1

Note to future readers: the question has been greatly modified since this answer was posted. The second moment no longer makes sense

There are two answers to your question:

• You misunderstood the concepts of a table for hierarchy and a table for a subclass. They concern inheritance, not associations. Therefore, I can explain what these concepts really are.
• The database design that you consider can be recommended regardless of the first point.

1), ?

"--" "--", , . - . , , , :

PermanentEmployee ContractEmployee, Employee. ( ) ContractEmployee, PermanentEmployee ( )?

, , . , , , , , (, "CON" "PER", ). , , . , , " end_contract_date", .

: , , - , ( , /) .

2) ?

"--" "--" . , , (). --, , fk .

"--" , . , EmployeeDetails, . . , (, ).