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

41–50 of 174 posts

Re: SQL Keys in Depth

#41

Post by taspeotis »

tzahola wrote: post #38
simonw wrote: post #10

"For instance, a database of hobbyist club members could include uniqueness on the two columns first_name, last_name of members. Duplicates are likely unintentional at this scale, and if necessary the constraint can be dropped. Until an actual conflict happens the key is a reasonable precaution." Absolutely do not do this. People have names that are duplicates. A situation where someone is unable to join a club becau…

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.

That regex is short by a few kilobytes [1]!

[1] http://referencesource.microsoft.com/#System.ComponentModel....

Re: SQL Keys in Depth

#43

Post by Mithaldu »

taspeotis wrote: post #41
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.

That regex is short by a few kilobytes [1]! [1] http://referencesource.microsoft.com/#System.ComponentModel....

Don't see a timestamp on your link, but i suspect this one is more correct:

https://metacpan.org/source/RJBS/Email-Valid-1.202/lib/Email...

Re: SQL Keys in Depth

#45

Post by nickjackson »

Mithaldu wrote: post #43
taspeotis wrote: post #41

Earlier quoted context omitted.

That regex is short by a few kilobytes [1]! [1] http://referencesource.microsoft.com/#System.ComponentModel....

Don't see a timestamp on your link, but i suspect this one is more correct: https://metacpan.org/source/RJBS/Email-Valid-1.202/lib/Email...

I prefer /@/

https://davidcel.is/posts/stop-validating-email-addresses-wi...

Re: SQL Keys in Depth

#46

Post by vinceguidry »

foreigner wrote: post #33

At what scale does all this stuff start to actually matter? I have an database with ~100 tables and ~500M rows driving a medium-traffic web app and various back-end systems. We use auto-incrementing integers as primary keys and try not to expose them externally. Indexes are added as necessary to enable specific queries. We don't enforce any other constraints (e.g. not-null or foreign keys) at the database level. ...…

They don't necessarily matter until you hit Twitter-size for performance purposes, but take care to pay close attention to the idea of natural vs. artificial keys, and heed his warning about turning artificial keys into natural keys.

One day you'll be integrating database systems together and you'll be glad you followed his advice. I've seen some really ugly bizdata schemas.

Re: SQL Keys in Depth

#47

Post by tzahola »

nickjackson wrote: post #45
Mithaldu wrote: post #43

Earlier quoted context omitted.

Don't see a timestamp on your link, but i suspect this one is more correct: https://metacpan.org/source/RJBS/Email-Valid-1.202/lib/Email...

I prefer /@/ https://davidcel.is/posts/stop-validating-email-addresses-wi...

This is the only sane way.

Re: SQL Keys in Depth

#48

Post by vinceguidry »

Terr_ wrote: post #19
hug wrote: post #14

Earlier quoted context omitted.

Let's be honest: If you could make these choices more natural then you would have done so by now. If you could make SQL better than it is, you already would have done that as well. In fact, if most people who are interested in SQL could make SQL better than SQL is, we wouldn't be using SQL anymore. We're not using it because we have some weird tradition we enforce. We're not using it because we like the way it looks,…

I think you're discounting network-effects and historical inertia. Even things which are functionally very simple to implement won't necessarily catch on, ex: "If you could make keyboard layouts more practical than QWERTY, then you would have done so by now."

I honestly don't think we are.

The more I learn about databases, the more I realize just how hoary that world is, teeming with verdant beasts just itching to snatch you away from your comfortable application into Cthulian madness.

SQL abstracts all of that away. Honestly we should thank the computing gods and make daily offerings that such a thing is even possible.

Re: SQL Keys in Depth

#49

Post by zaxomi »

taspeotis wrote: post #41
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.

That regex is short by a few kilobytes [1]! [1] http://referencesource.microsoft.com/#System.ComponentModel....

When I decided to learn more about regular expressions back in 2002-2003 I found a regular expression for RFC822 (with comments removed): http://www.ex-parrot.com/pdw/Mail-RFC822-Address.html

As an exercise, I deconstructed that into the pieces it was built from. Funny times :)

Re: SQL Keys in Depth

#50

Post by amarkov »

foreigner wrote: post #33

At what scale does all this stuff start to actually matter? I have an database with ~100 tables and ~500M rows driving a medium-traffic web app and various back-end systems. We use auto-incrementing integers as primary keys and try not to expose them externally. Indexes are added as necessary to enable specific queries. We don't enforce any other constraints (e.g. not-null or foreign keys) at the database level. ...…

As you can see leaking through in a few of the examples, the scenarios where it matters are the ones where non-experts are reading to and writing from the database. If a data entry clerk is just typing values into a form thinly wrapping an INSERT, you'd better make sure any input that would break implicit assumptions is rejected. If you have a legion of Bobs from marketing who "know some SQL", you can't hand-optimize every single query they try to run.

When the database is just serving a webapp you control end to end, the only part that'll really have a huge impact is making sure you can partition by the primary key effectively. Which is good, because complex indexing schemes and foreign key constraints actually scale very badly.

Post reply on HN