Hmm.. here is what I have: department table id | title | description ..... (all departments go into here ..Fishing, Camping, Rods, Spinning ect..)
And the table below from the original message Why would I use the sub object table .. So I would stick EVERY department id in the OWNER table and EVERY department id in the SUB_OBJECT table.. and then combine their ID's in the RELATIONSHIP TABLE? Isn't it already doing something similar by sticking the ID value from the DEPARMENTS table in the relationship table? I guess my question then would be why the owner and sub-object tables? It seems a bit redundant? Paul Giesenhagen QuillDesign http://www.quilldesign.com SiteDirector v2.0 - Commerce Builder > you should have 3 tables > an owner table (owner and ownerID) > a sub object table (subobject and subobjectID) > a relationship table (ownwerID and subobjectID are complex primary key ) > > > > On Mon, 15 Apr 2002, Paul Giesenhagen wrote: > > > I have to ask this one again .. I asked this question late Friday and didn't get any answers that actually fit my situation. I will try to have this question make sense. > > > > I am trying to build an OUTPUT that would look like this: > > > > Fishing > > Fishing/Rods/Baitcast > > Fishing/Rods/Baitcast/Special Bait Cast Rods/... > > Fishing/Rods/Baitcast/Not so Special Rods/... > > Fishing/Rods/Spinning > > Fishing/Reels/BaitCast > > Fishing/Reels/Spinning > > Fishing/Line > > Camping > > Camping/Tents > > Camping/Stoves/Big Stoves > > Camping/Stoves/Little Stoves > > Camping/Sleeping Bags > > Marine/ ect... > > > > Ok as you can see, we have 3 main departments (actually more, but for example we will go with whats above), and multiple departments (unlimited depth) that are below each department. > > > > My table structure is this: > > ID = Identifying id > > OWNER = Parent > > SUB_OBJECT = Child > > (Example: Owner=FISHING and SUB_OBJECT=RODS ..then OWNER=RODS and SUB_OBJECT=BAITCAST) > > > > As you can see, parents can be children and children can become parents. I am inserting NAMES instead of ID values that are in the actual table (makes it more readable for this message). Each Owner/Sub_object is an ID Value that relates to another table called departments. As you can see, sub_objects end up becoming OWNERs with sub_objects below them. So parents can be children and children can become parents. > > > > Table Setup > > > > ID | OWNER | SUB_OBJECT > > 1 Index Fishing > > 2 Index Camping > > 3 Index Marine > > 4 Fishing Rods > > 5 Fishing Reels > > 6 Fishing Line > > 7 Rods Baitcast > > 8 Baitcast Special Baitcast Rods > > 9 Biatcast Not so Special Rods > > 10 Rods Spinning > > 11 Reels Baitcast > > 12 Reels Spinning > > 13 Camping Tents > > 14 Camping Stoves > > 15 Camping Sleeping Bags > > 16 Stoves Big Stoves > > 17 Stoves Little Stoves > > 18........ > > > > I know the INDEX value and each of my Index Sub_objects are my main catagories, and want wind down through all the departments and build a recursive list that starts with main catagory and build a "catagory/sub_dept/sub_dept/sub_dept ect... > > > > I know this is alittle loong and I appologize .. but I am dumbfounded and keep "looping" myself into nowhere, trying to get the output as it is above. > > > > Anyhelp with the SQL and CF Syntax is greatly appreciated! > > > > At this point, I am not looking for performance :) just a solution! > > > > Comon' I know that there is a supermind that can help me!! > > > > Thank you > > Paul Giesenhagen > > QuillDesign > > http://www.quilldesign.com > > SiteDirector v2.0 - Commerce Builder > > > > > > > > > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
