Products table design problem

Databases
1

Hi

I have developed small CMS system for the websites that I create for customers. However, from time to time, I have difficulties with my products table,and I need help to solve it.

To display the customer products, I have the following tables structure:

products_categories
products_subcategories
products

products_categories has following fileds:
catid
description
categoryphoto
sortorder
visible

products_subcategories has following fileds:
subcatid
catid
description
subcategoryphoto
sortorder
visible

and products table has the following fileds:

productid
catid
subcatid
productname
price
description
productphoto
productpdf
isnewproduct
showonhomepage
productvideo
sortorder
visible

Now, if my customer has sub categories, I use this structure and I am fine, more or less. When the customer has no subcategories, I just do not place the table products_subcategories, and I just remove the subcatid from the products table, and I am more or less fine again.

My problem arise when customer told me, you know, I have subcategories for some products, but I do not have subcategories for other products. Or, when customer tell me, you know, in category books for example I have 3 levels of subcategories, but in the category DVD, i have no subcategories at all. Same problem arrise if he ask me: Can i have 5 levels of subcategories everywhere?

Can anyone propose me a products table design which will suit my customer needs?

Regards, Zoreli

2

use this structure for your categories and subcategories – Categories and Subcategories

then link each product to whichever level of category or subcategory it belongs to, it doesn’t matter whether at the top level (category) or at the 3rd level (sub-subcategory) or wherever, because the product link will always be to the exact same column in the categories table, and the categories table determines what level it’s at

3

Hi r937

Thanks for answering my question…

If i understand it right, I should keep the products table(right?) and use the table from the link as my categories-sub categories table.

While this sound great…I don’t know how can I edit a product and the category then? Let say that I want to have category photo, description etc…but no price, isnew,showonhomepage in the edit mode for the categories…but I do need those fields for products.

How to solve that, any idea, or recommendation?

Also, how to cover the possibility that customer may delete a product (which is actually a category). Should I implement on cascade delete in such case?

On the end, is it smart idea to prevent the customer of entering unlimited levels? Because if he did so, the menu of the site will be real mess…

Any advice will be deeply appreciated.

Once again, thanks for sharing your knowledge.

Regards, Zoreli

4

yes, but remove the “subcatid” column

solve what? those columns stay in the product table

you might have difficulty cascading a delete through subcategories, but you can definitely implement a cascading delete from the categories table to the products table

good observation, yes, unlimited subcategories would mess up most web pages that try to display them…

5

Hi r937

Once again, thank you very much for spending your time to help me.

Yes, I realize that, thanks for clarifying however

Well, categories,sub categories in general all levels that are not products, does not need all fields. it is quite confusing when you send the customer to edit a product, which is actually a category and he find inside price field or isnewproduct field…I would have to implement some if and else code which if the product is actually category, then I should display in the edit just the name, description and image, otherwise I should display complete table. Can you propose any other solution for this?

Well, here as well I might employ the strategy if the product id is found in parentid field in the category table that mean that it is category or sub category and display some warning before to delete the actual record, is that good way to solve this?

Since I never had the situation more than 5 levels to be requested by the customer, I may fix the back office to accept maximum 5 levels, even I have no idea how I would implement that yet…

Any other comments & advices will be very much appreciated.

Regards, Zoreli

6

hold it right there :slight_smile:

how can a product be a category? i think you need to clarify this in your own mind, and make a clear distinction

categories and subcategories should have only description, categoryphoto, sortorder, and visible – all product related columns should be in the products table

there should be no overlap of columns

so when you edit a category, it’s a category, and when you edit a product, it’s a product

there should be no instances of a “product, which is actually a category”

7

Hi r937

This is the point that I get lost with adjacency model. Now, the question is: Should I have table products_categories as well? And the catid from that table should be parentid in categories table??? Help me to get out from the mess that is created in my head…

Regards, Zoran

8

i’m not sure i understand your confusion

you need only two tables –

categories
catid – primary key
parentcat – foreign key, nullable
description
categoryphoto
sortorder
visible

products
productid
catid – foreign key
productname
price
description
productphoto
productpdf
isnewproduct
showonhomepage
productvideo
sortorder
visible

9

Hi

Now I see…thanks for clarifying… really simple. However, one last thing that confuses me, why should parentcat be foreign key? Where it will point?

Regards, Zoreli

10

Hi

Now I see…thanks for clarifying… really simple. However, one last thing that confuses me, why should parentcat be foreign key? Where it will point?

Regards, Zoreli

11

because that’s what it is :slight_smile:

take a wild guess :slight_smile:

and if you’re not sure, read that article again that i linked to earlier :smiley:

12

Well please correct me if I am wrong, but…that mean that I will need three tables…which is fine, just in one of previous answers you said

i’m not sure i understand your confusion

you need only two tables –

===============================

I read the article. OK, I got it now.

I will have three tables product table, products_categories and categories(from the link). This design will leave me to have unlimited levels, but due the eventual mess with the navigation, I have to reduce it to some number, I will decide how much it will be.

THANK YOU SO MUCH… This problem was real nightmare for me, luckily, you solve it…
Once again, thanks for sharing your knowledge with me and the comunity of this forum.

Regards, Zoreli

13

the products_categories table is really only necessary if you want to allow a single product to belong to more than one category or subcategory