Comment by adileo | Hacker News Reader

Comment by adileo | Hacker News Reader

Parent Full thread

Very interesting insight, as you say, probably ORMs instilled this way of thinking, while the primary key is not just constrained to that function I perfectly agree with that.

* at the cost of dealing with long keys creating large indexes and more cache thrashing in the RDBMS. *

This is actually one of the point I'm currently dealing with. Especially finding the right trade-off to manage records in the order of hundred millions/billions, the right decision could save GB of space both on disk and RAM and seconds in computation during each JOIN operation for example.

That's why I drafted out a comparison table, sometimes I see people start using UUIDs right away just because they are offered as out of the box in some ORMs or just because it souds "cool", without knowing that there are better or simplier alternatives out there.

I've run into the same problems. You compiled good useful information, I will bookmark. If you had named it "Choosing the best surrogate key format" I wouldn't have complained at all!

I have a customer with a fairly large database (university students). URLs on the site often have primary keys in them. They use autoincrement integers for the primary keys. Most operations require the user to log in first, so the system can check access to rows based on user permissions, so even if someone tries to change the key in the URL they won't get anything. For the handful of public-facing URLs that have a key in them we set up tokens (UUIDs) that map through an intermediate table to the real key. That way we get the benefits of simple incrementing integer keys for authorized users and internal operations, and hide those keys when presented to the outside world. Very much what you describe. We could not identify good natural keys for the main tables. Some tables have natural keys, i.e. the country table uses ISO country codes. The ordering/pagination issue you mention in your article falls outside the realm of proper relational database design but comes up a lot in real applications.

Reply on news.ycombinator.com