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?
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).