Re: Foreign key(s) in a single child table relating to multiple parents

If you were to have documents related to these entities that you placed in web pages (such as pages for the parents with links to children and vice versa), then what would that web of data look like? Where documents would be linked? Alternatively, if these records were filed in a manual file system, what file folders would you have and what "See folder xyz" links would be written to cross-reference documents?

Model the data the way you are inclined to think about the information (as everyone did intuitively BEFORE there was a notion of attempting to flatten and then piece back together the language/data).. This will typically yield graphs rather than flat relations. If you have the freedom to implement the data in a system that permits modeling data as graphs (at least in trees), then I would suggest doing so. If you are saddled with an RDBMS or SQL-92 compliance requirement, then look at the root of each graph -- what are the
"home pages" or "top level file folders" labeled? These would likely map to
your primary entities/tables/relations. When normalizing the rest of the data, link it back (eventually, at least) to its root node.

Where you have 1-M or M-1 relationships, in an RDBMS implementors typically place a foreign key in the table on the M side, since that yields a column that is single-valued. In the case of M-M, a link table (or relationshp table) is a common strategy.

I haven't caught quite enough clues, but the way you have described this, it sounds something like the old (and I'll date myself here) "M card" scenario, where a transaction file has transactions that apply to a Master file identified with an "M" in card column 1 (for example), while some other
"card type" applys to another type of transaction that maps to another
"parent" file. In that case, your "card deck" could have either of the
options you describe, but if it is important to have 5 separate parents, then making an additional 5 relationship/link tables might be in order. Tables: ParentA,B,C,D,E Child,
RelationshipA-Child,B-Child,C-Child,D-Child,E-Child.

In the IBM U2 database (PICK), I'd model it with links and return links (as one would on the web)

ParentA
MyChildren: MultiValued field

ParentB
MyChildren: MV

...
Child:
ParentType: (A-E)
ForeignKeyToParent: <string>

This is similar to your Option2. --dawn

--dawn Received on Mon Feb 16 2004 - 00:33:19 CET