database design challenge

Anon

I am just learning about database design, and I have come across a table design challenge that I cannot seem to get past. Maybe you can help!

I am helping to design a database for a VERY small e-commerce web site. There are 1500 products, so in order for customers to be able to find what they are looking for, products must be broken into categories and subcategories. My clients have decided that Having 4 levels is enough...category, subcategory, subsubcategory, and occasionally subsubsubcategory. For instance, category shirts, subcategory t-shirts, subsubcategory cotton tshirts, and MAYBE subsubsubcategory BLUE tshirts. Each level has a description (so cotton subsubcat cotton t-shirts will have a description of the quality of the cotton). And one product can be in more than one category, and obviously one category will have more than one product.

That is my dilemma. I originally had 4 tables, one for cat & description, one for subcat & description, etc... But then I realized I was limited to only 4 tables. Maybe one day my clients would want to have a subsubsubSUBcat (dear lord!). So that didn't work. So then I figured one table for all levels, then another table linking each level to a productID. but then I had a table with the productID in there 8 times (a product in 2 categories, 2 subcats, 2 subsubcats, and 2subsubsubcats). This seemed too redundant as well. Then I tried having 3 tables, one with all the levels and their descriptions, one with all the different combinations possible, and one linking the productID to a structure. But this didn't work either b/c again I was limited to the number of COLUMNS i put in the structure table.

Does anyone have any suggestions? Again, I'm a novice so please try to use plain language when explaining!

Thanks!

nicole

Anon

This sounds like a many to many relationship (multiple products linking to multiple categories) -- so initially you will want to have 2 tables, one for product information and one for your categories ..

Now what you will want is a third table that will simply link categories and products together -- the table structure might be something like this:

id - int - auto increment - internal key
catid - int - category id
prodid - int - product id

Now you can place a product in multiple categories while having the flexibility of modifying an individual product record (not having one per category)

Another table will need to be used for your category organization .. So you might set it up something like this:

id - int - auto increment - internal key
catid - int - category id
parentcatid - int - the "parent" category id

So basically lets say you have the following categories:

products (1) - shirts (2) - short sleeve (3)- white (4)

What you will want to do is have the following records in the table to explain this layout:

id | catid | parentcatid
1 | 1 | 0 (no parent)
2 | 2 | 1
3 | 3 | 2
4 | 4 | 3

Now lets say you want to add another sub category under products, pants (5) -- you could add the following to the table:

id | catid | parentcatid
1 | 5 | 1 (no parent)

Now, when you display the category, you can do a search for the parentcatid and pull any additional catid's that act like a sub category .. from this you can then pull products associated and the product info from the table ..

This is particularly nice because it will allow you to have unlimited categories in any type of heiarchy and have the ability to place the same product in multiple categories (you could technically build on this idea and have category keywords and have the category auto-populate with products that fit a predefined set of attributes (ie white cotton shirts) ..

Hope this helps.

Anon

Quick correction .. when adding the pants category, the table should look like:

id | catid | parentcatid
1 | 5 | 1

John

Anon

Table1: Categories
cat_id
cat_name

Table2: SubCategories
subcat_id
subcat_name

Table3: SubSubCategories
subsubcat_id
subsubcat_name

Table4: SubSubSubCategories
subsubsubcat_id
subsubsubcat_name

Table5: Products
product_id
product_name
cat_id
subcat_id
subsubcat_id
subsubsubcat_id

create select-list-boxes that are populated with cat_name's and so on (values would be the id's) Do this by pulling from the DB.

typical SQL:
select * from products where cat_id=$cat and subcat_id=$subcat_id....etc.

if your client chooses to add another subcategory, they simply add a table and add an appropriate column to the products table.

how bout that?

Anon

Build it like you would a threaded message board. You have a category table that holds the category_id (primary key) the name and description of the category, and an optional parent_id for sub categories.

Categories:
category_id int not null primary key
name varchar(80)
desc varchar(255)
parent_id int

Now when you add a category it's either a root level category or a sub category, root level categories will have 0 for the parent_id and sub categories will have the category_id of it's parent as the parent_id. This way you can navigate back up the category tree to the main category.

Now your second table is just a table of product_id's and category_id's that links a given product to a category so your "Blue cotton t-shirt" would be linked to at least 4 categories (shirts, t-shirts, cotton, and Blue).

Products_x_Categories
product_x_category_id int not null primary key
product_id int not null
category_id int not null

Let me know if this doesn't make any sense and I'll try to clarify things.

Anon

Nice much better explanation than I gave. ;->

toma42

Er, John had it right Craig. Your way would be wrong and hard to maintain.

Anon

yeah well, I was thinking off the top of my head. I hadn't read John's first... um.. my dog ate my homework and I had a headache... there was flood... a terrible earthquake.... AAAAHHHHHH

Anon

thanks for the compliment 🙂

Anon

You guys are GENIUSES!!!!!

I had to sit there and actually POPULATE the table with fake info b/c i wasn't sure I understood correctly. But it all makes so much SENSE now!

Thank you, thank you thank you. You have LITERALLY just saved me from two days of confusion, deliberation, and frustration.

I love the internet!

nicole

ps. craig...no excuses. my dog eats my homework and i have constant headaches and somehow it doesn't affect the speed of my neurons nor the efficiency of their connections. :-) (you set it up the way i did at first... then i realized how inefficient it was!)

Anon

Now tell my boss. ;->