On Primary Keys
orchestrate.io
orchestrate.io
No, they shouldn't. The fact that your dataset may contain a piece of information that is usually unique its completely unrelated to the need of RDMSs to have identifiers to implement table relationships and to identify records in a table.
Not to mention that it can bring lots of headaches down the line. The classic example is using social security numbers (or another country's equivalent, such as CPF in Brazil) to identify individuals. Down the line you get a requirement to support international customers. That is now a major undertaking.
You are also offloading the responsibility of maintaining the uniqueness property to a third party, in effect trusting their database.
Database keys are more-or-less equivalent to pointers. In the case of natural keys, they're pointers that (a) do not have ASLR, and (b) are exposed to the user.
As the article mostly ignores, you will guess wrong about what can be reasonably considered permanent or unique. Email addresses change (and aren't always unique), names change (and aren't unique), etc. This is much harder to fix if you used that guess as the basis for your entire data model.
The problem with proper keys was given as "auto_increment doesn't work on distributed systems". This is rather silly, as there are plenty of alternatives (GUID, UUID, the mentioned snowflake keys).
It's also interesting that proper keys are a "crutch" for designing data models and are best replaced with cleverness, and apparently are only useful to dictate record order (I know that Oracle at least doesn't work that way). This brings to mind "debugging code is twice as hard as writing code; if you write code as cleverly as possible, you are by definition not clever enough to debug it". DON'T BE (OVERLY) CLEVER. Do the least-fragile thing that doesn't add complexity (that is, doesn't add complexity visible to you.). Only do clever things that add fragility if you have an actual measured reason to do so.
As a database architect I always use auto-incrementing as the primary key. This ID is actually a surrogate key for the real primary composite key, which I assign to a unique key.
So, with the unique key in place, the performance will be nearly as good as you'd expect for direct looks by key attributes. However, the performance gain using the surrogate ID within join tables and as foreign keys is much greater.
"It’s also worth noting that over their years of use in SQL databases they’ve become somewhat of a crutch when designing the data model for a system."
They are not a crutch. They are a wonderful feature. One could model with GUIDs if necessary or something similar for your surrogate key but nothing beats the simplicity of an auto incrementing ID.
Even a NoSQL database like MongoDB creates a unique _id for you for each document.
Then we dive into some mistruths about emails being unique per user (I know plenty of people who still share email addresses, both personal and also in shared commercial accounts). I worked at a place a few years ago that had designed their entire accounts system around the idea of email addresses being immutable. They'd subsequently had to introduce a secondary column for actual email addresses -- customers would login with their historical email address as their ID. The idea that a uid is a 'natural key' just seems perverse (or at least demonstrates a profound misunderstanding of what a natural key is).
A sideways reference - without actually using the phrase - to CAP theorem. Composite keys are suggested as a solution to a vaguely phrased problem without acknowledging the performance issues, and subsequent design constraints, they introduce. Ultimately there seems to be a misunderstanding about the differences between a (primary) key and an index.
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
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.
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.
Obviously YMMV (as may your definition of 'large' and 'performant').
Perhaps the only gotcha I've run into is that JS can't reliably generate them. (You need a decent RNG, and while JS has an RNG, no guarantees are made about it.) (Trivially work-aroundable with an AJAX endpoint on the server, of course.)
So, in practice they are terrible primary keys in systems that do not have a native UUID type. In that case, an 8-byte bigint generated externally by a ticket service like Snowflake is often far superior.
I'm going to argue that that's not the norm: "3-byte unicode" is kinda WTF, since it doesn't really exist. If you're in UTF-8, it's a 36 byte comparison.
That sounds, however, a lot like MySQL, which has perhaps one of the more braindead "UTF-8" implementations. That said, I'm not sure that it uses 3 bytes for code points that don't require it. (At least, that it spaces them that way: there may be nulls past the data, sure, but those won't count in a comparison.)
Primary/unique keys are indexed. Creating an index of strings instead of integers is surely going to result in a huge index and slow performance?
The article makes it seem like your primary key and main index have to be the same, but that's not necessarily the case. Most DBs have some support for physically ordered indexes.
So take username, for example. Let's say username has to be unique, but it can change. (It doesn't even matter too much if it's not totally unique, just mostly unique.) Let's say you're going to be doing most of your lookups in the user table by username.
If performance is an issue, keep your auto-incrementing guaranteed-unique primary key and add a clustered index on user name. The data is now physically stored by its natural key, but you still have a nice unique, unchanging primary key. Lookups are very fast, though depending on the implementation, inserts/updates might not be so much.
Postgres supports this with the CLUSTER command, although it has to be run manually - it doesn't keep up as updates and inserts come in. SQL Server calls it a clustered index and enforces the physical ordering on inserts and updates. Oracle has indexed-ordered tables which can achieve a similar effect, though IIRC the index has to be the PK (nothing to stop you from having a unique constraint on a sequence-generated column, though.)
1234 "John Doe" null null null
5824 "John Doe" "Sale 1" "item 1" $5
4321 "John Doe" "Sale 1" "item 2" $50
4382 "John Doe" "Sale 2" "item 1" $4
CREATE UNIQUE INDEX (name, sale, item);I once let myself in for a lot of headaches by designing a system around this unwarranted assumption.
Previously discuss here, http://news.ycombinator.com/item?id=3058327
Gap-free, monotonically increasing id's should only be used for things that have regulatory compliance issues. For example, there shall be no gaps in invoice id's, and voided ones shall be marked.
Kind of "eventually monotonic" distributed auto-increment keys. Your mileage may vary.
With JS disabled, the page header is white text on a transparent background.
With JS enabled, I see no way to remove the header. I have little enough vertical screen real estate, adding an extra chunk of stuff on the top doesn't help. I ended up removing it entirely.