Let’s talk about IDs.. When i learn programming old time ago, usually every time we read or watch tutorial about creating database structure, it’s like every table must have an auto-increment id. But when i was in some project on middle 2015 some people said auto-increment are bad and can make your Data Away. As a replacement they recommend to use UUID instead.
if you didn’t know what UUID is ?
UUID (universally unique identifier) is is a 128-bit number used to identify information in computer systems. The term globally unique identifier (GUID) is also used, typically in software created by Microsoft.
They look a little bit like this:
it’s make me confuse.. So at the time the question always pop in my brain is…
it is true UUID are better than auto-increment ID ?
And finally i found the best answer for this question from Tom Harrison.
He said :
Reasons UUIDs are Good
There are several reasons using a UUID as a PK would be great compared to auto-incrementing integers:
- At scale, when you have multiple databases containing a segment (shard) of your data, for example a set of customers, using a UUID means that one ID is unique across all databases, not just the one you’re in now. This makes moving data across databases safe. Or in my case where all of our database shards are merged onto our Hadoop cluster as one, no key conflicts.
- You can know your PK before insertion, which avoids a round trip DB hit, and simplifies transactional logic in which you need to know the PK before inserting child records using that key as its foreign key (FK)
- UUIDs do not reveal information about your data, so would be safer to use in a URL, for example. If I am customer 12345678, it’s easy to guess that there are customers 12345677 and 1234569, and this makes for an attack vector. (But see below for a better alternative).
Reasons UUIDs May Not be Good
- Performance issue if the UUID stored as string varchar(36)
- Hard to sort random numbers
- It’s very annoying to have deal with values that are too big to remember or verbalize.
And the most i like from he’s article is our main solutions are simillar like what i’m doing now 😀 it make me feel yeay i’m in good way hahha…
Best of Both? Integers Internal, UUIDs External
Internally, let the database manage data relationships with small, efficient, numeric sequential keys, whether
Then add a column populated with a UUID (perhaps as a trigger on insert). Within the scope of the database itself, relationships can be managed using the usual PKs and FKs.
But when a reference to the data needs to be exposed to the outside world, even when “outside” means another internal system, they must rely only on the UUID.
You can read the full article from this link
UUID or GUID as Primary Keys? Be Careful!