CODEWITHSUNDEEP
mysqlrelational-databasenormalization
djt
djt

Reputation: 7535

MySQL - Break into more tables?

I'm creating an order system to to keep track of orders. There's about 60 products or so, each with their own prices. The system isn't very complicated though, just need to be able to submit how many of each product the person orders.

My question is, is it more efficient to have an 'orders' table, with columns representing each product, and numeric values representing how many of each they ordered.., example:

orders
    id
    product_a
    product_b
    product_c
    etc...

OR

should I break it into different tables, with a many-to-many table to join them. something like this maybe:

customers
     id
     name
     email

orders
     id
     customer_id

products
     id
     product

orders_products
     order_id
     product_id

Upvotes: 0

Views: 125

Answers (4)

M Noivad
M Noivad

Reputation: 106

Always build for future features and expansion in mind. A shortcut here or there always seems to bite you later when you have to re-architect and refactor the whole thing. Look up normalization and why you want to separate every independent element in a relational DB.

I am often asked “why make it a separate table, when this way is simpler?” Then remind them that their “oh, there are no other of this type of thing we will use” then later having them ask for a feature that necessitates many-to-many, not realizing they painted you into a corner by not considering future features. People who do not understand data structures tend not to be able to realize this and are pretty bad at specifying system requirements. This usually happens when the DB starts getting big and they realize they want to be able to look at only subset of data. A flat DB means adding columns to handle a ton of different desires, while a many-to-many join table can do it with a few lines of code.

Upvotes: 0

vyegorov
vyegorov

Reputation: 22905

Should you go for the 1st case, how will you keep track of the prices and discounts you gave to your customers for each product? And even if you have no plans to track it now, this is quite common thing, so might have request for such change.

With normalized schema all you have to do is add a couple of fields.

Upvotes: 0

aniri
aniri

Reputation: 1831

I'd also use the second way. If the db is simple as you say, the difference might not be much in terms of speed and such. But the second way is more efficient and easy to reuse/enhance in case you get new ideas and add to your application.

Upvotes: 0

Jack
Jack

Reputation: 301

I would break it out apart like you show in your second sample. This will make your application much more scalable and will still be quite efficient.

Upvotes: 1

Related Questions