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

Say I have an online store wherein each product has a single category (and there are hundreds of categories to choose from) assigned to it (e.g. "book", "portable DVD player", etc.). If I needed to provide descriptive fields for each category (e.g. "author" would be a field for the "book" category), what is the best way to represent this in a database?开发者_JAVA百科

Option 1 (name value pairs):

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

This means I can rely on one table for any category. I'm concerned that the pivoting required to display this data side by side with other books might be a potential problem.

Option 2 (individual tables):

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

This means I need a table for each category.

NOTE: not that I think it matters, but the category would be coming from a hierarchy of categories (e.g. Electronics -> DVD Players -> Portable DVD Players).

My $0.02 - one table per category. If things are truely different, then embrace that and set up your tables accordingly.

Naturally if some of the entities have common data, that can be abstracted/normalized out, but I think the name/value pair option you have up there could lead to some nasty readability/query performance issues down the road.

Are you sure you want to restrict only to one category. I mean, can you think of any case where you product can be belong to multiple categories?

Well, anyway here's one solution which might be usefull to you:

UPDATE (few 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 type called "director":

 detail_type:
   detail_type_id: 100
   name: "director"

And some value:

detail_value:
  detail_value_id: 200
  value: "James Cameron"      

Mapping of type and value:

detail_value_type:
  detail_value_type_id: 300
  detail_value_id: 200
  detail_type_id: 100

Which details belong to product:

product_detail_value_type:
  product_detail_value_type: 400
  product_id: 500
  detail_value_type_id: 300

Then we have categories:

category:
  category_id: 600
  name: "movie"

And category-product mapping:

category_product:
  category_product_id: 700
  product_id: 500
  category_id: 600

And finally the product itself:

product:
  product_id: 500
  name: "Aliens"

I would suggest you to base your design on the one that internet tag are based on.

Let me explain you :

You will need 4 tables more your main object table.

First one : The name tag table, this is a basic table id | name, that will store attribute of object : "author", "size", "weight"; anything that can describe an object

tag_table
id    varchar(36)
tag   varchar(36)

Second one : This table will match the value with the tag names stored in the tag_table value. It does have the same design

value_table
id    varchar(36)
value varchar(36)

Third one : will determine which value is which tag.

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

Fourth one : will join an Object with its data that carectrize it

object_tag_value
id_object  varchar(36)
id_pair    varchar(36)

And finally your object table.

Implementing hierarchy system :

For the one-to-many or many-to-many hierarchy implement an extra table that would relate the two object :

object_relation
id_parent varchar(36)
id_son    varchar(36)

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

With this schema you will be highly scalable an object can have now an infinite characteristic you are not limited any more. Plus you avoid data redundancy because tags name are unique.

Hope I was clear enough and that it helps you,