That's right. The SecretAgents method is the best way. Development is
quite a bit more difficult, but would be worth it in the functionality and
speed you may require later.
I still use the more simple method with a single table because the
three-level site is pretty standard and, while this allows expansion beyond
that, it most of what I need can be done pretty well. The technique that
can be found on SecretAgents is great, but takes a few extra steps.
Good Luck.
- BILL -
----- Original Message -----
From: "Norman Elton" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, June 11, 2001 8:01 AM
Subject: RE: Database Design Help
> 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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
