One products table or multiple products tables?

[spaceman]

I'm working on a shopping cart application where the store contains a diverse range of products. What I'm trying to work out is whether to create individual tables tailored specifically for each product category, or whether to have a single table for all products with a fair amount of redundancy.

Simple example: Let's say I'm selling CDs and Books at my store. Common fields might include

Name
Description
Price
Weight
Height
Width
InStock

...but to fully describe a book I might want the
additional fields

ISBN
NumberPages

...and to fully describe a CD I might want the
additional fields

NumberTracks
NumberDiscs

So is the 'best' solution to go for a single 'Products' table for all products such as this:

CategoryID // Book or CD
Name
Description
Price
Weight
Height
Width
InStock
ISBN
NumberPages
NumberTrack
NumberDiscs

Or do I go for two tables

Books
-----
Name
Description
Price
Weight
Height
Width
InStock
ISBN
NumberPages

CDs
---
Name
Description
Price
Weight
Height
Width
InStock
NumberTrack
NumberDiscs


My guess is that there is no strictly right or wrong answer. The multi-table option is more efficient in terms of minimising redundancy, but the single table option is easier to code against because all products are in the same place.

Perhaps my question should be - what factors should make me lean more towards one approach as opposed to the other?

What does/would Amazon do?

Thanks.

[johnn]

I would use 2 tables. In the future you may need to add more fields to tables and they may not related to other product; or later, the store may cancel one product such as CDs, and add other products then it's a mess.

I bet Amazon would do the same as I do since each group takes care one division, and they don't want to see any fields that are not related to what they do.

[DLG_1]

okay here's your answer.

have more than just 2 tables.


combine what fields are identical into one table.

MASTER PRODUCT TABLE
---------------------
NAME
DESCRIPTION
:
:
:
CATEGORY_ID

under category_ID i would enter a category that would identify the product as a: CD, BOOK, SHOES, MASCARA, ETC.

have another table CATEGORY TABLE that describes these categories

CATEGORY TABLE
---------------
CATEGORY NAME
CATEGORY ID

then have a category description table

CD CATEGORY DESCRIPTION
--------------------
CATEGORY_ID
PRODUCT_ID
CD TRACKS
:
:


BOOK CATEGORY DESCRIPTION
--------------------
CATEGORY_ID
PRODUCT_ID
ISBN
TITLE
:
:
:

the reason you would do this is that if you just wanted to display the basics of a product you only have to query 1 table. when the person wants to get more details then you can involve the category tables and pull more info.

this would accomplish a faster query when displaying multiple products in that there isn't a bunch of white space or redundant stuff.

if you really want to get fancy under the category description table you can have a CROSS-SELL PRODUCT ID which would recommend a buyer a complementary product with their purchase.

if you ever wanted to sell hot dog buns this would recommend a set of hot dogs to the client while shopping. or milk when purchasing eggs...

man i'm hungry!

hope this helps.

dlg

[spaceman]

Hi DLG_1,

Thanks for your reply. I'm really glad I asked the question, because I really like your answer. I just wasn't fully comfortable with either of my original options, but couldn't quite figure out why. Now I know why.

Do you know from experience of having worked on big database projects that this solution is the 'best' or 'preferred' one? Just curious. Thanks again.

[DLG_1]

i think that this helps normalize the database better than any other options. i have worked on a couple of database projects, but i'm not an expert as wayne. (sitpoint)


dlg

[spaceman]

I've got another level of complexity to introduce into this issue of database design for a store with a diverse range of products...

How does one handle (or allow for) sub-categories and, potentially, sub-sub-categories (and so on) of products?

For example, you might want to sub-categorise books into 'hardback' and 'paperback', and CDs into 'Rock', 'Jazz', 'Dance', etc...

Categories
----------
CATID CATNAME
1 Books
2 CDs
3 Cameras
. ..

SubCategories
-------------
CATID SUBCATID SUBCATNAME
1 1 Hardback
1 2 Paperback
2 3 Rock
2 4 Jazz
2 5 Dance
.. ... ...

