[jhdkjqhkjqhwk]

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]

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]

There's a big difference between "things that need to be unique" and "things that are unique and also never, ever change"