How to store multiple reference items in a field?

G

ggagnidze2012-07-11 18:45:50

MySQL

ggagnidze, 2012-07-11 18:45:50

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.

Reply

Answer the question

In order to leave comments, you need to log in

8 answer(s)

D

Dmitry Sergeev, 2012-07-11
@ggagnidze

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

J

JustLuckyGuy, 2012-07-11
@JustLuckyGuy

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.

A

adminimus, 2012-07-11
@adminimus

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

G

ggagnidze, 2012-07-11
@ggagnidze

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.

M

Mikhail Osher, 2012-07-11
@miraage

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

D

dali, 2012-07-11
@dali

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.

S

Sild, 2012-07-11
@Sild

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

C

chuck, 2012-07-12
@chuck

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