Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

You'll regret using natural keys

blog.ploeh.dk

Post reply on HN
Print view View original

511–520 of 568 posts

Re: You'll regret using natural keys

#511

Post by whyever »

wavemode wrote: post #363
gregw2 wrote: post #264

Earlier quoted context omitted.

Thanks for the reply. I will concede or defer to you in regards to PHI and HIPAA... it seems the philosophy behind HIPAA/PHI is very different than PII or GDPR. HIPPA is prescriptive. PII/GDPR are principle-based. HIPAA, it seems, has some text that it's not PHI if the risk is "very small" based on the opinion of someone with statistical expertise documents that it could be de-identified OR if the person avoids an ex…

The other difference between HIPAA and GDPR is compliance rules. Under GDPR it is not a problem if all your employees who have access to your database have access to PII (since, from the perspective of GDPR, the customer has given consent to share their information with your organization as a whole). Sharing with third parties outside your organization is where you get into trouble. But under HIPAA, even your own emp…

I don't think GDPR is that different in that regard, you also have to minimize access to PII within the organization. See Article 25:

> The controller shall implement appropriate technical and organisational measures for ensuring that, by default, only personal data which are necessary for each specific purpose of the processing are processed. That obligation applies to the amount of personal data collected, the extent of their processing, the period of their storage and their accessibility. In particular, such measures shall ensure that by default personal data are not made accessible without the individual's intervention to an indefinite number of natural persons.

Re: You'll regret using natural keys

#512

Post by cogman10 »

hot_gril wrote: post #508
cogman10 wrote: post #507

Earlier quoted context omitted.

Yes but also no. Yes, in that there are DB technologies not built in a fashion where records are stored in a sorted order of some fashion. No, in that they are very much not common technologies. Most databases, relational, non-relational, etc have some form of a B-Tree at their core somewhere.

I can see this. Spanner is an example where you don't want this, idk if that's considered common enough. Postgres and MySQL both support hash indexes that are unordered, but the default in both is btree, and Postgres hash indexes used to have some caveats that made them unsuitable (idk about now) so I've gotten in the habit of just using the default. MySQL docs claim that a hash index is much faster if you only need…

As always, depends on the implementation.

Hash maps should generally always have faster lookups than Btree based structures. However, they'll have slower writes especially when contested. A key issue hash tables have to deal with is what happens when a remapping needs to happen. For example, when 2 keys have the same hash. In that case, locking becomes a lot more messy.

For a btree this is simpler. It's built to be able to handle reshuffling and rebalancing in a way that's semi easy to have fine grained locks around.

Re: You'll regret using natural keys

#513

Post by hot_gril »

cogman10 wrote: post #512
hot_gril wrote: post #508

Earlier quoted context omitted.

I can see this. Spanner is an example where you don't want this, idk if that's considered common enough. Postgres and MySQL both support hash indexes that are unordered, but the default in both is btree, and Postgres hash indexes used to have some caveats that made them unsuitable (idk about now) so I've gotten in the habit of just using the default. MySQL docs claim that a hash index is much faster if you only need…

As always, depends on the implementation. Hash maps should generally always have faster lookups than Btree based structures. However, they'll have slower writes especially when contested. A key issue hash tables have to deal with is what happens when a remapping needs to happen. For example, when 2 keys have the same hash. In that case, locking becomes a lot more messy. For a btree this is simpler. It's built to be a…

Hm yeah, now I'm wondering how MySQL implements it. Consistent hashing is one way to limit the scope of a remap.

Re: You'll regret using natural keys

#514

Post by selcuka »

yellowapple wrote: post #201
aaronbrethorst wrote: post #69

Earlier quoted context omitted.

Fast-forward a couple years: now I have scripts that launch sub-tasks more than once a second.

