MySQL表格结构

Question

How would I go about setting up several tables that need to pull info from one another?

Here's my scenario...

Table Products (15 different items)

id, name, price, features

Table Orders

id, user_id, date, time, products, total_price

My problem is that when an order comes in it can have a variety of combinations when it comes to the products selected. Ex: 4x items with (id=3), 7x items with (id=9) etc etc...

How do I store that so that it can be easily retrieved and fast to process without over complicating things?

One way I can think of is storing products as a json array. Something like:

{"4":3,"7":9,"1":14}

I've never worked with stuff like that before so no clue how challenging data retrieval would be.

Any tips are greatly appreciated...

Answer 1

You need one to many, where a table order could have many products, you need to have a pivot table where you put the id order, id product and the number of products. When you do want to find all the products in an specific id order you will know all the products searching by the id order.

Here's an example:

DataBase design: one to many database?