4 ms·

What you're talking about is a natural key[1], which can be contrasted with a surrogate key[2]. (Surrogate keys are also called "synthetic keys" or "factless ke

What you're talking about is a natural key[1], which can be contrasted with a surrogate key[2]. (Surrogate keys are also called "synthetic keys" or "factless keys".) So the question is whether natural keys are ever useful. I think the answer is yes, they are useful if the real world already imposes a key on the stuff you are modeling in the database. In that case, it is already unique (as it needs to be), and generating your own key doesn't accomplish anything (but is redundant). For example, suppose I'm creating a history database of who owned what internet domain names at which times. If my table is one-to-one with domains, I can use the domain name itself as the primary key. If not, I can perhaps use the domain name plus some other column as a composite key. For a table of ownership intervals, my primary key could be this: domain name + start date + end date. --- [1] https://en.wikipedia.org/wiki/Natural_key https://en.wikipedia.org/wiki/Natural_key [2] https://en.wikipedia.org/wiki/Surrogate_key https://en.wikipedia.org/wiki/Surrogate_key

whatshisface 5y ago
>For example, suppose I'm creating a history database of who owned what internet domain names at which times. If my table is one-to-one with domains, I can use the domain name itself as the primary key. In your example, wouldn't that not work, because the same domain would be in the table twice with two different owners?
- adrianmonk 5y ago
  You might have more than one table in the database. For example, you could have a table of current owners for quick lookup and a table of all owners for full history. For the current owners table, you'd use just the domain as a key. For all the full history table, you'd use domain + start date + end date as the key. So yeah, that makes it a weird example, but the point is that in neither table did you make up a synthetic key. In one table, you used a real-world fact (domain) as the key, and in the other table you used three real-world facts (domain plus two dates) together as a key.

whatshisface 5y ago

>For example, suppose I'm creating a history database of who owned what internet domain names at which times. If my table is one-to-one with domains, I can use the domain name itself as the primary key. In your example, wouldn't that not work, because the same domain would be in the table twice with two different owners?

adrianmonk 5y ago
You might have more than one table in the database. For example, you could have a table of current owners for quick lookup and a table of all owners for full history. For the current owners table, you'd use just the domain as a key. For all the full history table, you'd use domain + start date + end date as the key. So yeah, that makes it a weird example, but the point is that in neither table did you make up a synthetic key. In one table, you used a real-world fact (domain) as the key, and in the other table you used three real-world facts (domain plus two dates) together as a key.