Why it is not recommended to store product specifications in single column?

Question

I have multiple products which each of them may have different arrtibutes then the other products for example laptop vs t-shirt. One of the solutions that may come to mind is to have text "specs" column in "products" table and store the products specs in it as text key/value pairs like

for example "label:laptop, RAM:8gb". What is wrong with this approach? Why I can not find any web article that recommend it ? I mean it is not that hard to come to one's mind.

What I see on the internet are two ways to solve this problem :

1- use EAV model

2- use json

Why not just text key/value pairs as I mentioned

Possible duplicate of [Entity Attribute Value Database vs. strict Relational Model Ecommerce](https://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce) — philipxy, Sep 26 '19 at 18:06

score 1 · Answer 1 · answered Sep 26 '19 at 16:44

In SQL, a string in a primitive type and it should be used to store only a single value. That is how SQL works best -- single values in columns, rows devoted to a single entity or relationship between two tables.

Here are some reasons why you do not want to do this:

Databases have poor string processing functionality.
Queries using spec cannot (in general) be optimized using indexes or partitioning.
The strings have a lot of redundancy, because names are repeated over and over (admittedly, JSON and XML also have this "feature").
You cannot validate the data for each spec using built-in SQL functionality.
You cannot enforce the presence of particular values.

The one time when this is totally acceptable is when you don't care what is in the string -- it is there only to be returned for the application or user.

Why are you reluctant to use the solutions you mention in your question?

Fellow developer asked me to use string key value pair for each product and I was leaning toward json or EAV since I believe it is much faster to search in product specs if they were in EAV or json than string, am I correct? — mark820850, Sep 26 '19 at 16:52
In EAV, you can use indexes. With JSON, that depends on the database, but most support indexes on JSON. — Gordon Linoff, Sep 26 '19 at 17:10

score 0 · Answer 2 · answered Sep 26 '19 at 16:43

0

Text pairs (and even JSON blobs) are fine for storage and display, so long as you don't need to search on the product specifications. Searching against unstructured data in most SQL databases is slow and unreliable. That's why for variant data the EAV model typically gets used.

You can learn more about the structure by studying Normal Forms.

answered Sep 26 '19 at 16:43

PaulProgrammer

16,175
4
39
56

EAV faster than JSON faster than Text Pairs? I mean for reaching the specs – mark820850 Sep 26 '19 at 16:59
Yes, if you're fetching your entire dataset, then parsing JSON and text pairs in the application to filter. You could use `like` queries, but that's unreliable. – PaulProgrammer Sep 26 '19 at 17:01
Though getting the specs themselves from the product isn't "faster", but it's usually "fast enough". – PaulProgrammer Sep 26 '19 at 17:03
Would you explain how ````Like```` is unreliable? I did not understand what you mean – mark820850 Sep 26 '19 at 17:04
Also, don't optimize for speed until speed is a problem. [Premature optimization is the root of all evil](https://stackify.com/premature-optimization-evil/). – PaulProgrammer Sep 26 '19 at 17:05
What I mean what if I want retrieve laptops with 16GB ram? Then how ````Like```` is unreliable? – mark820850 Sep 26 '19 at 17:06
like is unreliable because you can search for a "size" "L" as `select * from blah where attr like '%size%' and attr like '%L%' would also get something that has a size, but has a color of 'Lavendar' – PaulProgrammer Sep 26 '19 at 17:07
Do you think you can accurately predict ALL the attributes and permutations that might exist? – PaulProgrammer Sep 26 '19 at 17:08
No I don't think – mark820850 Sep 26 '19 at 17:10

score 0 · Answer 3 · answered Sep 26 '19 at 17:13

SQL assumes attributes are stored individually in columns, and the value in that column is to be treated as a whole value. Support for searching for rows based on some substring of a value in a column is awkward in SQL.

So you can use a string to combine all your product specs, but don't expect SQL expressions to be efficient or elegant if you want to search for one specific product spec by name or value inside that string.

If you store specs in that manner, then just use the column as a "black box." Fetch the whole product spec string from the database, after selecting the product using normal columns. Then you can explode the product spec string in your application code. You may use JSON, XML, or your own custom format. Whatever you can manipulate in application code is valid, and it's up to you.

You may also like a couple of related answers of mine:

How to design a product table for many kinds of product where each product has many parameters
Is storing a delimited list in a database column really that bad? (many of the disadvantages of using a comma-separated list are just as applicable to JSON or XML, or any other format of semi-structured data in a single column.)

After seeing some of the crazy ways developers use JSON columns in questions on Stack Overflow, I'm beginning to change my opinion that JSON or any other document-in-a-column formats are not a good idea for any relational database. They may be tolerable if you follow the "black box" principle I mention above, but too many developers then extend that and expect to query individual sub-fields within the JSON as if they are normal SQL columns. Don't do it!

Why it is not recommended to store product specifications in single column?

3 Answers3