[brettgriffin]

> Stripping information from an identifier disconnects a piece of data from the real world which means we no longer can match them. But such connection is the sole purpose of keeping the data in the first place.

The surrogate key's purpose isn't to directly store the natural key's information, rather, it's to provide an index to it.

> The solution is not to come up with yet another artificial identifier but to come up with better means of identification taking into account the fact that things change.

There isn't 'another' - there's just one. The surrogate key. The other pieces of information you're describing are not the means of indexing the data. They are the pieces of data you wish to retrieve.

[mkleczek]

Any piece of information that can be used to retrieve something using this index has to be available "outside" your database - ie. to issue a query "give me piece of information identified by X" you have to know X first. If X is only available in your index then you must have another index to retrieve X based on some externally available piece of information Y. And then X becomes useless as an identifier - it just adds a level of indirection that does not solve any information retrieval problem.

That's my whole point: either X becomes a "real world artifact" or it is useless as identifier.

[brettgriffin]

That's not really how data is requested. Most of these identifiers are foreign keys - they exist in a larger object graph. Most systems of records are too large for people to associate surrogate keys to anything meaningful - they can easily have hundreds of billions of records.

Rather, users traverse that through that object graph, narrowing a range of keys of interest.

This hacker news article was given a surrogate key, 46272487. From that, you can determine what it links to, the name/date/author of the submission, comments, etc.

46272487 means absolutely nothing to anybody involved. But if you wanted to see submissions from user pil0u, or submissions submissions on 2025-12-15, or submissions pertaining to UUID, 46272487 would in that in that result set. Once 46272487 joins out to all of its other tables, you can populate a list that includes their user name, title, domain, etc.

Do not encode identifying information in unique identifiers! The entire world of software is built on surrogate keys and they work wonderfully.

[mkleczek]

> This hacker news article was given a surrogate key, 46272487. From that, you can determine what it links to, the name/date/author of the submission, comments, etc.

> Do not encode identifying information in unique identifiers! The entire world of software is built on surrogate keys and they work wonderfully.

The amount of manual work required to manage duplicates is in no small part the result of not thinking enough about the identifiers and simply slapping surrogate keys on the data.