m:m relationship producer_id, producer_id

[M Marin]

Hello

I am developing a java based program and also I have to normalise the DB. My problem at this phase of the development is to reorganise the database structure. I need a m:m relationship between records of the same table. Producers are linked to producers. I know how to do a many to many relationship with two different entities using a third "link" table. So the question is: can I use a second table with two fields (producer_id, producer_id) just to link records of the same table. What is the best solution for this relationship?

Regards

M. Marin.

[James Sutherland]

Yes, you can use a join table to define a m-m relationship between the same table, the same as for two different tables. You will need to name the two foreign key columns differently of coarse.

i.e. consider a managed relationship using a join table
EMP_MANAGED
MANAGER_ID->EMP.ID
MANAGED_ID->EMP.ID