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?
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).
Regards,
Aleksandar
Regards,
Aleksandar
Don't forget to Mark As Answer the post that solved your problem.
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.
Don't forget to mark useful responses as Answer if they helped you towards a solution.
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.
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.
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:
asplearning
Participant
909 Points
951 Posts
How to link Primary Key in this scenario?
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?
Thanks.
alpetrovski
Member
44 Points
7 Posts
Re: How to link Primary Key in this scenario?
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).
Regards,
Aleksandar
Aleksandar
Don't forget to Mark As Answer the post that solved your problem.
markfitzme
Star
14319 Points
2215 Posts
Re: How to link Primary Key in this scenario?
Nov 27, 2012 09:30 PM|LINK
Try this:
Category table:
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.
asplearning
Participant
909 Points
951 Posts
Re: How to link Primary Key in this scenario?
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:
Parent_Category: CategoryID, CategoryName
Child_Category: ChildID, ChildName,CategoryID
Grandchild_Category: GrandchildID, GrandChildName,ChildID
Regards;
asplearning
Participant
909 Points
951 Posts
Re: How to link Primary Key in this scenario?
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.
Thanks.
alpetrovski
Member
44 Points
7 Posts
Re: How to link Primary Key in this scenario?
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.
Aleksandar
Don't forget to Mark As Answer the post that solved your problem.
asplearning
Participant
909 Points
951 Posts
Re: How to link Primary Key in this scenario?
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.
Regards;
alpetrovski
Member
44 Points
7 Posts
Re: How to link Primary Key in this scenario?
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.
Ex.
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
Categ2
Aleksandar
Don't forget to Mark As Answer the post that solved your problem.
asplearning
Participant
909 Points
951 Posts
Re: How to link Primary Key in this scenario?
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.
oned_gk
All-Star
30969 Points
6338 Posts
Re: How to link Primary Key in this scenario?
Nov 27, 2012 11:44 PM|LINK
Maybe you can use like this
Mobilephones > BlackBerry > BlackBerry