Re: Complex RDB Tables

Thanks a bunch.

"Jason Glover" <jglover_at_xtra.co.nz> wrote in message news:27b86948.0203202005.50cde6ce_at_posting.google.com...
> 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 - 16:13:55 CET