Donald,
I've done this before - it wasn't nice. The best solution I came up with
was to store the kits in the item table as though they were items. So
the structure is: (Lets call a 'Product' an Item or a Kit depending on
the 'prod_type')
PRODUCT KIT CONTENTS PRODUCT (as item)
prod_id<----------------->kit_id
prod_type {Item | Kit} prod_id<------------->prod_id
The product table also store the kit/item description.
This structure allows n levels of kit building, and (obviously) allows a
single query to retrieve a 1 level kit breakdown.
You question about a single query to access this tree structure is a bit
of a classic (and I'd be interested in other peoples answers) but in
reality I found that it was sufficient to report to 1 level to kit
breakdown only (since printing a tree structure on a report is messy).
So my report said, for example:
KIT1 contains
ITEM1
ITEM2
KIT2
KIT2 contains
ITEM3
The issue came when trying to decide if we had 3 of KIT1 in stock. I
couldn't find a single query (that allowed n levels) so I wrote a
function that iterated the tree.
Andy.
Donald Tyler wrote:
>
> 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
>
> The only way I could think of to allow a kit to be contained within a kit:
>
> Add another field to KIT_CONTENTS called Sub_Kit
>
> Which seems like a dumb way of doing it, because first of all, most kits
> don't have sub kits, so that would be a wasted field in most cases.
> Secondly, as far as I know the application logic would have to do multiple
> queries's to get the sub_kits.
>
> I really hope there is a simple and more logical way to do this, preferably
> allowing me to run a SINGLE query that will show a kit with all its sub
> kits. Otherwise I would need to have repeated queries from the application
> logic, and I really want to avoid that.
>
> Please help, thanks.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
