4 ms·

The biggest problem with "natural" keys, as referenced in the article: they have a nasty habit of changing. In the article's example, making "username" or "emai

by al2o3cr 12y ago

The biggest problem with "natural" keys, as referenced in the article: they have a nasty habit of changing. In the article's example, making "username" or "email" the PK is going to be messy if you have foreign keys referencing the value when it updates.

userbinator 12y ago
Using email I can see being a problem, but username? If this is for identifying accounts, then that shouldn't be one that ever changes.
- lacksconfidence 12y ago
  really depends on the site, several places let you change your username.
- skywhopper 12y ago
  If identity is a concern, maintain a list of old usernames. But in the real world, there are always extenuating circumstances in which allowing a username to change is good policy. Admins could blindly enforce a "never change usernames" policy, but why not just use a meaningless key and avoid the risk?
- avemg 12y ago
  Jane Doe gets married and changes her name Jane Smith. She no longer wishes to log into your system as janedoe and would prefer to login as janesmith. Certainly one could make the argument that you should just tell her that the name can't be changed and she'll have to just live with it. Depending on your business, that may or not be acceptable.
  - emeraldd 12y ago
    Even worse, Jane Smith decides she hates that bleep John and they get divorced. She really won't want to see Smith as part of her username anymore.
- Monkeyget 12y ago
  I am glad that twitter, github and stackoverflow allowed me to change my username. You could call it 'display name'. I wish hn would allow it.
- owlmanatt 12y ago
  You're ubinator. Unfortunately, you come home one day to find Mr. Binator in bed with some 20-something blond hussie. The divorce paperworks sails through. Do you still want to be ubinator, reminded of that bastard every time you have to log in, or would you prefer to go back to umaidenname? We both know where HR would stand on that one :V
- eli 12y ago
  Someone else wants to take over my account. Forever and ever they have to log in using my name?
- GFK_of_xmaspast 12y ago
  Suppose my name is "Paul", my username is "paul". Now I've just transitioned and I'm not "Paul" anymore. What then?
davidw 12y ago
They are often also difficult to get quite right. If you use an artificial key, you're pretty much good to go unless you want to make a distributed system. With natural keys, you run the risk of not having analyzed your data properly and picking the wrong thing.
jhdkjqhkjqhwk 12y ago
If it's liable to change, it's not a natural key. Yes, requirements change and flexibility is a good thing to build in, but any system needs to be designed with a set of invariants in mind. And those invariants need to be effectively communicated to stakeholders. These invariants are useful, as they help people understand the system. Usernames are a great example. If users want to refer to one another (or to themselves) they're going to need a consistent and transparent way to do so. Showing them a username which is behind-the-scenes mapped to a synthetic key is just leaving the door open for bugs to manifest down the track. The benefits flow on when you are presenting related information live or through an API -- a human-readable foreign key means you can provide useful information without having to perform a join. This does wonders for debugging and discoverability. Useless use of opaque keys (integers or shudder GUIDs) when a perfectly good natural key is available is in my experience a tremendous code smell. It leaves the opportunity for lazy developers to forget about invariants -- in schema design you'll see such things missing unique constraints, and then foreign systems to which the true keys aren't exposed doing horrible, fragile things whenever they have to traverse a relation.
- emeraldd 12y ago
  Natural keys are also a nightmare to deal with when you have to do complex joins. They are not guaranteed to be a single column value and can lead to joining across a half dozen columns when you need data from multiple tables. Usernames are actually a very good example of something that looks like an invariant when in actuality is anything but. On top of that, they are notoriously inconsistent between systems so you can't rely on usera in one system being usera in another even when you control both systems. Even if you do have a nice single column natural key, what happens when a business requirement changes and there's suddenly a completely new coding system? Or perhaps your coding system needs a new distinction, do you rely on hand or create a new column? How many tables to you have to touch to change to accommodate the new data? How much code is relying your tables be structured just so? Natural keys look appealing on face, but they can all too easily lead to a big mess down the road. The larger and more complex the system, the more external systems that you have to integrate, the bigger the mess becomes.