So use timestamps with sub-second precision, which virtually every SQL database supports (even ones like SQLite that don't have built-in date/time types).

What about ntpd updates, or multiple scripts running in parallel?

Re: You'll regret using natural keys

#515

Post by selcuka »

kubanczyk wrote: post #81
selcuka wrote: post #58

Earlier quoted context omitted.

> Why is knowing someone's ID an issue? It increases the attack surface as an authorization vulnerability will allow an attacker to enumerate and access all records. Yes, it is security through obscurity, but a random (e.g. UUID) scheme makes it harder.

~128 bits worth of obscurity is considered real security for the time being. Assuming a cryptographically secure PRNG. Thats like guessing a password 18 ASCII chars long.

> ~128 bits worth of obscurity is considered real security for the time being.

Sure, what I meant was UUIDs are not supposed to be confidential information, unlike passwords. They are exposed in URLs and whatnot.

Re: You'll regret using natural keys

#516

Post by wesselbindt »

nikita2206 wrote: post #418
tapland wrote: post #359

Earlier quoted context omitted.

It's not really fine. I work on a Healthcare system in the nordics. Who you billed, who visited what doctor, who your primary care provider is, all the people a doctors office has as patients, refers to the SSN. You don't want to lose that connection or to have to update everything for any change. You store a unique identifier for the person in the system, and you can then pull the actual personal identification numb…

But there’s no natural key for a person, not even the SSN. This article also starts off by showing an example where no natural key exists (Restaurants) and acknowledges it.

Solution: use a synthetic one

Re: You'll regret using natural keys

#517

Post by sgarland »

parpfish wrote: post #481
sgarland wrote: post #457

Earlier quoted context omitted.

> you need to do an adhoc query to look something up so you have to type in the key in a where clause Having a surrogate key here doesn’t help. A WHERE predicate can be rewritten as an INNER JOIN quite easily. Or you could use a subquery, or a CTE. Many options. The other problems discussed are an engineering culture problem. Either you value correctness and consistency or you don’t.

not sure how converting a WHERE to a JOIN helps when you're doing adhoc queries like this to look up records: `SELECT * FROM users WHERE email LIKE 'FirstName@domain.net'`

I misunderstood your point; I thought you were saying having a surrogate key here would be better.

For the above, either ensure that all email addresses are stored in one case (and index / query on the same), or if that’s impossible for some reason, you can query with ILIKE, optionally creating a trigram index (for Postgres; this isn’t a problem in MySQL since by default it’s entirely case-insensitive) on the column. Another option is to index with a functional index (CREATE INDEX user_email_lowercase ON user (LOWER(email))), and then code the API doing these lookups to cast to lowercase. That way, it’ll be retrieved and displayed however the user entered it, but retrieved based only on the CI version.

Re: You'll regret using natural keys

#519

Post by ssahoo »

strnisa wrote: post #151

I've become a fan of unique, relatively short and "human-readable" IDs, such at the ones used by Stripe, e.g. `cus_MJA953cFzEuO1z` for an ID of a customer. Here's a Stripe dev article on the topic: https://dev.to/stripe/designing-apis-for-humans-object-ids-3... If you use JavaScript/TypeScript, you can make them like this: function makeSlug(length: number): string { const validChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcde…

UUIDv7's premise to solve this, isn't it? it's 32 chars without dashes.

Re: You'll regret using natural keys

#520

Post by wavemode »

whyever wrote: post #511
wavemode wrote: post #363

Earlier quoted context omitted.

The other difference between HIPAA and GDPR is compliance rules. Under GDPR it is not a problem if all your employees who have access to your database have access to PII (since, from the perspective of GDPR, the customer has given consent to share their information with your organization as a whole). Sharing with third parties outside your organization is where you get into trouble. But under HIPAA, even your own emp…

I don't think GDPR is that different in that regard, you also have to minimize access to PII within the organization. See Article 25: > The controller shall implement appropriate technical and organisational measures for ensuring that, by default, only personal data which are necessary for each specific purpose of the processing are processed. That obligation applies to the amount of personal data collected, the exte…

GDPR is indeed different in that regard. And I'm not sure how your quote says otherwise. "The employees of this company" are not an indefinite number of persons.
Post reply on HN