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:

orders table

order_id, product_id

Products table

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.

order_id, product_ids

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?

  • 3
    Do not store comma separated values in a single column. You typically use an order_items table that stores the products used in an order. – a_horse_with_no_name Aug 22 '18 at 6:36

Your suggested design violates first normal form, which states that every attribute must be atomic. If you store multiple items as a CSV string, you are going to experience many challenges querying it later. You also won't be able to index it efficiently, and of course you won't be able to enforce referential integrity.

Orders are typically stored in an orders table, with attributes pertaining to the order, such as order date, and customer. Order items is a different entity, and should be modeled as an order items table, whose key should be a composite key consisting of order ID and the item identifier.

Follow tried and proven designs, don't try to re-invent the wheel, and definitely don't violate first normal form. There are many good reasons for it.

  • Thought so just thought if anyone tried anything similar or had any other ways. – Abu Nooh Aug 22 '18 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.