Re: Relational Database Design Question - Category Tree

Hi Jim:
Your tree concept is correct. You will use the following and others can fill in details since I'm weak in this area myself. 
First you will need to use:
Create Database Statement, as you likely know.
Then you will use a:
Create Table statement for each table in the tree concept.
The Parent Table will not need anything special but the Child Tables will need a field 
that  matches to a field in the parent Table.
This is called a Foreign Key. You can set up your code then to make sure their is a parent for any child you attempt to insert. Also, you can specify an attribute, or parameter I get terms mixed up, that will allow what is called Cascading to occur. So, if you delete or change a parent table all child tables will be deleted or changed as required. I have not done this but just have the overview knowledge from doing other research in the area. 
Hope this helps a little.
Rick Farmington Mich. USA
----- Original Message ----- From: <james.homme@xxxxxxxxxxxx> 
To: <programmingblind@xxxxxxxxxxxxx>
Sent: Wednesday, September 12, 2007 7:53 AM
Subject: RE: Relational Database Design Question - Category Tree



Hi Rodney,
The reality is that I don't know what I am doing. I don't know how to force a category to have one parent. I don't know how to allow a category to have 
multiple parents.

Thanks.

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."




            "Rodney Haynie"
            <RodneyH@xxxxxxxx
            om>                                                        To
            Sent by:                  "programmingblind@xxxxxxxxxxxxx"
            programmingblind-         <programmingblind@xxxxxxxxxxxxx>
            bounce@freelists.                                          cc
            org
                                                                  Subject
                                      RE: Relational Database Design
            09/12/2007 07:24          Question - Category Tree
            AM


            Please respond to
            programmingblind@
              freelists.org






4 tables... Less duplicate data... That is true if you allow a category to
have more than one parent.

The key is to implement a scheme that you are comfortable with.
-Rodney


-----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
http://www.freelists.org/list/programmingblind

__________
View the list's information and change your settings at
http://www.freelists.org/list/programmingblind



__________
View the list's information and change your settings at
http://www.freelists.org/list/programmingblind 

__________
View the list's information and change your settings at http://www.freelists.org/list/programmingblind

Other related posts: