I'm wondering what the best practice is to use inheritance in JPA entities if the entities don't share a primary key, in particular if the database already exists and cannot easily be changed.
Assume we have an employee database, like the one used in the Oracle HR examples. Now, that database isn't quite realistic. Salaries usually change with time, and we will keep track of how salaries have evolved. For example a work contract might say that every year in December, employees meet with their manager to discuss the salary for the next year.
We would therefore remove the salary field from HR.EMPLOYEES and create a new table HR.SALARIES:
EMPLOYEE_ID NOT NULL NUMBER(6)
VALID_FROM NOT NULL DATE
SALARY NUMBER(8,2)
Primary key would be composed of EMPLOYEE_ID and VALID_FROM.
So, now if Pat meets her manager Michael in December and agrees on a new salary, we will insert a new record into HR.SALARIES.
Now, let's assume the HR department made a mistake when entering the new data, so Pat gets paid an incorrect salary in January. She reports the problem to her boss, he contacts the HR department and they correct the record in HR.SALARIES. She'll receive the missing amount with her February payment.
At the end of the year, the company is audited. The auditor wonders why pay check issued to Pat in January doesn't match the salary entry in the database. To make sure, auditors can always understand why the system made certain decisions (such as issuing a check in a certain amount), we need to keep a change history.
Change HR.SALARIES to:
EMPLOYEE_ID NOT NULL NUMBER(6)
VALID_FROM NOT NULL DATE
SALARY NUMBER(8,2)
CHANGE_DATE NOT NULL DATE
Add new table HR.SALARIES_CHANGE_HISTORY:
EMPLOYEE_ID NOT NULL NUMBER(6)
VALID_FROM NOT NULL DATE
SALARY NUMBER(8,2)
CHANGE_DATE NOT NULL DATE
Primary key would be composed of EMPLOYEE_ID, VALID_FROM and CHANGE_DATE.
We keep the change history in a separate table for performance reasons: it will hardly ever be read by our HR system.
Now we are finally coming to my question. How do we best model this DB in JPA, and can we somehow take advantage of inheritance, since tables HR.SALARIES and HR.SALARIES_CHANGE_HISTORY are identical except for the fact that CHANGE_DATE is part of the primary key in HR.SALARIES_CHANGE_HISTORY.
There is the @MappedSuperclass in JavaEE which sounds good at first, but it is not possible to use different primary keys in a class hieararchy that uses @MappedSupperclass (see JSR-000338 JavaTM Persistence 2.1, Section 2.4 and JSR 220: Enterprise JavaBeansTM,Version 3.0, Section 2.1.4).
Is there any way to avoid duplication of code in the entity beans for SALARIES and SALARIES_CHANGE_HISTORY?
