> -----Original Message-----
> From: Donald Tyler [mailto:[EMAIL PROTECTED]
> Sent: 30 July 2003 16:42
> To: [EMAIL PROTECTED]
> Subject: Advice wanted on Data Structure
>
>
> I have a question that I hope I can explain well enough:
>
> I am trying to figure out a data structure for an inventory
> system. The
> system contains:
>
> Items
> Kits (Made from a collection of Items and/or other Kits)
>
> Now my question is:
>
> Is there any way to structure this in a database so that I could run a
> single query to get the contents of a kit, even though it
> contains other
> kits?
>
> My problems occur when I try to create the tables as so:
>
> ITEMS: KIT_CONTENTS KITS
> KIT_ID<---------------->KIT_ID
> ITEM_ID<--------->ITEM_ID Description
> Description
> Price
> Etc
>
This is a classic problem known as a Bill of Materials explosion and
unfortunately relational databases don't handle it very well.
Storage is easy(ish).
Fundamentally you have a recursive many to many relationship between
components, resolved as
Component: Component_Link
id <-----------|---assembly_id
name |---subcomponent_id
That is 2 foreign keys back to the same master table, if the diagram isn't
clear.
In OO terms, both item and kit are subclasses of component. There are may
ways to implement that
in a relation database, but the simplest is to store them as a single table
with a type field.
Retrieval is harder.
To get the contents of an assembly (kit),
select *
from component as assembly, component as subcomponent, component_link
where assembly.name=?
and component_link.assembly_id=assembly_id
and subcomponent.id= componentLink.subcomponent_id
BUT, this only goes down to one level which may be enough for most purposes,
but for stock monitoring (e.g. I've sold 10 of kit ZZA102, what effect does
that have
on my item stocks), you need to do it recursively.
With mysql ( and most other DBMS) the only alternative is to do the
recursion in a program -
ie
get all first level children
foreach get next level
foreach get next level
etc
Oracle has an excellent CONNECT BY extension to standard sql which does this
brilliantly, and I believe mysql AB are
planning to imlpement it sometime.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
