Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

You'll regret using natural keys

blog.ploeh.dk

Post reply on HN
Print view View original

501–510 of 568 posts

Re: You'll regret using natural keys

#501

Post by a57721 »

zelphirkalt wrote: post #132
maweki wrote: post #125

Earlier quoted context omitted.

> I challenge you to come up with a single plausible example So I come from academia, but generally if you use a natural key as PK in a foreign key constraint it may be possible to express additional consistency criteria as CHECK-constraints in the referencing table. So this is a bad example, but say you have Name and Birthdate as your PK, and you have a second table where you have certain special offers sold to your…

Why does anything need to be a primary key anywhere in order to enforce some constraint? At least from ORMs I know I can set for example any group of attributes unique. Other constraints can be implemented in some general method that is called when persisting in the actual database. Even if no ORM, you can write a wrapper around your persisting procedure.

> At least from ORMs I know I can set for example any group of attributes unique

Just in case, ORMs send DDL statements with uniqueness constraints to the DBMS, they don't do any magic here.

Re: You'll regret using natural keys

#502

Post by zamadatix »

mths wrote: post #23
ray_v wrote: post #9

Feels like this could have used a few more solid examples up-front. I think another good example would be PlayStation Network using the natural key of "gamer tags" as the primary key to identify players would be a good example. Since this effectively locks players into having to keep a gamertag in order to uniquely identify them in the service -- instead of having a synthetic key that carries no meaning or data other…

Last I checked, Steam still has me logging in with my two decade old hotmail address as my account name. At least it's not something that shows publicly, I think.

I'm able to log out and back into Steam using my plain username rather than email. The box actually says"enter account name" and wouldn't log me in if I tried to enter my email instead. I'm not sure if there are conditions to this e.g. my account was only created in 2009 and I'm not sure if I've ever toggled anything that "flipped it over" along the way either.

On the API side I know Steam has something like 3 different forms of auto generated alphanumerical account ID and I imagine that's what everything is really keyed off of on the back end.

Re: You'll regret using natural keys

#504

Post by hot_gril »

cogman10 wrote: post #298
strnisa wrote: post #151

I've become a fan of unique, relatively short and "human-readable" IDs, such at the ones used by Stripe, e.g. `cus_MJA953cFzEuO1z` for an ID of a customer. Here's a Stripe dev article on the topic: https://dev.to/stripe/designing-apis-for-humans-object-ids-3... If you use JavaScript/TypeScript, you can make them like this: function makeSlug(length: number): string { const validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcde…

A problem with this approach is it's not monotonical. Especially if you want to use this thing as an index in a database, you'll run into problems where you try doing middle insertions frequently, which causes fragmentation. The solution to this problem is making the higher order characters time sorted [1]. You don't need to go all out like uuid, you can have a pretty low resolution. It's more important that new inse…

Depends heavily on what kind of database/index. Going with anything other than a random uuid4 adds complexity; for one, do you want to expose time info? I'd rather default to uuid4 as the client-exposed ID* and only change if there's a solid measured reason to.

* not the same as your internal DB row primary keys, which in Postgres should usually be bigserial

Re: You'll regret using natural keys

#505

Post by samatman »

psychoslave wrote: post #401
strnisa wrote: post #151

I've become a fan of unique, relatively short and "human-readable" IDs, such at the ones used by Stripe, e.g. `cus_MJA953cFzEuO1z` for an ID of a customer. Here's a Stripe dev article on the topic: https://dev.to/stripe/designing-apis-for-humans-object-ids-3... If you use JavaScript/TypeScript, you can make them like this: function makeSlug(length: number): string { const validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcde…

To my mind, it always felt so saddening that adoption of a truly straightforwardly readable notation for numbers never took of. I mean it’s so easy to do. You can start for example with a single syllable per digit, and for example only target CV syllables. From this there is many possibilities, but for example, let’s consider only a base ten. Starting with vowels order o, i, e, a, u with mnemonic o, i graphically clo…

This is how the Urbit address scheme works, btw: https://urbit.org/blog/the-urbit-address-space

Re: You'll regret using natural keys

#506

Post by bjourne »

thatjoeoverthr wrote: post #393

