0

I am working on a website(PHP), and one of the features that the site should have is ordering items from the website, and the order will be like an array with an architecture similar to this:

order(
item1(item id, quantity)
item2(item id, quantity))

And i want to know what is the optimal way to store the order, i came up with two ways to do that, The first way which seems the easiest and simplest to me is by Using json_encode() or serialize() and storing the values in a single cell in the same table as the rest of the order information,

The second way is to make a new table with three columns, one for the order id, the second for the item id and the third for the quantity, so this way if the user submits an order with * number of items i will have * number of rows for the order in the order items table.

When searching about the best way to do that i discovered the the first way is basically blasphemy in an SQL database and that the second way is the correct way, but is it really applicable for all cases, i mean in my case i don't foresee a situation where i wont request all of the order or need queries to be done on an individual item in an order, I am leaning to chose the first way and blame it on Occam's razor, since for my website it seems like the simplest answer, is there something that i am not seeing(I mean i admittedly have little experience in coding let alone databases) which explains all the hate for this way? performance wise i mean.

0

If you just want somewhere to dump JSON then you're wasting your time with MySQL. Use a simple docustore. You will lose the ability to query the dataset in any meaningful way, but if storage and retrieval is your only requirement, there's no need for a relational database.

If you have more complex requirements, then you will be far better off using a relational database. e.g. You need to know how many items of type X are needed to fulfill all open orders; You need to know how many open orders currently exist for a specific customer, and what the total value of those orders is, etc etc. Using a docustore and JSON will severely limit your ability to answer questions about your dataset.

Finally, you're describing a many-to-many relationship between orders and items, right? One order can consist of multiple items, and one item can be a part of multiple orders, yes? In this case you need three tables - orders, items and an intermediate table to join them: orderitems. This design is what we call "normalised" - it will allow you easily query the data while minimising the amount of data stored.

  • I am not going to use this method everywhere, or anywhere except for this specific place, storage and retrieval is my only requirement for this information only not the rest of the site's data. – Ahmed AlHallak Aug 19 '16 at 3:20
  • 1
    Your point about why i should use RDBMS is why i did, And you make a good point in You need to know how many items of type X are needed to fulfill all open orders but i won't be using this feature, but i will need RDBMS's features in other cases. And yes i am describing a many-to-many relationship, and making three tables and joining them is what i meant to describe in the second way. So with this in mind is there a reason in your opinion why not to use the first way, especially since it's simpler and easier to use(for me at least) than the second "correct" way? – Ahmed AlHallak Aug 19 '16 at 3:38
  • You will create technical debt for yourself en.wikipedia.org/wiki/Technical_debt – Andrew Brennan Aug 19 '16 at 8:24
  • 1
    I don't foresee a situation where i will have technical debt because of this, because as i said previously the website isn't going to have any features that will incur me to reverse my code in this specific situation, but thanks anyway for the opinion and info, for now i think i will follow first method and decide later if need to change it. – Ahmed AlHallak Aug 23 '16 at 2:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.