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?
posted 7 years ago
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