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

    N:N Relationships, Intersection tables, and primary keys

    drbugs
    drbugs Nov 12, 2002 10:31 AM

      Hi,
      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?

      TIA!

      Randy

      • 2671 Views
      • Tags:
        • 1. Re: N:N Relationships, Intersection tables, and primary keys
          gfzhang
          gfzhang Nov 13, 2002 2:17 AM (in response to drbugs)

          I meet the same problem.

          Maybe the FK cannot be a part of a compound PK for the following reason:
          (1)The names of the fields in the primary key class must be a subset of the names of the container-managed fields(EJB 2.0 spec 10.8.2)
          (2)An entity bean local interface type(or a collection of such)cannot be the type of a cmp-field.(EJB 2.0 spec 10.3.1).

          Hope anyone could tell me if I am right or not.

            • Actions
          • 2. Re: N:N Relationships, Intersection tables, and primary keys
            drbugs
            drbugs Nov 13, 2002 3:59 PM (in response to drbugs)

            I think you are probably right.

            For now, I have the database enforcing the PK, so I might be able to get away with catching an exception (pk exists type). Not the cleanest way, but I think it will work.

            I was hoping for a more elegant, or at least a nicer solution so my bean can more accuartly represent the data.

            If I find something I willpost on this topic again.

              • Actions
            • 3. Re: N:N Relationships, Intersection tables, and primary keys
              gfzhang
              gfzhang Nov 14, 2002 2:04 AM (in response to drbugs)

              "container-managed fields" refer to "container-managed persistent fields" or "container-managed relationship fields" or both?

              I think if something is to be a primary key, it must also be a field( or attribute) of a Primary Key class. How about if the CMR object is the attribute of a primary key class?

                • Actions