1

I'm creating my own component for storing products (shopping cart). The most of products usually have their prices and other attributes. What way of storing these attributes in the DB is easier and better: in a single column or as the part of json? I.e. like:

id     name      price        attribs
1    Product1    1000    "field1":"value1"
2    Product2    2000    "field1":"value2"

OR

id     name                  attribs
1    Product1    "price":"1000","field1":"value1"
2    Product2    "price":"2000","field1":"value2"

The question is especially about storing thousands of products. What way is faster when Joomla and database work together and in which case server load is less? And when is it more right to store data as json instead of a single column? Please explain

4

There are several ways to store data in databases, how you wish to do it depends on your application, it's depth and future growth.

I'm no expert in this area, but I do have the experience to know that JSON-storage rarely is efficient, ultimately it strikes back at you.

If you only plan on storing this simple data in one simple table, maybe it seems easier to just stringify it into JSON, but history clearly shows that structuring databases inevitably win in terms of efficiency, letting other people into your code and building the application. If you for instance are going to change the price of a single product, you need to pull down the data, unserialize the data, modify the price and then put it back, whilst with multiple columns you can do this in one simple database call.

Another problem could occur should you attempt to do more complex database queries, such as getting a list of all products which has the price of 50 or lower, for example, or has a special column to a special value. Also if you want to join in other tables, this will render complicated.

In my personal experience, I strongly recommend using multiple columns. The only case where I'd use JSON is if I should need dynamic columns, which tends to be rare.

0

I guess you should do it separately. Fetching price for a single item from thousands will be easier here. Further you can implement discount/offer system if you do it separately. I do separately store price in database. I use json in case of office working hours and days, things like that. I hope this helps :)

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.