Comment by b-man | Hacker News Reader

Comment by b-man | Hacker News Reader

Parent Full thread

- Joins, lookups, indexes. Here data type can matter regarding performance and resource use.

I struggle to see a practical example.

> - Idempotency. Allowing a client to generate IDs can be a big help here (ie UUIDs)

Natural keys solves this

> - Sharing. You may want to share a URL to something that requires the key, but not expose domain data (a URL to a user’s profile image shouldn’t expose their national ID).

The you have another piece of data, which you relate to the natural key. Something like `exposed-name`.

> There is not one solution that handles all of these well

Natural keys solve these issues.

> Also, we all know that stakeholders will absolutely swear that there will never be two people with the same national ID. Oh, except unless someone died, then we may reuse their ID. Oh, and sometimes this remote territory has duplicate IDs with the mainland. Oh, and for people born during that revolution 50 years ago, we just kinda had to make stuff up for them.

If this happens, the designer had a error in his design, and should extend the design to accommodate the facts that escaped him at design time.

> Actually, the article is proposing a new principle

I'm putting it in words, but such knowledge has been common in the database community for ages, afaict.

> If this happens, the designer had a error in his design, and should extend the design to accommodate the facts that escaped him at design time.

Errors in the initial design should be assumed as the default. Wise software engineering should make change easy.

Constraints on natural keys are business logic, not laws of mathematical truth. They are subject to change and often violated in the real world. The database as record-keeping engine should only enforce constraints on artificial keys that maintain its integrity for tracking record identity, history, and references.

Your database may not be primarily a record-keeping engine, and your tradeoffs may be different.

> Errors in the initial design should be assumed as the default. Wise software engineering should make change easy.

I don't think I said that errors would not happen.

> I struggle to see a practical example.

Memory, and CPU, and even storage eventually, those would be the main practical examples of where having a key that's composed of something very small saves you space and thus, time.

Say we want to use a bigint key vs a VARCHAR(30)? depending on your big key you might be talking about terabytes of additional data, just to store a key (1t rows @ bigint = 8TB, 1T rows at 30 chars? 30TB...). The data also is going to constantly shuffle (random inserts).

If you want to define the PK as the natural key with no separate column then you get to do comparisons on all the natural key columns themselves, so instead of doing 1 4 or 8 byte column comparison you get to do what? 5 char comparisons?

Having worked extensively in ETL - when a developer tells me "there's no duplication about this real world process" what they mean is "there's no duplication in my mental model about this real world process"

> Memory, and CPU, and even storage eventually, those would be the main practical examples of where having a key that's composed of something very small saves you space and thus, time.

> Say we want to use a bigint key vs a VARCHAR(30)? depending on your big key you might be talking about terabytes of additional data, just to store a key (1t rows @ bigint = 8TB, 1T rows at 30 chars? 30TB...). The data also is going to constantly shuffle (random inserts).

>> Joins, lookups, indexes

I don't see how what you brought up has anything to do with these.

But the main point is being missed here because of a physical vs logical conflation anyhow.

> Natural keys solves this

It would be helpful if the article used a natural key. Instead, it uses a primary key that is neither natural nor guaranteed, and is mutable. It makes assumptions that are not true, and that is one of the big dangers in using natural keys.

> If this happens, the designer had a error in his design, and should extend the design to accommodate the facts that escaped him at design time.

This again is a dangerous assumption. The danger here is the assumption that facts don't change. Facts do change. And facts that are true at design time are not necessarily true 1 day later, 1 year later, or 1 decade later, or more.

Again, when the example can't even use a natural key to present it's idea of using natural keys, we have a problem.

The problem with natural keys is that nobody ever says, "My bad, I should have spelled my name the same way on this form as I did when I registered for the service, I promise not to do it again." Instead they say, "No matter how I spell it, you should have still known it was my name!"

Reply on news.ycombinator.com