3 Replies Latest reply on Nov 14, 2002 2:04 AM by Guofeng Zhang

    N:N Relationships, Intersection tables, and primary keys

    randy Newbie

      I have an existing database that I am looking at implementing a set ejbs on. In particular, there are 2 tables that have a many to many relationship, one called Application (AppModel, AppVersion, AppName, PartNumber) with a PK of (AppModel, AppVersion), another table called Programmer(SerialNumber, Institution) with a PK of SerialNumber. There is also an intersection table called Baseline(SerialNumber, AppModel, AppVersion, DateInstalled), where SerialNumber is a FK from Programmer. AppModel and AppVersion are a FK from Application. DateInstalled is an relationship attribute. All four keys are currently set as a primarykey.

      My problem is how do I map this to an EJB model. If I try to create a many to many CMR between Appliaction and Programmer, using Baseline as the RelationTable, I don't see how I can populate the DateInstalled attribute. Also I couldn't implement finder methods based on baseline, since there would be no baseline Entity.

      If I create a new Entity bean called Baseline, and have a Many To One CMR to Application and another Many To One CMR to Programmer, I don't see how I can use a CMR object as a primary key.

      I thought about removing the PK constraint on the Baseline table, but conceivably duplicate data could then be inserted, which I really shouldn't happen.

      Ideas? Has anyone else used relationship attributes/fields, along with using a FK as a PK?