Relational table with more than 2 foreign keys

[cane74]

Hi.

Is there any possibility to create CMP beans which are connected through a table like:

table R_COMPANY_PERSON_POSITION {
 id_company: integer
 id_person: integer
 id_position: integer
}

Every foreign key corresponds to Entity Bean. Single company may have a lot of employees and every employee can have one or more positions. System should give a functionality of adding person to company with specified position. I don't want to write separate Entity EJB for this table, but I don't know it's possible to manage this relational table through @ejb.relation XDoclet tag. Any help will be appreciated :-)

Regards,
Bart

[sesques]

Hi bart,

First, I think that there is no restriction about managing this relation table through @ejb.relation, just you must use also the @jboss.relation-table, @jboss:relation and @jboss.relation-mapping to define the alternate table.

But this means that you define many-to-many relations between the 3 entity beans. Do you really need this ?
Does Employees belongs to many companies ?

When I was young (so far away), I learned that this type of relation (with more than 2 tables) must be avoided as possible. But if you are sure of your modelisation, you can go on.

Pascal

[cane74]

Hi Pascal,

suppose CRM system, where companies are customers and each company has many contacts to different employees and each employee has different company position - I think managing single relation table is not bad idea, but I'm still looking for best solution :-)

The biggest advantage of such modelisation is that every person in system is always SINGLE record, even if the same person will be a contact to more than one company (it happens!). Of course I can do another table (lets say T_POSITION) with foreign keys defining company and person to which this position belongs, but then the information will be not in a single place :-(

Regards,
Bart

[aloubyansky]

If this table was read-only it would be ok. Otherwise, it won't work with the current implementation. Each pk pair is supposed to be a seperate row. This means if INSERT succeeds one of the columns will certainly be null.

[cane74]

So I need to redesign part of DB - thanks :-)

Bart