Table design - is this good approach?


I need help from more experienced database designers & developers
in attempt to build a table named products and my main goal
is the following:

I would like to have the possibility of unlimited levels
of categories, subcategories, subsubcategories…

For the sake of simplicity, I will present my table with
3 fields only:


I populate the table with some products, and now if I run the following query

SELECT productid, parentid, productname_en
FROM `products`
ORDER BY parentid,productid
LIMIT 0 , 30

I will get the following results:

productid 	parentid 	productname_en

62 	0 	Books
63 	0 	Music
55 	47 	A Little History of the World
56 	47 	The History of the Ancient World
57 	47 	Ancient Rome: The Rise and Fall of an Empire
58 	47 	The Rise and Fall of Ancient Egypt
51 	48 	A Dance with Dragons
52 	48 	A Feast for Crows
53 	48 	The Night Eternal
54 	48 	The Infection
74 	48 	New Sub Cat
50 	49 	Beginning Database Design
59 	49 	Beginning Database Design Solutions
60 	49 	Visual Basic 2010
66 	61 	The Giant Book of Poetry
47 	62 	History
48 	62 	Literature
49 	62 	Computer Science
61 	62 	Poetry
64 	63 	Jazz
65 	64 	Smooth Jazz
67 	64 	Pure Jazz

Data from this table will be used for website.

This approach of unlimited category levels will give great flexibility to
the user to create his product list as he want it, but it will cause many problems to me to display the categories, subcategories, subsubcategories…(in the menu) and finally the products on pages when final category is clicked.

Can anyone give me peace of advice how to proceed with designing this table? I would like to be able easily to build the menu where I should display the different levels of categories and to be able easily to display the products on the page when appropriate category get clicked on the menu on the website

Should I add some more fields in attempt easily to determine if the product is final product or category?

Any suggestions will be deeply appreciated.

Regards, Zoreli

if you really and truly want to allow an unlimited number of subcategory levels, you have no choice but to change your data design to the nested set model

however, in practical terms, i feel that an unlimited number of subcategories is unrealistic

just how exactly would you propose to design the display of a menu that has 937 levels of subcategories? the user wouldn’t even be able to see the lower levels when you click open the menu!!

be practical, and don’t show any subcategories below a reasonable number, like a dozen levels or so

see Categories and Subcategories


Hi r397

Thanks for your response.

I realize that some limit must exits. You are right when you ask, how on earth the menu with 900 levels will be displayed.

What I am trying to do is just to avoid fixing the levels. I had many requests from customers where they were complaining that they do have subcategories in some category and do not have subcategories in some categories. (My previous CMS had fixed levels, categories, and on request, I was adding subcategories)

Now, if we assume that I will warn the customer to keep the menu simple, max 3-4 levels, is this good approach?

Regards, Zoreli

4 might not be enough :slight_smile:

but pick a number, and pubicize it

then back it up by an automated check

Let’s say I pick up 7 levels would be enough…


pubicize it…

publicize it where? in the documentation?

then back it up by an automated check


How can I do that? Not that good with databases…especially the querying the database is not really my speciality…:injured:

Thanks for your time and help

wherever you were going to warn the user… remember, you said “if we assume that I will warn the customer”

check the link i gave you, and write a query that searches for the 8th level