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

11–20 of 568 posts

Re: You'll regret using natural keys

#11

Post by iaabtpbtpnn »

fn-mote wrote: post #8
Spivak wrote: post #7

Natural keys are great when they're someone else's synthetic keys. My Discord bots make liberal use of this. model: DiscordMessage key = discord_message_id key = discord_user_id key = discord_channel_id key = message_type model: UserData key = discord_user_id key = field_name model: GuildFeature key = discord_guild_id key = well_known_feature_name model: DiscordEvent key = discord_event_id key = discord_guild_id This…

I think this is an excellent example of one of the pitfalls that the author is getting at. (Disclaimer: not a Discord bot programmer.) Suppose discord_user_id is an identifier like @Spivak#2024 that uniquely identifies the user. When Discord forces all users to change to unique ID's and eliminates the disambiguating numbers (or allows users to change them), then where does that leave you? (For the unfamiliar: somethi…

It probably leaves you in the same place you’d be with a synthetic key of your own: unable to track users across this key change without additional Discord data.

Re: You'll regret using natural keys

#12

Post by [deleted] »

fn-mote wrote: post #8
Spivak wrote: post #7

Natural keys are great when they're someone else's synthetic keys. My Discord bots make liberal use of this. model: DiscordMessage key = discord_message_id key = discord_user_id key = discord_channel_id key = message_type model: UserData key = discord_user_id key = field_name model: GuildFeature key = discord_guild_id key = well_known_feature_name model: DiscordEvent key = discord_event_id key = discord_guild_id This…

I think this is an excellent example of one of the pitfalls that the author is getting at. (Disclaimer: not a Discord bot programmer.) Suppose discord_user_id is an identifier like @Spivak#2024 that uniquely identifies the user. When Discord forces all users to change to unique ID's and eliminates the disambiguating numbers (or allows users to change them), then where does that leave you? (For the unfamiliar: somethi…

[deleted]

Re: You'll regret using natural keys

#13

Post by jjmarr »

fn-mote wrote: post #8
Spivak wrote: post #7

Natural keys are great when they're someone else's synthetic keys. My Discord bots make liberal use of this. model: DiscordMessage key = discord_message_id key = discord_user_id key = discord_channel_id key = message_type model: UserData key = discord_user_id key = field_name model: GuildFeature key = discord_guild_id key = well_known_feature_name model: DiscordEvent key = discord_event_id key = discord_guild_id This…

I think this is an excellent example of one of the pitfalls that the author is getting at. (Disclaimer: not a Discord bot programmer.) Suppose discord_user_id is an identifier like @Spivak#2024 that uniquely identifies the user. When Discord forces all users to change to unique ID's and eliminates the disambiguating numbers (or allows users to change them), then where does that leave you? (For the unfamiliar: somethi…

Discord's real user ID is a very lengthy number not visible to end-users nor used for other purposes.

https://support.discord.com/hc/en-us/articles/206346498-Wher...

Re: You'll regret using natural keys

#14

Post by beeboobaa3 »

fn-mote wrote: post #8
Spivak wrote: post #7

Natural keys are great when they're someone else's synthetic keys. My Discord bots make liberal use of this. model: DiscordMessage key = discord_message_id key = discord_user_id key = discord_channel_id key = message_type model: UserData key = discord_user_id key = field_name model: GuildFeature key = discord_guild_id key = well_known_feature_name model: DiscordEvent key = discord_event_id key = discord_guild_id This…

I think this is an excellent example of one of the pitfalls that the author is getting at. (Disclaimer: not a Discord bot programmer.) Suppose discord_user_id is an identifier like @Spivak#2024 that uniquely identifies the user. When Discord forces all users to change to unique ID's and eliminates the disambiguating numbers (or allows users to change them), then where does that leave you? (For the unfamiliar: somethi…

Discord user IDs are their numerical unique, unchangeable, id.

https://support.discord.com/hc/en-us/articles/206346498-Wher...

Re: You'll regret using natural keys

#15

Post by bongodongobob »

davidhyde wrote: post #10

A natural key is what I would normally call unique index. Say first, surname and date of birth in an employee table as a bad (poor design) example. As opposed to a surrogate key like personId being an auto incrementing ID which is the norm since it can be easier to use when joining tables. I wish articles like these would explain terminology up front. I found it irritating to wade into the anecdotal trivia and not kn…

One reason to not do that is because you could then figure out other users IDs by their hiring date. This ended up being a problem for me once with the auxiliary systems that relied on that ID, namely when it was used in links in other applications.

Re: You'll regret using natural keys

#16

Post by robomc »

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…

yeah the way he described it it's like... well who would ever do that. but foreign keying to emails or usernames is much easier to "accidentally" do and is a classic source of long-term headaches.

Re: You'll regret using natural keys

#18

Post by hansvm »

aaronbrethorst wrote: post #6
ltbarcly3 wrote: post #5

Sometimes you will regret it, other times you won't. Notice that even if you believe that you should 'never use natural keys', that is NOT the same thing as 'always add a generated synthetic key to every table as the primary key'. You should NOT always add a generated key to every table, even if you use a trash ORM that really wants you to do this.

That’s quite the absolute statement to make without even one example.

Maybe? Their statement was that you should "not always add," as opposed to the much stronger "always not add."

Re: You'll regret using natural keys

#19

Post by projektfu »

The devil's advocate cases:

1. When duplication occurs and goes unnoticed because the natural key isn't being used, and then perhaps the problem is "corrected" by an administrator in a way that doesn't make sense.

For example, someone sets up an account with an street address A, then forgets they had it and sets up an account with street address B. They call and complain that they can't find an old order, or whatever, and the duplication is discovered. The administrator later clobbers one of the addresses but both are in use. A natural key (behaviorally speaking) may have presented the duplication, assuming address isn't part of the natural key. This can be satisfied by uniqueness constraints, etc.

2. When you are browsing the database, you see a bunch of synthetic keys and have to perform various joins to be able to see the relevant data. The synthetic keys make joins easier to write, but make may make ad-hoc queries more time-consuming and difficult.

Re: You'll regret using natural keys

#20

Post by azlev »

I think somewhat different than the article in some ways.

The first example is why `UPDATE CASCADE` was implemented. So it's possible to use natural keys as identity without the fear of children table. At least in most databases it works.

The drawback of enumeration is real, so if you expose this key you'll need some authentication/ authorization mecanism.

Another good thing in natural keys is that you can eliminate part of the joins. You don't have to join the father table because the key in child table is known.

I think the biggest challenge is how to map logins to people. It's very common to interpret both as the same, but they are not.

Post reply on HN