I try a solution like this. It's kind of a tree [so your warned that this structure will be a waste of time if you have a subkit to a subkit to a... ]
KITS kt_id int kt_things..... parent_kt_id_fk int constraint fk_KITS foreign key (parent_kt_id_fk) references kits(kt_id) For details consult the manual; it won't work this create table :) I reccomend this when u have something like a lot of kits, and some of them have only one level of accesories [other kits]. ----- Original Message ----- From: "Donald Tyler" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, July 30, 2003 6:42 PM 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 > > 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]
