I have a design with an
orders table and
products table. Each order can have many products, so 1 order N products.
The default design would be to have something like this:
product_id, product_name, value
Because each order will have many products I'll have many rows just for one order, e.g.
order_id = 1, product_id = 1 order_id = 1, product_id = 2 order_id = 1, product_id = 3
I want to save the creation of multiple rows and just store the product_ids in a CSV way like:
order_id = 1, product_ids = 1,2,3
The table would look like this:
New updated orders table the difference being product_ids is now a VARCHAR which will hold many ids separated by commas.
If so how do I make the relationship? I can't link the product_id to the reference product_ids because it won't match. How do I create that relationship?
What are any other pros and cons or techniques I could use to achieve what I am trying?