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

SQL Keys in Depth

begriffs.com

Post reply on HN
Print view View original

151–160 of 174 posts

Re: SQL Keys in Depth

#151

Post by int_19h »

WorldMaker wrote: post #141
int_19h wrote: post #20

Earlier quoted context omitted.

In Postgres, having multiple tables share a single ID sequence is trivial thanks to SEQUENCE/NEXTVAL.

Sure, but Postgres doesn't to my knowledge support a cross-table UNIQUE index to insure your sequences are followed in all cases. I imagine that all it takes is one confused junior developer to add a bunch of manually IDed data by hand to create some weird and confusing corner cases in this scheme.

You can define the field as GENERATED ALWAYS, though, in which case specifying the value explicitly in INSERT won't work (there's an escape hatch, but it has to be explicitly and very verbosely invoked).

Re: SQL Keys in Depth

#152

Post by dizzystar »

wvenable wrote: post #148
dizzystar wrote: post #146

Earlier quoted context omitted.

Yes, each set of data should reflect the data you want to use. That's why I said I default to natural keys unless there is good reason to use a surrogate key. If I have to compare databases I've worked on over the years, the databases that are all surrogate will have many more data errors than a database that is predominantly natural keys. I could write a book on why that happens, but if you don't know relational the…

I'm working with a 3rd party database right that used more than a few natural keys and it's awful. The primary key for "users" is username so we can't rename our users to improve our company's username security policy. And that's just one of the problems. We'll be offline for a full 24 hours in April to handle changing the value (for necessary business reasons) of the natural primary key in the client table. I'm all…

A badly built database isn't the reason to toss out good practices.

It's actually quite obnoxious to see how bad many databases are, and this often causes a blow-back of tossing the baby out with the bathwater. It's a trap.

I'm not really sure what all the problems are, but if changing username to something else is causing a 24 shutdown, there are many deeper issues than a PK on username.

Re: SQL Keys in Depth

#153

Post by slg »

tatersolid wrote: post #136
slg wrote: post #16

Earlier quoted context omitted.

In theory yes, it solves some of the problems and introduces others. It isn't usable everywhere NEWID is usable, so if you assign UUIDS as part of a query you are out of luck. It also assumes you are generating all your UUIDs on the same machine without restarting which once again negates some of the benefits of UUIDs.

ULIDs solve these issues. Basically 48 bits of millisecond timestamp then 80 bits of randomness. The technique has been used in MSSQL apps since about 2003 (although people called them COMBs instead of ULIDs back then) https://github.com/oklog/ulid

Nice, I hadn't seen that before. It looks pretty good on first glance.

Re: SQL Keys in Depth

#154

Post by hug »

tome wrote: post #79
marcosdumay wrote: post #78

Earlier quoted context omitted.

Again, can you name any study that found a benefit with a non-laughable p-value? If nobody can, then one just has to conclude they are indistinguishable.

I'm not asking what hug concludes, I'm asking what he/she believes . What we believe and what we scientifically conclude can be quite divergent things.

Sorry for taking so long to get back to you: What I believe is that there are currently no keyboard layouts worth the hassle of switching, and to be honest I have a bit of a problem believing that there's any particular layout of 12 inch by 3 inch bit of space covered in linearly activated keys that is going to ever change that fact.

Most changes in keyboard layouts are obviated by the fact that peoples fingers are more flexible than the language they type in.

Do I believe there's better possible input methods than a 104-key keyboard? Most certainly. Have we got any yet? I don't think so.

Re: SQL Keys in Depth

#155

Post by needusername »

manigandham wrote: post #103

After a decade of large systems relying on RDBMs, we now use 64-bit integers for all primary keys with a global Hi/Lo id generation system (app reserves a range of numbers on startup to assign to records automatically). This means plenty of ID space, maintains rough numeric ordering, allows ID creation without a roundtrip for every insert, is easily portable across different databases, and produces unique IDs for eve…

> global Hi/Lo id generation system

Can you elaborate a bit? Are you using an ORM, eg. Hibernate? If so have to considered other strategies like pooled-lo, IDENTITY or recursive CTEs that offer the same benefits but make the sequence values match the database values and reduce "id loss"?

Re: SQL Keys in Depth

#156

Post by wvenable »

dizzystar wrote: post #152
wvenable wrote: post #148

Earlier quoted context omitted.

I'm working with a 3rd party database right that used more than a few natural keys and it's awful. The primary key for "users" is username so we can't rename our users to improve our company's username security policy. And that's just one of the problems. We'll be offline for a full 24 hours in April to handle changing the value (for necessary business reasons) of the natural primary key in the client table. I'm all…

