Kenneth Downs <ken@secdat.com> wrote on 03/06/2007 05:48:05 AM:
> MargaretGillon@chromalloy.com wrote: > > Postgresql 8.1.4 on Redhat 9 > > I have a table which stores M:M relationships. I can't put foreign > keys to the parents of this table because the relationships being > stored go to several tables. This was done so that only two fields > have to be searched in order for all relationships to be found for > an item. For an oem number there might be 50 to 100 relationships > and 40 different tables having to do with materials, locations, > revisions, specifications, customer, etc. that might be referenced. > > Have you considered creating real cross-reference tables (aka M:M) > between all pairs of tables, and then having a view that UNIONs themtogether? > > This way you don't have to re-invent the foreign key to get it all working. > >
> -- > Kenneth Downs > Secure Data Software, Inc. > www.secdat.com / www.andromeda-project.org > Office: 631-689-7200 Cell: 631-379-0010 > > ::Think you may have a problem with programming? Ask yourself this > ::question: do you worry about how to throw away a garbage can?
LOL, I actually thought of this late yesterday afternoon. At first I thought this idea would not work because of the number of tables. Then I decided I might be able to categorize the junction tables into 4 or 5 groups, and make a view for each group. Each view would use 15 to 20 tables. This plan is better than working with 50- 100 individual junction tables.
As you suggested using the foreign key structure that already exists in Postgresql is an easier way to go.
Cheers, *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297