Or perhaps you would create separate sub-category tables - 1 for Books, another for CDs, another for Cameras, etc?

Or perhaps (and I think this is the probably the right answer) you have just one category table, with an ID and a PARENTCATID, eg.

Categories
----------
CATID PARENTCATID CATNAME
1 - Books
2 - CDs
3 - Cameras
4 1 Hardback
5 1 Paperback
6 1 Fiction
7 1 Biography
8 2 Single
9 2 Double
10 2 Triple
11 2 Rock
12 2 Jazz
13 2 Dance
. ..

Er, there's a problem here. What if I want to construct the query "show me all double Jazz CDs". Hmmm - not sure now if the above would be helpful.

So the same system would have to allow for the possibility that some products might not require sub-categories, and some products would have multiple levels of sub-categories.

In any event, what would the fields of the Master Products table look like given that it's now not a simple case of a 1 to many relationship between categories and products?

Hoping for some more assistance

[johnn]

Hi,
This thread might be helpful to you:
http://www.sitepointforums.com/showt...ight=recursive

[DLG_1]

you would keep my initial tables the same however, in the master table you would allow for two fields:


MASTER PRODUCTS
----------------
PRODUCT_ID (PK)
CATEGORY_ID (FK)
SUBCATEGORY_ID (FK)



CATEGORIES
--------------
BOOKS
CDS
ETC
:
:

SUB-CATEGORIES
-----------------
SUBCATEGORY_ID (PK)
CATEGORY_ID (FK)
NAME
DESCRIPTION
:
:


in the master table you would just have a "sub-category" number that would link the product to the sub-category table which would further describe the product. no biggy, really simple.

if you really want to learn more about this kind of stuff, I highly recommend the following book:
] FUNDAMENTALS OF DATABASE SYSTEMS
it has a "hefty" price, but it is literally the best book out there to learn how to design great databases.
(btw, I have it in my collection)


dlg

[spaceman]

in the master table you would just have a "sub-category" number that would link the product to the sub-category table which would further describe the product. no biggy, really simple.

I think this approach would work for most of the product catalogue I'm working with. But speaking from experience of other databases I've worked, you just know that the moment that you tell the client "the structure of the database is such that a product must belong to a sub-category, and a sub-category must belong to a category", is the moment he/she tells you that some products can't have a sub-category, and other products are better grouped with two levels of sub-category.

In other words, looking at the really big picture, it would be great to be able to develop a system that allowed for a variable number of sub-categories, and allowed for products to be quickly and easily associated with one or more sub-categories.

Maybe recursion is the only way, but I've dabbled there before and it can be hard to get (and keep) your head around it.

[DLG_1]

I agree that recursion would be good, but in order to allow your sub-categories to have additional attributes you'll have to do something other than recursion.


dlg

[DLG_1]

actually i take that back. you SHOULD do recursion and but add an attribute to tag a product if it is a sub-category (1, 2, 3, 4, etc.) or sub-sub-category. then link that field (sub-category field) to another table that will link to another table that finally describes the additional attributes.


i know that i didn't make sense, i'm even re-evaluating what i said. ??

BUT THEN AGAIN, WOULD SUB-CATEGORIES HAVE MORE ATTRIBUTES THAN THEIR PARENT CATEGORIES, MAYBE NOT. then you wouldn't have to worry about the sub-category tables....just another table to describe the sub-category name...nothing more.

dlg

[nagrom]

I usually make a 'products' 'categories' 'subcats' and then a 'prod_cats' table.

The prod_cats table then holds the foreign keys for the products, categories and subcategories. This allows one product to be assigned to multiple subcats.

This can allow for some flexibility, if only the foreign subcat ID is filled in, the foreign product belongs to the subcat (and the subcat table knows what maincat it belongs to). If only the foriegn cat id is filled in, it belongs to the maincat and not in a subcat.

Tke it one step futher to assign the same subcat to many maincats (a cat_subcat table)

But still, clients will always find some reason why one of their products is super-special and needs special treatment.

So I make another table called 'clients_are_jerks'...