Database design: name-value pairs - good or bad?

Question

Let's say I have an online store in which each product has one category (and there are hundreds of categories to choose from) assigned to it (for example, “book”, “portable DVD player”, etc.). If I need to provide descriptive fields for each category (for example, “author” will be a field for the category “book”), what is the best way to present this in the database?

Option 1 (name value pairs):

=========================== field =========================== - field_id - category_id (FK, referring to category like "book") - name - value 

This means that I can rely on one table for any category. I am concerned that the rotation needed to display this data next to other books may be a potential problem.

Option 2 (separate tables):

 =========================== book_field =========================== - book_field_id - book_id (FK, referring to the actual book) - author - title - publisher - date_published ... 

This means that I need a table for each category.

NOTE: not what I think is important, but the category will come from a hierarchy of categories (e.g. Electronics → DVD Players → Portable DVD Players).

Answer 1

My $ 0.02 is one table for each category. If everything is really different, then hug this and customize your tables accordingly.

Naturally, if some of the entities have common data that can be abstracted / normalized, but I think that the name / value pair parameter that you have there may lead to some unpleasant problems with read / query performance in the future.

Answer 2

You really want to limit only one category. I mean, can you think of any case when your product may belong to several categories?

Well, anyway, one solution that may be useful to you:

UPDATE (multiple layers added)

 ======== products ======== - product_id - name ==================== categories_products ==================== - category_product_id - product_id (FK) - category_id (FK) =========== categories =========== - category_id - name ============================= products_detail_values_types ============================= - product_detail_value_type_id - product_id (FK) - detail_value_type_id (FK) ==================== detail_values_types ==================== - detail_value_type_id - detail_value_id (FK) - detail_type_id (FK) =============== detail_values =============== - detail_value_id - value ============= detail_types ============= - detail_type_id - name 

You have a type called a "director":

  detail_type: detail_type_id: 100 name: "director" 

And some meaning:

 detail_value: detail_value_id: 200 value: "James Cameron" 

Type and value mapping:

 detail_value_type: detail_value_type_id: 300 detail_value_id: 200 detail_type_id: 100 

What details relate to the product:

 product_detail_value_type: product_detail_value_type: 400 product_id: 500 detail_value_type_id: 300 

Then we have the categories:

 category: category_id: 600 name: "movie" 

And category display:

 category_product: category_product_id: 700 product_id: 500 category_id: 600 

And finally, the product itself:

 product: product_id: 500 name: "Aliens" 

Answer 3

I would suggest you base your design on the one on which the web tag is based.

Let me explain to you:

You will need 4 more tables for your main table of objects.

First: the name tag table, this is the base identifier of the table | name that will store the attribute of the object: "author", "size", "weight"; anything an object can describe

 tag_table id varchar(36) tag varchar(36) 

Second: this table will match the value with the tag names stored in the tag_table value. It has the same design.

 value_table id varchar(36) value varchar(36) 

Third: determine which value is a tag.

 tag_value id_pair varchar(36) id_tag varchar(36) id_value varchar(36) 

Fourth: joins the object with its data, which shorten it

 object_tag_value id_object varchar(36) id_pair varchar(36) 

And finally, your table of objects.

Hierarchical system implementation :

For the one-to-many or many-to-many hierarchy, an additional table is implemented that links two objects:

 object_relation id_parent varchar(36) id_son varchar(36) 

For many-to-one (Employee table with manager_id, for example) just add id_parent as a member of your object.

With this scheme, you will be very scalable, and the object can now have an infinite characteristic that you are no longer limited to. In addition, you avoid data redundancy because the tag name is unique.

I hope I was clear enough and helped you,