- eli 12y ago

by al2o3cr 12y ago

userbinator 12y ago

Using email I can see being a problem, but username? If this is for identifying accounts, then that shouldn't be one that ever changes.

lacksconfidence 12y ago
really depends on the site, several places let you change your username.
skywhopper 12y ago
If identity is a concern, maintain a list of old usernames. But in the real world, there are always extenuating circumstances in which allowing a username to change is good policy. Admins could blindly enforce a "never change usernames" policy, but why not just use a meaningless key and avoid the risk?
avemg 12y ago
Jane Doe gets married and changes her name Jane Smith. She no longer wishes to log into your system as janedoe and would prefer to login as janesmith. Certainly one could make the argument that you should just tell her that the name can't be changed and she'll have to just live with it. Depending on your business, that may or not be acceptable.
- emeraldd 12y ago
  Even worse, Jane Smith decides she hates that bleep John and they get divorced. She really won't want to see Smith as part of her username anymore.
Monkeyget 12y ago
I am glad that twitter, github and stackoverflow allowed me to change my username. You could call it 'display name'. I wish hn would allow it.
owlmanatt 12y ago
You're ubinator. Unfortunately, you come home one day to find Mr. Binator in bed with some 20-something blond hussie. The divorce paperworks sails through. Do you still want to be ubinator, reminded of that bastard every time you have to log in, or would you prefer to go back to umaidenname? We both know where HR would stand on that one :V
eli 12y ago
Someone else wants to take over my account. Forever and ever they have to log in using my name?
GFK_of_xmaspast 12y ago
Suppose my name is "Paul", my username is "paul". Now I've just transitioned and I'm not "Paul" anymore. What then?

davidw 12y ago

They are often also difficult to get quite right. If you use an artificial key, you're pretty much good to go unless you want to make a distributed system. With natural keys, you run the risk of not having analyzed your data properly and picking the wrong thing.

jhdkjqhkjqhwk 12y ago

If it's liable to change, it's not a natural key. Yes, requirements change and flexibility is a good thing to build in, but any system needs to be designed with a set of invariants in mind. And those invariants need to be effectively communicated to stakeholders. These invariants are useful, as they help people understand the system. Usernames are a great example. If users want to refer to one another (or to themselves) they're going to need a consistent and transparent way to do so. Showing them a username which is behind-the-scenes mapped to a synthetic key is just leaving the door open for bugs to manifest down the track. The benefits flow on when you are presenting related information live or through an API -- a human-readable foreign key means you can provide useful information without having to perform a join. This does wonders for debugging and discoverability. Useless use of opaque keys (integers or shudder GUIDs) when a perfectly good natural key is available is in my experience a tremendous code smell. It leaves the opportunity for lazy developers to forget about invariants -- in schema design you'll see such things missing unique constraints, and then foreign systems to which the true keys aren't exposed doing horrible, fragile things whenever they have to traverse a relation.

emeraldd 12y ago
Natural keys are also a nightmare to deal with when you have to do complex joins. They are not guaranteed to be a single column value and can lead to joining across a half dozen columns when you need data from multiple tables. Usernames are actually a very good example of something that looks like an invariant when in actuality is anything but. On top of that, they are notoriously inconsistent between systems so you can't rely on usera in one system being usera in another even when you control both systems. Even if you do have a nice single column natural key, what happens when a business requirement changes and there's suddenly a completely new coding system? Or perhaps your coding system needs a new distinction, do you rely on hand or create a new column? How many tables to you have to touch to change to accommodate the new data? How much code is relying your tables be structured just so? Natural keys look appealing on face, but they can all too easily lead to a big mess down the road. The larger and more complex the system, the more external systems that you have to integrate, the bigger the mess becomes.
eli 12y ago