3 ms·

> The pervasive use of artificial keys. USE NATURAL KEYS. Unfortunately probably 99% of real-world databases were designed with artificial keys. I wish I could

> The pervasive use of artificial keys. USE NATURAL KEYS. Unfortunately probably 99% of real-world databases were designed with artificial keys. I wish I could point to some literature on this topic. It is very rare and I only came to learn about this from a DBA who is well-versed in designing with natural keys. I'm trying to get him to publish more on this topic. While I really enjoy the concept of Natural Keys, I just see so few places where they are applicable. If I was designing a banking Db schema, I could see using an account number as a primary key as long as I am not exposing my Db to anything but internal systems. However, if I'm designing a social networking platform, what can I use as a natural primary key for a user object? I can't use a name because not all names are unique and they can change. I can't use an email because they can change and I feel like Email addresses can get pretty large(more space and slower lookups). I could maybe use a username if I enforce that a username can never change and must be unique. But, we then run into the same issues as emails where a username can be fairly long and thus cause slower lookups. I also don't like the idea of using strings as primary keys either because I would need to take into account implementation details like string encoding (utf8, utf16, utf32, ascii, latin-1) and make sure to encode/decode on every lookup/insert. So, I can see some use cases where natural primary keys make sense. However, I believe that for most use cases, artificial keys are a better choice. Integers don't take up a lot of space, it's easy to enforce uniqueness, they are a natural sequence, and they rarely, if ever need to change[1]. [1] In fact, I would make the argument that if you're altering primary keys at all, you're doing something wrong.

jackfoxy 9y ago
Properly identifying the natural keys requires more up front thinking than slapping on an identity or guid column. Also it ends up with a different set of tables at the end of the day. So looking at your current schema and saying natural keys don't work here is probably true. It's a big topic, and like I said, not enough literature, but if you search sql natural key you can get started.
- gnaritas 9y ago
  There are no proper natural keys, natural keys are a bad solution to the real world problem of running and managing an application. The correct solution is to use a constraint to enforce the uniqueness of your supposedly "natural key", and use an artificial key to actually join and code against because quite frankly natural keys suck and are commonly multi-column keys that require updating and joining against all of which make them poor choices for programming against. Surrogate keys don't break your app and code when business rules change or you finally figure out your natural key isn't so natural and has exceptions that forces you to make it not a key. If you think natural keys are a good solution, you've not lived outside of the database where real $$ is on the line. Those who live in a SQL ivory tower where breaking changes don't matter like natural keys; those who actually write applications that use databases and have to deal with the constant churn and change of business rules know how foolish natural keys are and being pragmatic and understanding how to correctly use a pointer to avoid the need to cascade updates and make the schema immune to business rule changes have long ago chosen surrogate keys which are the vastly superior engineering solution.
  - amilevin 9y ago
    Hi Gnaritas, I would love to have a discussion with you on the topic, but to get started I think we'll need a little more solid ground than the over-generalized, empty claims you made: "There are no proper natural keys" / "Natural keys are a bad solution" / "The correct solution is.." / "Natural keys suck" etc. Can you provide viable/scientific/quantitative/reproducible/logical, either practical or theoretical arguments to support these claims? If you can, I would be happy to discuss those with you in detail. BTW - If you need to change your database schema when your business rules change, the root problem is your data model. It means your database schema models the business rules, instead of modeling the data universe. Before we start the discussion, I would like to suggest that you read the following two short articles. I think it will give us a more solid common terminology. http://www.informationweek.com/software/information-management/celko-on-sql-identifiers-and-the-properties-of-relational-keys/d/d-id/1058284 http://www.informationweek.com/software/information-manageme...? http://www.informationweek.com/software/information-management/celko-on-sql-natural-artificial-and-surrogate-keys-explained/d/d-id/1059246 http://www.informationweek.com/software/information-manageme...? Cheers, Ami

jackfoxy 9y ago

Properly identifying the natural keys requires more up front thinking than slapping on an identity or guid column. Also it ends up with a different set of tables at the end of the day. So looking at your current schema and saying natural keys don't work here is probably true. It's a big topic, and like I said, not enough literature, but if you search sql natural key you can get started.

gnaritas 9y ago
There are no proper natural keys, natural keys are a bad solution to the real world problem of running and managing an application. The correct solution is to use a constraint to enforce the uniqueness of your supposedly "natural key", and use an artificial key to actually join and code against because quite frankly natural keys suck and are commonly multi-column keys that require updating and joining against all of which make them poor choices for programming against. Surrogate keys don't break your app and code when business rules change or you finally figure out your natural key isn't so natural and has exceptions that forces you to make it not a key. If you think natural keys are a good solution, you've not lived outside of the database where real $$ is on the line. Those who live in a SQL ivory tower where breaking changes don't matter like natural keys; those who actually write applications that use databases and have to deal with the constant churn and change of business rules know how foolish natural keys are and being pragmatic and understanding how to correctly use a pointer to avoid the need to cascade updates and make the schema immune to business rule changes have long ago chosen surrogate keys which are the vastly superior engineering solution.
- amilevin 9y ago
  Hi Gnaritas, I would love to have a discussion with you on the topic, but to get started I think we'll need a little more solid ground than the over-generalized, empty claims you made: "There are no proper natural keys" / "Natural keys are a bad solution" / "The correct solution is.." / "Natural keys suck" etc. Can you provide viable/scientific/quantitative/reproducible/logical, either practical or theoretical arguments to support these claims? If you can, I would be happy to discuss those with you in detail. BTW - If you need to change your database schema when your business rules change, the root problem is your data model. It means your database schema models the business rules, instead of modeling the data universe. Before we start the discussion, I would like to suggest that you read the following two short articles. I think it will give us a more solid common terminology. http://www.informationweek.com/software/information-management/celko-on-sql-identifiers-and-the-properties-of-relational-keys/d/d-id/1058284 http://www.informationweek.com/software/information-manageme...? http://www.informationweek.com/software/information-management/celko-on-sql-natural-artificial-and-surrogate-keys-explained/d/d-id/1059246 http://www.informationweek.com/software/information-manageme...? Cheers, Ami