Hmmmm I was not too serious - hence my use of the word "cheat". You did understand my suggestion, but wanted to make it "better", by using VarChar. I still fail to see how using varchar would make it better. Having developed a number of databases in my time I am well aware of the improperness of storing lists of id's.
The customer wanted to: [store an arbitrary length hierarchy in one table] and [retrieve the path to the root parent] [in one query]. I believe my solution "works" based on these requirements/deliverables. Heck, I'm going to suggest storing both the ID and the Category name for display purposes :p Didn't say it was elegant, valid, correct or the right shade of blue. No mention was made by the customer of modifying the hierarchy nodes or anything. I can come up with solutions for that too, but it'll cost them extra :) The customer wanted a solution and that's all I was offering. And it's definitely cheating. :-) I've seen many "properly designed database" projects fail to complete and many "home grown" syntactically incorrect nightmare RDBMS (MS Access!) designs survive for years in both large and small organisations. Aaron ----- Original Message ----- From: "Micha Schopman" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Friday, December 24, 2004 12:18 AM Subject: RE: In One? > I thought you were making a joke.. :) One of the principles of a good > database design is not to use lists of id's. You could better if you > really want to do such thing, create a seperate table to store the path. > The disadvantage of using lists is, you cannot use constraints to force > existing relationships and you cannot index the data in a optimal form. > > You'd be better off with two tables like: > > Table 1: Path > ID = Identity > ObjectID = Int (object for which the path is stored, so this could also > be ArticleID for example) > > Table 2: PathData > ID = Identity > PathID = Int (Contains ID from the Path table) > Priority = TinyInt (Contains an order) > Target = Int (contains the result of a path, can be ID, anything you > want to) > > So with this data > > Path table: > ID = 1 > ObjectID = 138475384047 > > PathData > ID = 1 > PathID = 1 > Priority = 1 > Target = 3567242 > > ID = 1 > PathID = 1 > Priority = 2 > Target = 3453456 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188630 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
