you need at least 3 fields CategoryID ParentID Name
CategoryID is the key, and identifies the category ParentID is the CategoryID of the parent category Name is the category name When you add a sub category you set its ParentID to the CategoryID To show all sub categories of CategoryID 99 SELECT * FROM table WHERE ParentID = "99" HTH Peter -----Original Message----- From: Alex Pilson [mailto:[EMAIL PROTECTED] Sent: 29 July 2003 06:06 To: [EMAIL PROTECTED] Subject: Category System schema Does anyone know the best schema to achieve a multi-level category system? I need a person to be able to add categories on the fly, but then also specify sub categories and even possibly sub-categories of sub-cats. I have found two possible ways inside one table with using parentID, groupID, etc. But the issue I am now running into is that I have to sort on groupID to get the items to "group" correctly when showing in a HTML drop down list. Which means I can't sort alphabetically. If I do the order is not right...using Lasso 6 with some looping code to indent the subs in the list. On top of that I would also like the ability to assign a priority field for listing in that order as well. There has to be some kind of solution to this that I don't see. Any ideas? Thanks! -- <---------------------------------------------------------------> Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] <---------------------------------------------------------------> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]