8

I have this project which stores product details from amazon into the database.

Just to give you an idea on how big it is:

[{"title":"Genetic Engineering (Opposing Viewpoints)","short_title":"Genetic Engineering ...","brand":"","condition":"","sales_rank":"7171426","binding":"Book","item_detail_url":"http://localhost/wordpress/product/?asin=0737705124","node_list":"Books > Science & Math > Biological Sciences > Biotechnology","node_category":"Books","subcat":"","model_number":"","item_url":"http://localhost/wordpress/wp-content/ecom-plugin-redirects/ecom_redirector.php?id=128","details_url":"http://localhost/wordpress/product/?asin=0737705124","large_image":"http://localhost/wordpress/wp-content/plugins/ecom/img/large-notfound.png","medium_image":"http://localhost/wordpress/wp-content/plugins/ecom/img/medium-notfound.png","small_image":"http://localhost/wordpress/wp-content/plugins/ecom/img/small-notfound.png","thumbnail_image":"http://localhost/wordpress/wp-content/plugins/ecom/img/thumbnail-notfound.png","tiny_img":"http://localhost/wordpress/wp-content/plugins/ecom/img/tiny-notfound.png","swatch_img":"http://localhost/wordpress/wp-content/plugins/ecom/img/swatch-notfound.png","total_images":"6","amount":"33.70","currency":"$","long_currency":"USD","price":"$33.70","price_type":"List Price","show_price_type":"0","stars_url":"","product_review":"","rating":"","yellow_star_class":"","white_star_class":"","rating_text":" of 5","reviews_url":"","review_label":"","reviews_label":"Read all ","review_count":"","create_review_url":"http://localhost/wordpress/wp-content/ecom-plugin-redirects/ecom_redirector.php?id=132","create_review_label":"Write a review","buy_url":"http://localhost/wordpress/wp-content/ecom-plugin-redirects/ecom_redirector.php?id=19186","add_to_cart_action":"http://localhost/wordpress/wp-content/ecom-plugin-redirects/add_to_cart.php","asin":"0737705124","status":"Only 7 left in stock.","snippet_condition":"in_stock","status_class":"ninstck","customer_images":["http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/51M2vvFvs2BL.jpg","http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/31FIM-YIUrL.jpg","http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/51M2vvFvs2BL.jpg","http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/51M2vvFvs2BL.jpg"],"disclaimer":"","item_attributes":[{"attr":"Author","value":"Greenhaven Press"},{"attr":"Binding","value":"Hardcover"},{"attr":"EAN","value":"9780737705126"},{"attr":"Edition","value":"1"},{"attr":"ISBN","value":"0737705124"},{"attr":"Label","value":"Greenhaven Press"},{"attr":"Manufacturer","value":"Greenhaven Press"},{"attr":"NumberOfItems","value":"1"},{"attr":"NumberOfPages","value":"224"},{"attr":"ProductGroup","value":"Book"},{"attr":"ProductTypeName","value":"ABIS_BOOK"},{"attr":"PublicationDate","value":"2000-06"},{"attr":"Publisher","value":"Greenhaven Press"},{"attr":"SKU","value":"G0737705124I2N00"},{"attr":"Studio","value":"Greenhaven Press"},{"attr":"Title","value":"Genetic Engineering (Opposing Viewpoints)"}],"customer_review_url":"http://localhost/wordpress/wp-content/ecom-customer-reviews/0737705124.html","flickr_results":["http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/5105560852_06c7d06f14_m.jpg"],"freebase_text":"No around the web data available yet","freebase_image":"http://localhost/wordpress/wp-content/plugins/ecom/img/freebase-notfound.jpg","ebay_related_items":[{"title":"Genetic Engineering (Introducing Issues With Opposing Viewpoints), , Good Book","image":"http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/140.jpg","url":"http://localhost/wordpress/wp-content/ecom-plugin-redirects/ecom_redirector.php?id=12165","currency_id":"$","current_price":"26.2"},{"title":"Genetic Engineering Opposing Viewpoints by DAVID BENDER - 1964 Hardcover","image":"http://localhost/wordpress/wp-content/uploads/2013/10/ecom_images/140.jpg","url":"http://localhost/wordpress/wp-content/ecom-plugin-redirects/ecom_redirector.php?id=130","currency_id":"AUD","current_price":"11.99"}],"no_follow":"rel=\"nofollow\"","new_tab":"target=\"_blank\"","related_products":[],"super_saver_shipping":"","shipping_availability":"","total_offers":"7","added_to_cart":""}]

So the structure for the table is:

  • asin
  • title
  • details (the product details in json)

Will the performance suffer if I have to store like 10,000 products? Is there any other way of doing this? I'm thinking of the following, but the current setup is really the most convenient one since I also have to use the data on the client side:

  • store the product details in a file. So something like ASIN123.json
  • store the product details in one big file. (I'm guessing it will be a drag to extract data from this file)
  • store each of the fields in the details in its own table field

Thanks in advance!

UPDATE

Thanks for the answers! I just want to add some more details to my question. First, the records are updated for a specific interval. Only specific data such as the price or the title are updated.

Second, I'm also using the json encoded data in the client-side so I thought at first it would be easier to just have it json encoded so I can easily use it in the client side without having to convert. Does this change your opinion about simply storing the fields in a regular table field in an RDBMS setup?

  • 4
    Storing a JSON blob limits searchability of that data – imagine I want to find all products where details.amount < 40 and details.node_category = "Books". A NoSQL database could provide this quite easily. I also enjoyed the reference to the White Star class ;-) – amon Oct 24 '13 at 8:09
  • I've addressed your update in my answer – Wim Ombelets Oct 28 '13 at 9:32
  • 1
    It is absolutely wise! Looks like the two answers given are stuck in their RDBMS past and are not open to the powerful and extremely efficient concepts intorduced by NOSQL. – user197803 Sep 25 '15 at 4:57
