Say I've a web-based store in which each product includes a single category (and you will find 100s of groups to select from) designated into it (e.g. "book", "portable DVD player", etc.). Basically required to provide descriptive fields for every category (e.g. "author" will be a area for that "book" category), what's the easiest method to represent this inside a database?

Option 1 (title value pairs):

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

What this means is I'm able to depend on a single table for just about any category. I am concerned the pivoting needed to show this data alongside along with other books may well 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
...

What this means is I want a table for every category.

NOTE: not too It matters, however the category could be from a hierarchy of groups (e.g. Electronics -> DVD Gamers -> Portable DVD Gamers).

My $.02 Body table per category. If situations are actually different, then embrace might setup your tables accordingly.

Naturally if a few of the organizations have common data, that may be abstracted/stabilized out, however i think the title/value pair option you've available online for can lead to some nasty readability/query performance issues in the future.

Are you certain you need to restrict simply to one category. I am talking about, are you able to think about any situation in which you product could be fit in with multiple groups?

Well, anyway here's one solution which can be helpfull for you:

UPDATE (couple of 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've type known as "director":

 detail_type:
   detail_type_id: 100
   name: "director"

Plus some value:

detail_value:
  detail_value_id: 200
  value: "James Cameron"      

Mapping of type and cost:

detail_value_type:
  detail_value_type_id: 300
  detail_value_id: 200
  detail_type_id: 100

Which particulars fit in with product:

product_detail_value_type:
  product_detail_value_type: 400
  product_id: 500
  detail_value_type_id: 300

Only then do we have groups:

category:
  category_id: 600
  name: "movie"

And category-product mapping:

category_product:
  category_product_id: 700
  product_id: 500
  category_id: 600

And lastly the merchandise itself:

product:
  product_id: 500
  name: "Aliens"