How to construct dynamic self referencing many-to-many relationship ?
I have a problem that i cannot overcome.
I have tables:
Types:
id
name
Products:
id
name
type_id
product_products
parent_id
child_id
Let's assume that we have several products in Product table. Some of them are:
(Name is not relevant)
CB1234 - products.type_id -> cardboard
CBB999 - products.type_id -> cardboardbox
CBP321 - products.type_id -> paper
TSH123 - products.type_id -> tshirt
FAB321 - products.type_id -> fabric
THR321 - products.type_id -> thread
Now i want to tell that product_product relationship that i have cardboardbox that is made of cardboard and paper types of products. In cardboardbox-cardboard and cardboardbox-paper relationship i want to define how much percentage of product is used (consistency) to make cardboardbox product(lets say 80/20, not relevant)
I have different cases for different products. Now i want to define relationship between tshirt-fabric and tshirt-thread but instead of consistency i want to define fabric and thread cost for this product.
Products and product parameters count is not fixed. There could be 500 different products and different relations between them. What are good practices for this problem? There are few ways that i came up with
In both cases there are things i don't like in design, but maybe those are correct way to do that and maybe i have designed my database wrong from the start.
1) would be the way to go - not sure there would really be "lots" once you worked out everything you wanted to store
product_product_consistency product_product_cost (could this instead be represented as product_cost, then calculated from product_product_consistency or some such?)
What is the final usage, what kind of questions do you want to ask the data?