[cfaussie] Re: UUID vs Integer with Primary keys..

[Scott Barnes]

Kewl thanks for your input, i guessed the integer would of been the smarter pick, as for indexing purposes alone it would have to have been faster. I just noted in some higher-end applications i have seen that they have used UUID which left me thinking maybe they aren't as bad as i thought.   But..   I was however thinking of maybe another solution?   What if i assign the ID as:   #DateFormat(Now(),'yyyymmdd')# + #TimeFormat(Now(),'hhmmss')# + #RandRange(1,999)#   Would Would Give me: 20030122947339999   I guess its still a very large integer, but i don't anticipate the database or the tables in question having approx 1-999 people inserting information into the database on that split second, so in a way its very unique?   I Guess what i am trying to do is basically make sure that in any given table the same ID doesn't exist throughout the db, so they are all individual rows   (I have thought of just using a table, as a "pool" and just keep incrementing as i go, which is still an ok solution i guess; just requires one more query before the insertion)   Scott.           "Scott Barnes" <[EMAIL PROTECTED]> wrote in message news:22131@cfaussie... 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: archive@mail-archive.com To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/