What's the best design for a logical structure that contains different properties of various types for different entity categories? (MySQL or NoSQL)

Question

I'm designing a MySQL database that needs to contain a list of products. Those products, based on their category, will need to have a list of various properties.

Let's say all products in category "displays" will need to have a property "name" (varchar), and "description" (text), whereas the products in category "keyboards" will need to have properties "cord length" (float) and "keyboard highlighting" (boolean).

I came up with a following structure:

Table product:

• id_product INT [PK]
• category ENUM('displays','keyboards', ...)

Table property:

• id_property INT [PK]
• name VARCHAR(50)

Table value:

• id_value INT [PK]
• id_product INT [FK]
• id_property INT [FK]
• type ENUM('varchar','boolean', ...)

Table value_varchar

• id_value INT [FK]
• value VARCHAR(255)

Table value_boolean

• id_value INT [FK]
• value BOOLEAN

As you can see, the structure contains two basic tables product and category and a many-to-many relation in value table, but it doesn't keep the property values in value table (because they can be of different types). Instead, the values are kept in additional type-specific tables value_varchar, value_boolean and so on (there should be more such tables, like value_text etc.).

Such structure seems suboptimal to me, as if I want to retrieve all values for a particular product, I would need to retrieve the value types at first and then run additional queries to retrieve the values from the tables related to those types. However, I can't come up with anything better right now as far as MySQL is concerned. Maybe NoSQL is an answer to such a construct? If so, which one of NoSQL engines I should look into? I have no experience with NoSQL.

Thanks in advance for helping me out.

Answer 1

I recommend you to read about the dangers of the Entity-Attribute-Value pattern on this presentation by @Bill Karwin.

One of the solutions is storing NoSQL-like data in a serialized BLOB, if you do not need to read and write individual properties. That is, storing a key (product) as the primary key, and the variable property-value pairs all together in a single blob. This is a non-normalized structure, leaving the responsibility of the constraints to the application layer, but it works great for certain patterns (the same that usually work well with NoSQL solutions). Even if you need indexing, you can create inverted indexes on a separate table as needed.

This and other solutions can be seen on this presentation in the latest MySQL Conference, including when you should search for alternative database engines.

Answer 2

In addition to what @jynus has written above - check out my answer to a similar question here (see reference to Celko). Furthermore, the MySQL ENUM type should be avoided for many reasons - 8 reasons as to why they are "evil" can be found here.