Re: Complex RDB Tables

The problem you have found there is that relationships are inheerantly heirarchial. The mechanism for defining relationships is through primary and foriegn keys, but they only allow you to define a 1-to-many relationship.

i.e. One workorder contains many parts.

But then if you put a foriegn key in the parts table to relate it back to the wrokorder table you find that you can only use the part in one workorder. That is not on.

That is because the workorder and the parts actually have a many-to-many realationship.

i.e. One workorder contains many parts. One part can feature in many work orders.

The solution to this problem is to have a "linking" table, this linker table contains forign keys into both of your related tables and doen't model a real "enitity", just a relationship.

i.e.

CREATE TABLE WorkOrderPart
(
WordOrderID char(10),
PartId char(10)
)

Then, to actually get any use out of data you will need to join the tables back together. Welcome to the wonderful world of JOINs.

SELECT WO.*, P.*
FROM WorkOrder AS WO
LEFT JOIN WorkOrderPart AS WOP ON (WO.WordOrderID = WOP.WorkOrderID) JOIN Part AS P ON (WOP.PartID = P.PartID)

Truely though, you are going to get lost without a good read through a book on the subject. OODBs are quite a different mindset to RDBs.

Good luck

JBoy

"objectman" <kcintron_at_elp.rr.com> wrote in message news:<Fm3m8.38055$Dv6.1593053_at_typhoon.austin.rr.com>...
> I'm a newbie to complex relational DB modeling.
>
> What I'm attempting to do is.
>
> WorkOrder
> parts used (1+)
> accessories used (1+)
>
> In other works the WorkOrder has n+ parts and accessories that are used.
> In an OODB I would solve this using aggregation. What is the solution
> in the relational world. Thanks in advance.
Received on Thu Mar 21 2002 - 05:05:56 CET