> -----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]