Issue: Mapping Employee Insurance relationships

[Craig Russell <Cr...@Sun.COM>]

Javadogs,

We are finalizing the mapping for the TCK and have a small issue 
mapping the one-one relationships. We started with this page but we ran 
into some issues: http://wiki.apache.org/jdo/EmployeeInsuranceMapping.

We want to add another one-one relationship between Employee and 
Insurance so we can properly test that it's possible to have more than 
one of the same relationship between classes. This is the same reason 
we added such things as mentor-protege and fundingDepartment. In doing 
this, we made subclasses to make the relationships make sense, but this 
added some complexity to the model and the mapping:

1. Multiple relationships between classes.
2. Mapping one side of the relationship to a superclass field and the 
other side to a subclass field.

We now have two relationships, defined by Employee.medicalInsurance 
which corresponds to MedicalInsurance.employee, and 
Employee.dentalInsurance which corresponds to DentalInsurance.employee.

The issue is that the employee field is defined in the superclass 
Employee. And the third normal form for the database has one column 
with a foreign key to Employee. Other possible mappings would have 
multiple columns representing the relationships, but these would not be 
normalized.

So here's what we propose:

The classes:

abstract class Employee extends Person {
...
MedicalInsurance medicalInsurance;
DentalInsurance dentalInsurance;
}

abstract class Insurance {
long insid;
}

class MedicalInsurance extends Insurance {
...
}

class DentalInsurance extends Insurance {
...
}

The schema:

CREATE TABLE employee {
     personid INTEGER PRIMARY KEY
...
};

CREATE TABLE insuranceplan {
     insid INTEGER PRIMARY KEY,
     carrier VARCHAR(64) NOT NULL,
     lifetimeOrthoBenefit DECIMAL NOT NULL,
     planType VARCHAR(8) NOT NULL,
     discriminator varchar(64) NOT NULL,
     employee INTEGER REFERENCES employee
};

The mapping:

<class name="Employee">
     <inheritance strategy="superclass-table"/>
     <field name="dentalInsurance" mapped-by="employee"/>
     <field name="medicalInsurance" mapped-by="employee"/>
</class>

<class name="Insurance" table="insuranceplan">
     <inheritance strategy="new-table">
         <discriminator strategy="class-name" column="discriminator"/>
     <inheritance/>
     <field name="insid" column="insid"/>
     <field name="carrier" column="carrier"/>
          <foreign-key/>
     </field
</class>

<class name="DentalInsurance">
     <inheritance strategy="superclass-table"/>
     <field name="lifetimeOrthoBenefit" column="lifetimeOrthoBenefit"/>
     <field name="employee" column="empid">
</class>

<class name="MedicalInsurance">
     <inheritance strategy="superclass-table"/>
     <field name="planType" column="planType"/>
     <field name="employee" column="empid">
</class>

If anyone sees any issues with this, please let us know.

Craig

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!