A badly built database isn't the reason to toss out good practices. It's actually quite obnoxious to see how bad many databases are, and this often causes a blow-back of tossing the baby out with the bathwater. It's a trap. I'm not really sure what all the problems are, but if changing username to something else is causing a 24 shutdown, there are many deeper issues than a PK on username.

> A badly built database isn't the reason to toss out good practices.

It's a good reason to toss out bad practices like, for example, using natural keys as primary keys. Which is, almost universally, considered a bad practice. It is, in fact, the bad practice that causes the most pain in this bad database.

> if changing username to something else is causing a 24 shutdown, there are many deeper issues than a PK on username.

This is true; the software is so old it pre-dates most modern RDBMS features and that is a factor in the database design. And it contains a few decades worth of data. And the process to go through the all records and re-do every foreign and primary key value is expensive (through the app server) but is a well-tested path. None of this would be necessary, however, if they had used surrogate keys for these tables. They did use surrogate keys for other tables so it wasn't a technical limitation.

Re: SQL Keys in Depth

#157

Post by tome »

hug wrote: post #154
tome wrote: post #79

Earlier quoted context omitted.

I'm not asking what hug concludes, I'm asking what he/she believes . What we believe and what we scientifically conclude can be quite divergent things.

Sorry for taking so long to get back to you: What I believe is that there are currently no keyboard layouts worth the hassle of switching, and to be honest I have a bit of a problem believing that there's any particular layout of 12 inch by 3 inch bit of space covered in linearly activated keys that is going to ever change that fact. Most changes in keyboard layouts are obviated by the fact that peoples fingers are m…

Thanks for the response. For most people I'd agree with you. I found my RSI symptoms were significantly improved by switching to Dvorak though. I concede that is not a scientific test :)

Re: SQL Keys in Depth

#158

Post by Dylan16807 »

MaxGabriel wrote: post #147
tzahola wrote: post #38

Earlier quoted context omitted.

Ah, this brings me back to the time when I was trying to convince a product manager that it was a bad idea to "validate" email addresses with regular expressions. I failed, and the product was rolled out with the following regex: ^[a-z0-9.-]+@[a-z0-9.-]+\.[a-z]{2,4}$ I quit shortly thereafter.

I used to hold this opinion, but this Stack Exchange post https://dba.stackexchange.com/a/165923/34006 changed my mind. In short, HTML5 defines its own specification of an email address here [2], and notes: > This requirement is a willful violation of RFC 5322, which defines a syntax for e-mail addresses that is simultaneously too strict (before the "@" character), too vague (after the "@" character), and too lax (al…

That's an interesting choice of rules. When cutting down the complexity so drastically, do you really need to double the length of your regex just to enforce the rule that dashes only appear in the middle of a domain segment? This version is so much more legible:

  /^[a-zA-Z0-9.!#$%&'*+\/=?^_`{|}~-]+@[a-zA-Z0-9-]{1,63}(?:\.[a-zA-Z0-9-]{1,63})*$/
Hostnames starting or ending with dashes tend to work in the real world anyway.

Re: SQL Keys in Depth

#159

Post by Dylan16807 »

twic wrote: post #135
tzahola wrote: post #39

Earlier quoted context omitted.

>I think the point was that for a 40 people club odd are very very poor that two people would have the same name. Wrong. For example I have exactly the same name as my father and we're members of the same squash club.

I don't have exactly the same name as my father, and we are not members of the same squash club. So, in the sample so far, there's only a 50% chance of that happening. I suspect that if we enlarged the sample, it would fall much further. A single counterexample does not falsify the theory that the odds are very very poor!

We're interested in the odds of collision of people in the same club. Any data about people not sharing a club is irrelevant.

> I suspect that if we enlarged the sample, it would fall much further.

Try to back that up, maybe.

Re: SQL Keys in Depth

#160

Post by Dylan16807 »

onion2k wrote: post #83
yeukhon wrote: post #61

Earlier quoted context omitted.

> It was fine until they wanted separate accounts on the same service and it failed. When it comes to designing a system, I try my best to prioritize this way: security, privacy, and finally usability. A product with the best user-experience may have the most vulnerable design. I say this because we need to balance between security and UX. Pick some constraints which make sense to your system. If we have to accommoda…

They want a separate account for some specific reasons, I assume, so why not help them? That's the point - they didn't want different email accounts. They wanted to share one. There will always be edge cases that don't conform to your expectations of how users should be doing things, and your applications probably need to work even in those less-than-ideal circumstances. If your app is going to be used by older peopl…

How did they want to identify themselves upon using the service? Email plus name? Is it worth making login more complex for everyone else?

Or do you expect old people that share an email account to gracefully handle unique usernames? I'm pretty skeptical of that one.

And "So long as users are aware that they won't be able to recover an account" sounds like a joke. Maybe one in twenty people will truly internalize that, if you're lucky.

Post reply on HN