Representing classes hierarchy in database (JPA Inheritance)

Question

I'm working on staff management application for a clinic, basic scenario, but learning about JPA inheritance lead me to over think some parts, and I need help to clear my thoughts and design.

SCENARIO:

I have some obvious classes: employee, clerk, medical staff, nurse, doctor..etc... the conform to a class hierarchy where everyone is an employee, a nurse and a doctor are medical staff:

employee <__ clerk |__ medical staff<__ nurse |_ doctor 

and I thought about representing this hierarchy with java classes using JPA inheritance table per class strategy.

The pros

1. Each class reuses data and methods from parent classes.
2. The database ensures data integrity especially for relationships with other entities i.e a patient relationship to a doctor not to a clerk.
3. The design is logical and beautiful.
4. This design is future proof, as it's required to add many tables in the future as more data is available.

The cons

1. I think I'm overdoing it.
2. Too many pages to input && modify data.
3. Tables (in the view) that holds data form more than one Entity looks bad, and handling Class Cast and Type checks are cumbersome.

So I need some advice on such design, what is the way to go? and what am I missing there?

Answer 1

For mapping inheritance to tables there are three main strategies

• Class Table Inheritance: this is what you plan. You have a table for each class, so: employee, clerk, medical staff, doctor, and nurse. Of course, it's beautiful and you're on the safe side for future evolution. The main inconvenience is that you'll have to make a lot of joins to assemble the parts of a nurse, while there might not be so much nurse specific fields after all.
• Concrete Table Inheritance: you have tables only for concrete classes, and each of these table would contain all the fields for that class. The advantage is that you don't have to assemble the entities: all the attributes are in one table for a given entity. Unfortunately, this makes polymorphism much more painful; For example, here, for a given employee, you'd need to search in all the potential tables in which this employee could be defined.
• Single Table Inheritance: you put doctors, nurses and clerks in the same employee table, but you keep some class specific columns that remain empty for rows of other classes. This is less beautiful, but is extremely practical if from the data point of view, all these classes are very similar.

I'm not a healthcare specialist, but intuitively, I'd think that nurses, doctors and clerks certainly differ significantly int their skills and behaviors but certainly share most of their data. In this case, the single table inheritance could be a much more practical choice.