RE: Relational Database Design Question - Category Tree
- From: "Humberto Rodriguez" <sub@xxxxxxxxxxxxxxx>
- To: <programmingblind@xxxxxxxxxxxxx>
- Date: Wed, 12 Sep 2007 10:23:40 -0400
Hello Jim:
Lets say you have a table category that has:
cat_id, subof, level, description...
also, an item table that has:
item_id, name, unit_of_package, price, quantity_in_stock...
and a third table:
id, cat_id, item_id
You would have duplicates only in this third table, which holds only
three id
When at top level, the cat_id = subof
HTH,
Humberto
-----Original Message-----
From: programmingblind-bounce@xxxxxxxxxxxxx
[mailto:programmingblind-bounce@xxxxxxxxxxxxx] On Behalf Of
james.homme@xxxxxxxxxxxx
Sent: Wednesday, September 12, 2007 7:04 AM
To: programmingblind@xxxxxxxxxxxxx
Subject: Re: Relational Database Design Question - Category Tree
Hi,
I think I should use four tables because there would be less duplicate
data. Here is the summary of this thread as it was on the old list.
Me:
I want to depict a tree in a relational database. Here is what is going
to happen. There will be a table that has items in it. The items can
fall
into
categories. That means there will be a table of categories. An item can
be in more than one category. I think this means that I need a third
table.
The third table will hold the number of an item from the items table and
a number of a category from the categories table.
I think I need another table that has
a category number and a parent number. This would let me depict the
categories inside other categories.
Rodney:
I would say you only need 3 tables:
1. Item
2. Category
3. ItemCategory
I don't think you need another table because you can simply put the
ParentCategoryID into the category table. You would join the category
table to itself to get the parent category.
Humberto:
I think that you can handle the category and subcategory like eBay does.
There is a field category_id and another field subcategory_of in the
categories table. When the subcategory_of field is the same as the
category_id field, you are at a top level category.
Jim
James D Homme,
Usability Engineering
Highmark Inc.
james.homme@xxxxxxxxxxxx
412-544-1810
"It's more important for me to start to do the right thing than it is to
wait until I think I
can do it just right."
__________
View the list's information and change your settings at
//www.freelists.org/list/programmingblind
__________
View the list's information and change your settings at
//www.freelists.org/list/programmingblind
Other related posts:
