undefined | Better HN

0 pointsjackfoxy9y ago0 comments

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.

0 comments

3 comments · 1 top-level

gnaritas9y ago· 2 in thread

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.

amilevin9y 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-manageme...?

Cheers,

Ami

brantam9y ago

> the correct solution is to use a constraint to enforce the uniqueness

That would of course be correct for a natural key. It seems that you too recommend using natural keys and I agree with you on that point. Natural keys are required so that a database can represent the real world accurately and enforce essential business rules. We all rely on websites to enforce the uniqueness of user names; we all expect our bank to enforce the uniqueness of account numbers.

Those who live in an ivory tower where data quality doesn't matter don't like natural keys; those who write applications that have to work in the real world know how foolish it is to leave out natural keys. Those older and wiser heads will have seen and dealt with the serious practical consequences of shoddy databases where natural keys weren't implemented. I think that is the problem being referred to in this thread.

j / k navigate · click thread line to collapse