31

Size is not so much of an issue, the ability to query and maintain the data however is.

If, for example, Greenhaven Press decides they want to change their name to Greenhaven Press International, you'll have to find the record, deserialize it, change it, serialize it, pump it back into the database.

Consider this: does storing these objects as serialized data offer you a clear added value over storing it in a relational form? If the answer is no, then it might not be worth the hassle.

UPDATE

As far as your update of your question goes: I'm inclined to say no, it makes little or no difference. Whether you update one field or all of them in this json string is irrelevant because the whole process is identical.

Don't forget that your requirements might change; even though you're using json on the client side now doesn't mean you'll need json in the future. Storing your data in a relational form guarantees technology-independence while preserving relationships, data constraints and queryable metadata: this is where the true value of a relational db lies. Discarding those advantages will neither give you a performance gain nor make your application more scalable or flexible.

| improve this answer | |
  • "you'll have to find the record, deserialize it, change it, serialize it, pump it back into the database." In light of functions like JSON_SET or JSON_MERGE_PATCH this is no longer an issue. "does storing these objects as serialized data offer you a clear added value over storing it in a relational form?" I would probably start from the other way. Does storing these objects as normalized data offer you clear added value over storing it in a raw form? Normalizing data comes at a cost, especially if you normally operate on document model. – NeverEndingQueue Mar 19 at 10:59
  • Imagine you have a front-end application where the default natural format is the JSON / document object model. JSON field allows you simply to save that data without the need of doing the normalization transformation. Imagine now that your user wants to update his document. You can simply fed him with JSON. If you have normalized data you would have to denormalize it first. In this case using raw JSON saved you need of being responsible for normalization/denormalization. As always there is no best fit solution for all cases, but I believe JSON approach is underestimated. – NeverEndingQueue Mar 19 at 11:08
5

Is it wise to store a big lump of json on a database row?

No, generally it is a bad idea to store multi-valued data in one cell of a relational database because it goes against the basic structure/rules of RDBMS (as mentioned in the other answer).

However, you may look into the possibility of using one of the non-relational (no-sql) databases, such as MongoDB, which will help you store JSON objects and search for data inside those objects.

| improve this answer | |
  • 5
    IIRC - or PostgreSQL that has a new datatype especially for storing JSON data in a column and allowing you to query inside it (as if it was a dynamic set of columns) – gbjbaanb Oct 24 '13 at 12:13
  • 1
    @gbjbaanb thanks for the info, it was news for me. Reference: postgresql.org/docs/9.2/static/datatype-json.html – Aziz Shaikh Oct 24 '13 at 12:26
4

Assuming you don't have an immediate need to query over those JSON fields, then there's nothing wrong with this approach, so long as the performance hit of deserializing/serializing isn't a problem for your program.

Later on, let's say the requirement comes in to order/filter based on that "sales_rank" JSON field. You'd write a program to run over the data and deserialize the object, copying out that field to a dedicated column in the table, which would then be indexed. You'll need a custom serialization/update routine to make sure you keep this discreet column maintained in sync w/ the JSON object, but that's not hard.

This kind of hybrid approach can be very useful when you are still figuring out the app's functionality, as you get the fast development speed of NoSQL, with the benefit of relational modeling for the fields that need it.

| improve this answer | |
  • 2
    many RDBMS allow you to create indexes virtual/indexed columns that allow you to target those element in complex structures when you need to report on them later. You get the presentation performance of not having to constantly recompose you data and the reporting/query performance of the normalized data. – Matthew Whited Sep 25 '15 at 16:55
  • 1
    A college of mine just recently showed me that with the new JSON type in SQL Server, you can have dedicated computed columns and add indexes to them. Powerful and flexible. – Graham Jun 20 '17 at 19:25
2

It is sometimes a better fit for the application to store a complete document as one entity. It is not a coincidence that nosql and document databases are on the rise.

If you are aware of the drawbacks it can be an ok approack but you should also consider a real document database instead of inventing your own.

| improve this answer | |
  • I somewhat agree... there is a reason that more relational databases are starting to add special field types and functions to support complex JSON and XML queries. Some data just doesn't need to be normalized if the only thing you are ever going to do is present it as hierarctical data anyway. There are advantages to just using blob storage with your relational data versus running two seperate databases. – Matthew Whited Sep 25 '15 at 16:52
0

SQL Server 2016 also allows you to store JSON and process it, almost like all the NOSQL dbs out there. I still agree with most people out there that RDBMS approach is probably the best route and it gives you the most flexibility.

With the SQL Server 2016 JSON Approach, I do not know how much power it has (editing, deletions; I know you can search for fields/properties in the json rows) but its an option worth investigating.

https://docs.microsoft.com/en-us/sql/relational-databases/json/store-json-documents-in-sql-tables?view=sql-server-2017

| improve this answer | |

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