UID

[Garry Flanigan]

I am using MS SQL backend on an app and in the main customer table am using CustomerID with "Default Value or Binding" set to newid(), which generates a unique identifier (UUID) that is 36 characters long. This is because it is intended that customers will be able to logon to see some details of their account and download reports pertaining only to their own account.
I am wondering how far one should go in using UUID's as the primary key of tables. For example, if there is a 1-M CustomerNotes table, should it's primary key be a UUID? What is best practice in this case? Can someone point me to a document?

[Steve Wood]

Re: UID

From what I read on the SQL side, long UID's will be slower to process, but it would be hard to say if that would be noticeable in your application. In the case you describe, typically the primary key is a simple auto-increment value starting at 1 or some other seed value.

For many years I used a 32 character UUID as the primary key for my user list and cannot say I have had any problem at all. I wanted a UUID for each record because I often referred to the record in an email such as mydomain.com/mypage.a5w?id=232LKJLJ33NLNN3OL2. I did not want my URL to look like this, mydomain.com/mypage.a5w?id=123 where the ID was a simple auto-increment value, because a curious user would "test out" other values like "124".

But lately I have stopped using UUID's for primary key, using auto-increment values instead*. If I need to refer to the record in a URL as described above, I add a field named "eid" as the "encrypted ID', which is a UUID. So in that case the URL might be mydomain.com/mypage.a5w?eid=232LKJLJ33NLNN3OL2.

There are tons of articles on this subject if you Google "UUID or autoincrement", or similar.

[Garry Flanigan]

Re: UID

Steve, your suggestion regarding eid is a very good option.
In my development environment I have used auto-increment fields but then started considering the mydomain.com/mypage.a5w?id=123 problem as mentioned in your post and thought that I should change to UUID primary keys. However I did not want to change everything everwhere and hence my post.
The "UUID or autoincrement" subject does depend very much on your particular circumstances (intranet only versus multi-tenant or web versus desktop) so I will be interested to read other opinions.

[Steve Wood]

Re: UID

The added benefit of having an "eid" separate from the primary key is you can change the eid value and instantly cut off access by any previous URL given to anyone in the past. In one application, I alter all of them at the start of the year and provide a "not found" message if an old one is used.

[Mkpatil]

Re: UID

In such situation I may consider using sequence, but then I would use a hash function on top of it.
Only hash value needs to be saved. Depending on whether you use database sequence or increment sequence programatically - you may not be required to save the original sequence.
select ORA_HASH('123') from dual; -- in oracle
2560241216
If the hash is too long - truncate it and then consider appending the original sequence somewhere in that or at the end.
Thanks.

[Garry Flanigan]

Re: UID

Steve, thanks for your suggestions.

I think I will stick with autoincrement.