The primary reason is likely that storing lists in varchar fields in db tables tends to cause all sorts of problems... And in this case, in particular, the lack of any list delimiter could cause unexpected problems as in
SELECT * FROM mytable WHERE treepath like '%00101%' could result in drawing records from the table where record 00101 is actually not a parent of the given record, but items 00010 and item 01010 or 01011 are ... It's also much less efficient ( and a lot slower ) for the db to parse these potentially huge varchar columns as it is to use a cross-reference table which uses indexed integers. In retrospect I think I made a couple errors in the example of the trigger I mentioned in my previous post ... I was after all drawing from memory. Isaac www.turnkey.to 954-776-0046 > Ken, > I agree, this is a great way to handle categories. I used > this method a > long time ago on a app I created with CF2 and it worked > really well. I've > always been surprised that this method doesn't get used > more. > Do you know of anywhere that has a standard set of > queries/stored procedures > for implementing this (adding/deleting/inserting > categories). I know it's > not all that difficult to do, having gone through it once > before, but it > would be helpful. Also, do you know if there is a formal > name for this > categorization method? > Nelson > ----- Original Message ----- > From: "Ken Beard" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Tuesday, July 16, 2002 3:06 PM > Subject: RE: Recursive queries >> the easier way is to have a table structure like this >> >> category_id, categoryName, treeID >> >> where category_id is your integer pk, name is obvious, >> treeID is >> 00001 >> 0000100001 >> 0000100002 >> 00002 >> 0000200001 >> >> >> etc like that. >> >> just order by treeID and you get everything in one >> query.. get a section >> like >> >> treeid LIKE '00001%' >> >> ken >> >> >> >> -----Original Message----- >> From: Justin Greene >> [mailto:[EMAIL PROTECTED]] >> Sent: Tuesday, July 16, 2002 2:10 PM >> To: CF-Talk >> Subject: RE: Recursive queries >> >> >> There is an example in the SQL Server book on-line on how >> to do this. I > do >> not remember where, but it does not use recursion. >> Recursion is very cool >> (I happen to love it) but is not terribly efficient, >> probably why LISP > never >> caught on. >> >> Justin >> >> > -----Original Message----- >> > From: chris.alvarado [mailto:[EMAIL PROTECTED]] >> > Sent: Tuesday, July 16, 2002 11:37 AM >> > To: CF-Talk >> > Subject: Recursive queries >> > >> > Still trying to decide how I like to perform some type >> > of recursive >> > query wth the following Table structure; >> > >> > ID NAME PARENT_ID >> > >> > If the current record IS the parent then the Parent ID >> > is 0 >> > >> > Otherwise the child has the ID of the parent as it's >> > Parent ID. >> > >> > >> > Anyone have any good references on recursive queries to >> > handle this? A >> > way to spider down through the parents and children >> > basically. >> > >> > Ive done this before using Stored Procs and temp >> > tables, but there has >> > to be an easier way. >> > >> > Any suggestions? >> > >> > >> > -chris.alvarado >> > [ application developer ] >> > 4 Guys Interactive, Inc. >> > http://www.4guys.com >> > >> > -- I can picture in my mind a world without war, a >> > world without hate. >> > And I can picture us attacking that world, because >> > they'd never expect >> > it. -- Jack Handy >> > >> > >> > >> >> > __________________________________________________________ > ____________ > Get the mailserver that powers this list at > http://www.coolfusion.com > FAQ: http://www.thenetprofits.co.uk/coldfusion/faq > Archives: > http://www.mail-archive.com/[email protected]/ > Unsubscribe: > http://www.houseoffusion.com/index.cfm?sidebar=lists ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
