Scott, I'm not sure why you would want to spread your data across 3 tables.
I always use the following db structure for a Tree Hierachy. pkWhateverID [integer pk identity] / fkParentID [integer] / textColumn [varchar(X)] I use 3 very small and simple stored procedures that create a temp table and create the sorted output I need to display. I'd be happy to send you a copy of these 3 stored procedures if it would be of any help to you? The disadvantage I see is that with the structure you got now you can not go down the Tree hierachy further than the sibling. With the above structure you can go down the hierachy as far as you need to. I would say the negative effect of using UUID would be: - It takes longer to create than an incrementing integer - I believe there is still a 0.01 % change that you will run into a duplicate - It takes more space to store a UUID instead of an integer Hey, maybe I am way of here and don't exactly understand what needs to be done... In anycase if I am on the right track let me know if I can help.. Taco Fleur PS. sounds like you got the perfect job being able to work on thsoe sites. > Sorry to ask probably a question thats had a debate before, but.. i will so shutup >and read. :D > > Whats the negative sideeffects of using UUID instead of Integer (autonumber) for >primary keys in a table. The reason i bring this up, as I always use a >parent-child-sibling-infant type relationship amongst various tables, one example off >the top of my head was a awhile back where i had 3 tables that basically provide: > > - Category (parent table) > - Category (parent table) > ---- File (child table) > ---- File (child table) > ------- file versions (sibling table) > ------- file versions (sibling table) > ------- file versions (sibling table) > ---- File (child table) > ---- File (child table) > ------- file versions (sibling table) > ------- file versions (sibling table) > ------- file versions (sibling table) > - Category (parent table) > ---- File (child table) > ---- File (child table) > ---- File (child table) > ---- File (child table) > - Category (parent table) > > This situation called for the ability to not only associate 3 different tables into >a heircahy based dataset, but it also called for i think 3-4 other tables that >provided other pieces of information per level, and in doing this i was forced to use >a big meaty looking SQL SP, that made use of both UNIONs and Views, which worked >great. > > I did find that if i simply relied on the Autonumber system, the heirachy would be >out of sync, as in the joining field for the child had the same value as the parent >pk, but having said that, the child pks also had the same value and so a child brach >would mistakenly associate its children under another child? > > ie: > parentID = 2 > > childID = 1 > childParent = 2 > > childID = 2 > childParent = 2 > > childID = 3 > childParent = 2 > > even though the childParent = 2, i did find that child referred to childID = 2 as >its parent? > > I did manage to find away around this so i didn't have to basically redesign the >entire db, but i'm about to embark on a bigger project (probably the biggest i have >undertaken yet) and i so want this to be a finely tuned effecient running machine, >and having remembering this scenario, i felt a bit nervous with simply utilising an >"autonumbered" system or even a seperate table which has a running pool of numbers >that you take from. > > Atleast with UUID i know that no matter what no other rowID through-out the entire >database will have the same value? > > Your thoughts would be appreciated. > > Scott Barnes > eCommerce > Tourism Queensland / Sunlover Holidays > [EMAIL PROTECTED] > > www.queenslandtravel.com > www.sunloverholidays.com --- You are currently subscribed to cfaussie as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
