Re: M:M table conditional delete for parents

Kenneth Downs <ken(at)secdat(dot)com> wrote on 03/06/2007 05:48:05 AM:

> MargaretGillon(at)chromalloy(dot)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