You shouldn't ascribe meaning to your surrogate keys beyond the fact that they are a unique identifier for each row, as such it doesn't matter what format it takes from the point of view of your schema design. All you should care about at that point is that it is a value that identifies something, so I stick with calling them ID
⁰.
In fact what you have with a UUID, unless you have had a brain-fart and stored them as strings¹, is effectively a large integer. You can't do arithmetic on it³ but the engine doesn't see it as any different to a bigger BIGINT
being used as a key.
Another general thing is that I try to avoid any name (for a column, table, function, procedure, ...) that could be a keyword so need escaping. This is a point against calling them uuid
if the main point for calling them id
is not enough. Of course this can't be perfect, I would avoid uuid
as I know it is a type name in Postgres but devs who have only ever use SQL Server up to this point might not know that as it isn't a reserved word (or even a type name) there, and I might well use words that are not portable elsewhere similarly.
If you effectively store multiple surrogate keys due to integrating with another systems that use something else, your internal identifier, is id
. The others are real data with meaning so far as your system is concerned, not actually surrogate keys, and should be named in a way that describes their content or use. Real world examples include ISBN, StaffReferenceNumber/SRN, IRN, PPN, …, but you might also have something less general like SalesForceId or JoesPartStoreId. Even if you don't define a surrogate key in these circumstances⁴, keep the meaningful names instead of just id
so it is obvious that your rules probably don't control their generation and use.
[0] or id or Id, keeping consistent with the casing rules you follow elsewhere just in case your stuff ends up being interpreted in a case sensitive way later.
[1] I still maintain a legacy system that made this mistake two decades ago, a little before my time. It all works but of course there are storage size and performance “considerations”, and interesting bugs² lead to invalid UUID values such as empty-string turning up.
[2] I'm also counting past bad colleagues, including that young David Spillett who messed up a few such times many years ago, as bugs in the business!
[3] Well, you could if you tried hard enough, but not with any built-in functions.
[4] I would always have a surrogate key in these cases anyway. This means only your bugs can cause issues like duplicate keys or the need for expensive primary key value changes that affect many foreign keys, instead of being beholden to dealing with other systems' bugs too.