MySQL table(s) structure(s)

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...

Maybe get into http://stackoverflow.com/questions/108699/good-php-orm-library — JustOnUnderMillions, Dec 07 '16 at 17:14
Sounds like you need to read a good textbook on database design. — Barmar, Dec 07 '16 at 17:32

score 1 · Accepted Answer · edited May 23 '17 at 10:30

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?

edited May 23 '17 at 10:30

Community

1
1

answered Dec 07 '16 at 18:08

migueref

302
1
7

hey @migueref, that makes a lot of sense. Thanks! – cnapsys Dec 07 '16 at 21:58
It's been a pleasure, you should read more about relations in da tabases, it could be helpful. I hope you can vote for me. – migueref Dec 07 '16 at 22:02

MySQL table(s) structure(s)

1 Answers1