One may notice this has something to do with mutability. If there isn’t a surrogate key, the record isn’t mutable. The database may let you change it, but the new record has a new identity. Mutability as a concept requires a common identity across time. Languages permitting mutability are using a pointer or reference as an implicit surrogate identity. A typical database can’t offer this, hence the need to put explici…

While that is true, for correctness appends should supplant updates. On HN, many users (like myself) have posted comments for over a decade. Suppose I changed my username from "bjourne" to "SpongeBob"... Should the comments I wrote in 2010 show up as having been authored by "SpongeBob" or "bjourne"? I'd strongly argue in favor of the latter since the former would constitute falsifying history. The "change" in username should be viewed as the creation of a new personae rather than a "change".

Re: You'll regret using natural keys

#507

Post by cogman10 »

hot_gril wrote: post #504
cogman10 wrote: post #298

Earlier quoted context omitted.

A problem with this approach is it's not monotonical. Especially if you want to use this thing as an index in a database, you'll run into problems where you try doing middle insertions frequently, which causes fragmentation. The solution to this problem is making the higher order characters time sorted [1]. You don't need to go all out like uuid, you can have a pretty low resolution. It's more important that new inse…

Depends heavily on what kind of database/index. Going with anything other than a random uuid4 adds complexity; for one, do you want to expose time info? I'd rather default to uuid4 as the client-exposed ID* and only change if there's a solid measured reason to. * not the same as your internal DB row primary keys, which in Postgres should usually be bigserial

Yes but also no.

Yes, in that there are DB technologies not built in a fashion where records are stored in a sorted order of some fashion. No, in that they are very much not common technologies. Most databases, relational, non-relational, etc have some form of a B-Tree at their core somewhere.

Re: You'll regret using natural keys

#508

Post by hot_gril »

cogman10 wrote: post #507
hot_gril wrote: post #504

Earlier quoted context omitted.

Depends heavily on what kind of database/index. Going with anything other than a random uuid4 adds complexity; for one, do you want to expose time info? I'd rather default to uuid4 as the client-exposed ID* and only change if there's a solid measured reason to. * not the same as your internal DB row primary keys, which in Postgres should usually be bigserial

Yes but also no. Yes, in that there are DB technologies not built in a fashion where records are stored in a sorted order of some fashion. No, in that they are very much not common technologies. Most databases, relational, non-relational, etc have some form of a B-Tree at their core somewhere.

I can see this. Spanner is an example where you don't want this, idk if that's considered common enough. Postgres and MySQL both support hash indexes that are unordered, but the default in both is btree, and Postgres hash indexes used to have some caveats that made them unsuitable (idk about now) so I've gotten in the habit of just using the default.

MySQL docs claim that a hash index is much faster if you only need kv lookups, so it seems like uuid4 with hash index would be suitable. Never tried it though, and can't say whether it's faster than using a btree with uuid7. Seems like in theory it would be.

Re: You'll regret using natural keys

#509

Post by indoordin0saur »

jklowden wrote: post #27

You think your surrogate key will save you? It will not. The world has an external reality that needs to be reflected in your database. If the unique identifier for your object — VIN, CUSIP, whatever — if it changes, the world will henceforth refer to it by both. You will need to track both. Adding a synthetic key only means you have to track all three. Plus you have to generate a meaningless number, which is actuall…

I agree with both the original author's thoughts and yours. Surrogate keys are generally necessary but also overrated. Natural keys will always remain important. I think it's important to keep natural keys as reliable as possible whenever you control the system. Creating a new surrogate key every time the data moves creates it's own headache as you have to keep track of more and more keys referring to the same concept.

Re: You'll regret using natural keys

#510

Post by crabmusket »

mypalmike wrote: post #474
crabmusket wrote: post #114

Earlier quoted context omitted.

Aren't you literally describing an index?

No. Maybe an example helps. You have a users table with username as natural PK. You have an access table with timestamps of users hitting a service, with FK of username. If you query the access table for a range of timestamps and just want the usernames, they're right there in the results of the access table query. If you had instead used a synthetic user_id key, the db would have to do an additional lookup in the us…

Oh of course, that makes sense. I'm not sure how I misunderstood that!
Post reply on HN