I built a site very similar to this, using the technique Bill explained,
with each record having a "parent" record. If you KNOW for sure that there
will be no more than three levels, then you can get away with three tables:
grandparents, parents, children. This is risky and not the best design.
The "parent record" idea worked very well, but there's an even better method
to build trees (that's the term for these things) located on
www.secretagents.com, as a free sample tutorial for there tutorial services.
It's not simple to pickup, but if anyone is interested in building trees of
infinite depth, it's certainly an interesting technique. It reduces the
number of queries required to find the parent and grandparent of a given
child. Using the parent record theory, you've gotta query twice: once to
find the parent, then again to find the parent's parent. At least that was
the only way I figured out :).
Norman Elton
-----Original Message-----
From: Bill King [mailto:[EMAIL PROTECTED]]
Sent: Monday, June 11, 2001 11:41 AM
To: CF-Talk
Subject: Re: Database Design Help
Cody,
I think you should still design the system so that it can have more than
three levels. Your site structure is very common in sites I have been
working with and most stay within those three levels, but sometimes there
can be a small change that the client wants that messes everything and
requires a fourth.
ANYWAY:
Here is the way we do it: Create a single table called SiteCategories.
Within the table create a column called "parent". The first level
categories will all have "0" as the parent. Each category will have an ID
and the sub categories will use this ID as it's parent. Here are a few
example records:
catID catTitle catDescription Parent
1 support description of support 0
2 services description of services 0
3 online desc. of online support 1
4 telephone desc. of phone support 1
Now when you click on support simply query all records where the parent
record is "1" and you will retrieve all support sub records. Simalarly when
you want to brong up "phone support" all you would need to do id determine
what its' parent is then query all items with the same parent.
You can reply to me directly if I have confused you with this.
- BILL -
----- Original Message -----
From: "Cody" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, June 11, 2001 7:23 AM
Subject: Database Design Help
> Hello. I'm trying to build an application but I'm stuck on designing the
DB. I'll try to explain how the site is going to be made, and hopefully
someone can help.
>
> There are only going to be 3 levels of pages. Category pages, sub pages,
and sub-sub pages. I want to be able to call any page, whether it's a
sub-sub, sub, or category page, just by it's id (like pageid=3).
>
> Then, using only the pageid information I want to be able to get all the
other pages in that category... so, for example:
>
> If pageid 3 was a sub-sub page... when someone goes to that page, I want
to be able to list the main category page it's under, the sub pages under
that category, and then the sub-sub pages under the sub page (which the
current page belongs to). Can anyone help me figure out how I should build
the DB to handle this?
>
> I feel like an idiot because it seems like it would be so simple
(especially since I know the pages will always be only 3 levels deep) but I
just can't figure out how to make the DB. Please help!
>
> Cody
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
