Relationship design

[tmwiega]

I'd like to layout a scenario and get some feedback on design considerations utilizing EJB2.0.

I'm trying to use entity relationships, and the problem I'm running into is designing for the (many-to-many) relationship. Where I work, the database design is outside my control, and we have a database schema that includes other attributes in our association or link tables. So I'm trying to figure out how to best use M-to-M relationships where additional attributes are added (other than foreign keys). As an example I'll use Sun's tutorial app. If I have the "PlayerEJB" and it has a many-to-many relationship to the "TeamEJB". What I'm looking for is the ability to add more attributes to the association or link table created. In this scenario, say we wanted to add the date or a sequence number the association was created. Is it possible to use a M-to-M relationship or should the association table (Team_Player_Table) itself become "TeamPlayerEJB" that maintains linkage to Player and Team tables.

[dsundstrom]

You have to use another entity (most likely one with just a local interface) to model this type of relationship. The new TeamPlayerEJB would be a member of two one-to-many relationships.

You many run into problems with the database design being outside your control. JBoss 3.0 currently doesn't support using relationship foreign key fields for primary keys, so your relation table must have an additional cmp field for your pk, which is not probably the design you will get from you db people.

This on my list of features for 3.0 final, but it is not at the top of the list. When, I do implement it you will be able to add new cmp fields, say PlayerFk and TeamFk, that you will map to the relationship fields. Then you would just create a compound pk for these fields.