0

Prob: I have a table which contains list of toys(dolls).while purchasing user can choose options(black dress or white dress) and accessories(earring and anklet).

options 1: First i thought of adding 2 different table for options and accessories.
http://imgur.com/a/FnE6ODatabase schema

But as i am never going to filter/search on these table so i thought of putting these options as JSON in seperate column.So that frontend can easily render JSON as options to user enter image description here

QUES: As i have to add total price from options and accessories Is this ok to store these type of details as JSON format?
I am also open for other suggestions which is easily maintainable

Rick James
  • 135,179
  • 13
  • 127
  • 222
user2858738
  • 520
  • 4
  • 15
  • Yes, but only for random options, _not_ for structure that you give an example of. See my [_EAV blog_](http://mysql.rjweb.org/doc.php/eav). – Rick James Dec 07 '16 at 23:46
  • Your question is very similar to this: http://stackoverflow.com/questions/31972056/mysql-embedded-json-vs-table/31977656#31977656 – Hazzit Dec 08 '16 at 13:06

1 Answers1

1

If you are using an sql-database (and it looks like you are) I really advise you against keeping the data as JSON inside.

Even though MySQL 5.7.8 gives you a good way to access data inside json types, you never know when you are going to need to filter/search/group based in that data.

A quick example that jumps here is that you want to give a user that buys a new doll (productid == 1) a list of all the add-on that people that also bought that doll (productid == 1) purchased in the last week (or month). If you are going to save that data as JSON it will be very hard to query.

I know that your example shows only the meta-data of the add-ons and options, but it's "easy" to go and also save the actual purchases that way (just as add-on to the purchase-row itself).

I advise against it.

Dekel
  • 60,707
  • 10
  • 101
  • 129