My Case For Using Both UNIQUEIDENTIFIER (Guid) -and- INT Ids in every database table

Case for dual database accessors (not keys) - Guid vs Int/UniqueIdentifier vs Int

Let's face it, you never know what exactly is going to end up with your next application. Has an application ever ended up exactly where it was planned in the beginning? I would say never.

There is an insane amount of writing on the debate of int vs guid, both have their pros and cons, both have storage, index and coding implications and we know (or can find) almost all of them with a simple search. Yet there is no consensus on which is the best, I am not here to add to that discussion.

Therefore I propose using BOTH - hear me out.

I propose that the best solution is to provide ONE of the two as your primary key, I prefer Guid as the PK, as it can be referenced more universally (think recycle bin, etc.), but it does have the added indexing cost of being larger; then simply add the auto-incrementing integer as an additional field. Whichever one you choose as your alternative (integer in my case) is simply as a just-in-case means of accessing your data - also indexed. This provides you the best of both worlds, without too much sacrifice. For example, you get the ubiquity of being able to generate a Guid anywhere (without a DB trip), but also have the ability to access your data by a clean 4-bit integer. If for some reason, the code is accessed by software that relies on integers, you are not stuck without one, and vice-versa.

Yes, this will obviously cause more data to be stored for every entry in a table (I am talking about main tables here, not necessarily lookup tables, etc.). This will cause extra index storage as well, but in the long run, I feel it provides enough advantage and future-proofing to justify the sacrifice. Now tell me how wrong I am...
Comments are closed