> A product has a list of items. (a one to many relationship) but an item can > belong to many products ( a many to many relationship) > > This would imply three tables; Product, Product-Item, Item > > The Product table has; > product_name, product_id etc. > The item table just the raw item details; > name, item_id, qty_on_hand etc > The Product-item table has their relationship (i.e is the repeating field). > So in your case this table could have the following fields; > product ID, Item ID, Number_used > > Then each time a product is "made", you would look up the records in the > Product-item table to get a selection of rows from the Item table. > Step through each row of this selection (of the product_item) and for each > item deduct the "Number_used" value from the associated record in the Item > table.
Thanks to everyone who responded, but I think James has come up with the perfect solution for my particular problem. As you all can tell, I'm not a database expert, so didn't think of anything like this, but once someone else points it out, I can see that it will do exactly what I want. Cheers, Sarah _______________________________________________ use-revolution mailing list [email protected] Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
