SQLite & repeated fields

[Sarah Reichelt]

> 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