Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Nov 29, 2012 08:48 PM by asplearning
Nov 27, 2012 09:22 PM|LINK
Hello everyone, I am trying to design my database and got stuck. So please give me some advice. Here are my tables:
Parent_Category: CategoryID, CategoryName
Child_Category: ChildID, ChildName
Grandchild_Category: GrandchildID, GrandChildName
Product_Table: ProductID, ProductName, Price, GrandchildID
This is how it works. When a user lists a product, they will first select parent category, then child category and then grandchild category for the their product from cascading listboxes. So here is the problem, not all Child_Category has grandchildren.
So the selection may stop at Child_Category listbox. As a result, I won't be able to insert GrandchildID.
What is the best solution to design my database in this case so that whether there is a grandchild or not, I can always know which category this product belongs to? I can store one more column called ChildID in the Product_Table, but is it the right solution?
Nov 27, 2012 09:29 PM|LINK
I would go with only one table Category with structure (CategoryID, CategoryName, ParentCategoryID). Thus, in the product table you will have foreign key to the CategoryID.
When listing the cateogories, you can first show categories without Parent (CategoryParentID is null), then after the category is selected you can show its children categories (where Children.ParentCategoryID = Parent.CategoryID).
Nov 27, 2012 09:30 PM|LINK
CategoryId, ParentCategoryId, CategoryName
The root category will, of course, have a null value in ParentCategoryId. Any child categories will have their parent's id in the ParentCategoryId.
If a product has only a single category, then you just have the CategoryId in the products table. If, however, you want a product in multiple categories then you need a table in between, such as ProductsToCategories, which would have the ProductId, CategoryId
as the structure. That lets you do what is known as a many-to-many relationship.
Nov 27, 2012 09:32 PM|LINK
I am sorry. there is a mistake in my previous post about the tables. This is the correct version:
Child_Category: ChildID, ChildName,CategoryID
Grandchild_Category: GrandchildID, GrandChildName,ChildID
Nov 27, 2012 09:41 PM|LINK
Hello markfitzme and Aleksandar, could you please list the columns and their relationship in each tables?
As you see I have three tables for category (One parent can have many children, one child can have many grandchildren), so if the product has grandchildID, I can always find the child and the parent. But the problem is grandchild may or may not exist.
Nov 27, 2012 09:44 PM|LINK
You will have just one Category table with this columns: CategoryID, CategoryName and ParentID.
ParentID will refer to the CategoryID of the parent category (from the same table).
In the ProductTable you will have CategoryID which will be foreign key to the CategoryID primary key in the Category table.
Nov 27, 2012 09:49 PM|LINK
I am sorry. I still can't understand how this work. So how many tables are there? From the suggestion, I can see there are three:
Parent_Category (PK: ParentID), CategoryTable(PK: CategoryID, FK: ParentID), ProductTable(FK:CategoryID), is it right?
If yes, my case is a little bit different. I have four tables:
Parent_Category (PK: ParentID), Child_Category(PK:ChildID, FK:ParentID), Grandchild_Category(PK:GrandchildID, FK:ChildID), ProductTable(FK: GrandchildID)
The problem is GrandchildID may or may not exist depends on the which child it is. Appreciate it if you can list all the tables and their relationship.
Nov 27, 2012 09:54 PM|LINK
No, there are only 2 tables :)
ParentID column from the Category table referes to a record in the same table that has CategoryID specified by the ParentID.
CategoryID | CategoryName | ParentID
1 | Categ1 | null
2 | Categ2 | null
3 | Categ3 | 1
4 | Categ4 | 3
In this table you have 4 categories. Category 1 is parent of category 3, and category 3 is parent of category 4.
Thus, you have these category trees:
Categ1 -> Categ3 -> Categ 4
Nov 27, 2012 10:15 PM|LINK
Hello Aleksandar, thanks for the replies. I feel that we are talking about two differnt things. Perhaps my previous explanation is not clear. So let me make this into real example. A user lists an iphone 5. so here are the categories he will select:
Mobilephones (Parent_Category) > iPhone (Child_Category) > iPhone 5 (Grandchild_Category)
And the same user lists a BlackBerry phone, so here are the categories he will select
Mobilephones > BlackBerry
Because BlackBerry doesn't have any grandchild like iPhone (Just an example), so what am I supposed to do in this situation?
Thanks for your patience.
Nov 27, 2012 11:44 PM|LINK
Maybe you can use like this
Mobilephones > BlackBerry > BlackBerry