How to store multiple reference items in a field?

Question

MySQL

Good day.

Suppose a company is engaged in the delivery of goods.
She has a directory (MySQL table) of products:

1 Orange juice
2 Pineapple juice
3 Birch juice

The client turned out to be a gourmet and ordered Pineapple and Birch juice.

Question: how to correctly reflect this order in the table of orders from the point of view of MySQL?
Do I need to specify product IDs separated by commas in one field, or should I do it differently?

Thank you.

Answer 1

I would do so. Two tables:
1. user_order table - stores userId and orderId (generated when the user has placed an order, orderId is the primary autoincrement id for the orders table)
2. order_item table - stores orderId and itemId

Answer 2

binary number. or whatever it's called.
01 - orange
10 - pineapple
11 - birch
i.e. if the client ordered all 3 juices, then it will be 100 (all numbers in binary). respectively you translate in decimal and in a way.

Answer 3

three tables can be used: nomenclature of goods, orders, positions in orders. In the third table, write two rows (Pineapple and Birch Juice) with reference to the row of the "Orders" table by order id, for example.
Of course, you can put orders in one table and write positions in one more field. Take whatever format is convenient (plain text, xml, json, serialized objects...), you still have to parse it separately with code. The disadvantage of this solution is that maintaining the referential integrity of the base will be very hemorrhoids. Well, the obvious overhead for storage and processing

Answer 4

I can't believe that there are no ready-made solutions. This is the task of any online store, of which there are a great many. L should be Best Practice.

Answer 5

NoSQL to help you.
If, nevertheless, MySQL - I agree with JetMaster

Answer 6

It doesn't matter if it's MySQL or any other relational database. Normalized to at least 3rd normal form. Everything, as painted by JetMaster and savant.

Answer 7

It seems that storing 2 values ​​in one field contradicts either normal forms or relational databases in general

Answer 8

If it is possible to switch to Postgres, then there is a data type - an array.