I am trying to design the DB schema of my new project and I need some suggestions. I am thinking to have one extra column for some tables which is a UUID. I wanted mainly for tables which contain data which are accessible through REST, so I don't want to expose the primary key.
My concern is that when I want to update a table, I need to execute one more query just to take the original primary key.
What do you think?
Best Answer
The biggest myth when designing applications is that you are only allowed to have one key.
Have multiple keys, go ahead and do it. Your application is allowed to have a different primary key than your database; I know this may sound strange at first, but you get the best of both worlds going this route.
UUIDs have many advantages, but make generally horrible clustered indexes. So, you can just use a int as a PK/clustered index in your DB, but also have another unique index on your external id column. This will may require an extra join for many queries, but that's okay, because in relational DBs, inner joins on auto-incremented int columns are blazing fast.
Another advantage to this dual key approach is the ability to easily geographically distribute your DB. Since UUIDs are globally unique, two geographically separated DBs will not have to slow down from coordinating their keys, as long as you make sure the DB int PK never leaves the DB and is not used by the rest service.
Here's another point, your external Id doesn't even have to be a UUID, there can be other options too.
The external can be an int as well, or a string, or the natural key of the entity; anything. This would make your urls less ugly than a UUID
Don't be afraid to have multiple keys, hiding your DB key from your rest api consumers is a good thing.
The most keys I have ever used has been two, but in the right situation, I can imagine up to four being justified (DB key, application key, business key, customer key)