Re: M:M table conditional delete for parents

[MargaretGillon@xxxxxxxxxxxxxx]

Kenneth Downs <ken@xxxxxxxxxx> wrote on 03/06/2007
05:48:05 AM:

> MargaretGillon@xxxxxxxxxxxxxx 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