You'll regret using natural keys
blog.ploeh.dk
You'll regret using natural keys
1–10 of 568 posts
Re: You'll regret using natural keys
#2I just have some form of unique id field / synthetic key, everywhere.
Even if just for documenting issues it makes life easier. “Table whatever id 12345 is the record in question.”
I’ve just seen data / relationships change too much too often in new and interesting ways to believe in using a natural key.
Re: You'll regret using natural keys
#3[deleted]
Re: You'll regret using natural keys
#4[deleted]
[deleted]
Re: You'll regret using natural keys
#5Sometimes 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.
Re: You'll regret using natural keys
#6Sometimes 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.
Re: You'll regret using natural keys
#7Natural 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
Re: You'll regret using natural keys
#8Natural 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: something like this happened. Not sure if those are really the "discord user id" used in bot service though, but suppose that they are for the sake of the argument.)
Re: You'll regret using natural keys
#9Feels 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 than "uniqueness"
Re: You'll regret using natural keys
#10A 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 know what I was reading about and I’m very familiar with how databases work!