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"