Yeah, I'd recommend that your tables always have an independent primary 'id' key that has no significance or ties to your business logic (other than to uniquely identify a row in your table). The Ruby on Rails guys really promote this as well and use the universal convention of the first field in a table being 'id' and it being the primary key. I do the same and usually use the BIGINT type (128-bit int, I think) to ensure a practically unlimited number of rows regardless of how many transactions I have (now or in the future).
I googled around a bit a month ago looking for GUID solutions for Flex. If I had seen the UUID stuff in the framework I probably would have used that. I found an alternative for generating unique id's... they're not standard GUID's, but they may be more unique (I just need a unique string key). The basic algorithm is to take a random number, the current time, a user salt (user name or other per-user detail that your system guarantees unique), and the capabilities string the Flash Player provides (it's a long query vars string describing screen resolution, OS, etc.). Concatenate all of that together then generate an SHA1 hash of it (use can use ascorelib for that). I took it one step further and then converted it to a base-36 representation to make it more compact (0-9, a-z), but you could go even further and base64 encode it -- I didn't do this because I wanted the id's to still be human friendly for debugging, which meant I didn't want case-sensitivity. Troy. On 06 Mar 2007 06:14:41 -0800, Paul DeCoursey <[EMAIL PROTECTED]> wrote:
--- In [email protected] <flexcoders%40yahoogroups.com>, "scott_flex" <[EMAIL PROTECTED]> wrote: > > Shaun, > > Thanks for the reply, good to hear some strong objections. > > First, i'm 99.9% sure this is the only app that will be generating > ids for this database, but i do agree with that point. I have full > control over all apps that would ever generate PKs for my db, i can > make sure they won't conflict. > > I guess the point of a GUID is that I won't have collisions, or the > possibility is so small, i'll take my chances, i'm not talking about > millions of transactions. > > I did think about creating a temp id and the translating when it > comes back down... but i'm trying to avoid having to send someting > back down after i post and save my xml to the database. > > Another thought is that when my app loads, i'll just download X > number of new ids that are available so i know (since the server gave > them to me) they are unused, no collisions. > This is exactly what I do. I have a pool of ids that get loaded when the client logs into the app. Then as I use up id's a get more when the pool get's too small. Our app can consume more than a dozen of id's at one time so our pool is quite large, but it has yet to fail for me. I was going to suggest this after reading that you were using these as Primary Keys. I should note that we don't use our guids/uuids as Primary keys, they are secondary keys. Our database maintains it's own primary key. I forget what it's called but it's one of those database design patterns that good database admins are supposed to use. Paul > I would say the stragety i'm trying to accomplish is not great for > large teams, but in this case, i'm one developer, one database. If > this product takes off and makes a ton of money it can always be > redesiged later... for now, i'm trying to cut a layer out of all the > server side to client ui mapping for sake of development time. > > --Scott > >
