Re: Modeling question...
Date: Fri, 25 Jul 2008 16:23:30 +0200
Message-ID: <g6cnl1$e3p$1_at_nntp.fujitsu-siemens.com>
Philipp Post schrieb:
> Volker,
>
>> How does one model relationships in which
> - one child table has several parent tables
> - each parent record can refer to several child records <
>
> I would use relationship tables for them. These will contain the keys
> of the parent and the child table as foreign key and will compose the
> primary key of these two columns.
>
>> - each child record belongs to exactly one parent record in exactly one of the several parent tables? <
>
> There, you could insert a column containing the key of the primary
> table into the child table as a foreign key if you have such a direct
> relationship.
>
> I would not put all the relations into one table only. You could not
> not properly enforce foreign key and other constraints there and would
> create a collection of unrelated things in it what will be hard to
> manage. Having a lot of relationship tables should basically not be an
> issue, if they are named consistently and meaningfull.
The idea we have right now looks like this:
Parent-child-table-chain (we do motherboards): "project"->"schematic"->"pcb"->"variant"->"version" This is a one-to-many-relationship from left to right. Each table has as primary key an id ("owner_id") that gets taken from the same sequence, so the id's in those tables are all different.
"Attributes" is a table with three columns: "owner_ID", "Name", "Value", with parent_id and "Name" being the primary key.
Now, every of the parent-child tables has the attributes table as additional parent. As you see, we reversed the parent-child relationship between attributes and the other tables. Now, the attribute table has a zero-or-one relationship with each of the parent-child tables.
The only weakness I see is that I see no way to enforce that the parent-child-chain-tables share the same primary key-space, i.e. that each id shows up in no more than one table.
Lots of Greetings!
Volker
-- For email replies, please substitute the obvious.Received on Fri Jul 25 2008 - 16:23:30 CEST