Many-to-Many Mappings with Extra Columns

[stepan]

I am attempting to implement a many-to-many mapping with additional columns in the relation table using CMP and CMR and am encountering difficulties. Here is the scenario:

1) Tables A and B each have a primary key of type INTEGER, A_ID and B_ID, respectively.

2) Table AB is an intersection table enabling a many-to-many relationship between A and B. It's primary key is a composite key consisting of foreign keys to A.A_ID and B.B_ID.

3) Table AB also has a column AB.EXTRA with data specific to each mapping between a row in A and a row in B. As an aside AB.A_ID and AB.EXTRA form an alternate key.

I can easily implement the many-to-many mapping between A and B using CMR between entity beans for A and B and a relation table mapping - BUT this leaves out AB.EXTRA. IF, OTOH, I try and implement AB as an entity bean and have many-to-one relations between A and AB and B and AB, I encounter difficulties because the primary key fields of AB are also foreign key fields, and I don't know how to make this work using CMP and CMR.

TIA for any help,

-- Steve

[aloubyansky]

You can't add extra fields to the table that is created to handle relationships.
If you have to do it, then, in CMP, you have to create one more entity bean and establish relationships between the three. But as you noted, currently, foreign keys can't be a part of primary keys.

[turalyon]

Would you recommend to switch to "bean managed relationship" in this case or is the extra CMP entity bean the way to go?

I wonder why this question is not asked more often, as i face the exact same problem.

I'm working on a document-management-sytem and i'm not sure about how to implement the Many2Many relationship between my entities "file" and "user". The only extra field i have to add would be "isVisible" to check whether a "file" that belongs to 1..n "user"s can be seen on that users public list of files.

Any comments on this architectural question will be appreciated.