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

A terrible schema from a clueless programmer

rachelbythebay.com

Post reply on HN
Print view View original

361–370 of 493 posts

Re: A terrible schema from a clueless programmer

#361

Post by ummonk »

Lewton wrote: post #104
darkwater wrote: post #48

I'd really love to be snarky here but I'll try to be polite: all those comments about the example situation are missing the whole point of the post. And it really worries me that there is a good chunk of the tech workers that just ignores the real meaning of something and just nitpick about stupid implementation details. The post is about managing rookie errors, being empathetic and also warn the ageism that pervades…

I’m happy the hn comments are nitpicking, I’m not particularly well versed in database schemas but while reading it I was going “???????” and it’s good to know I’m not going crazy

Yeah I was confused because the new design looked like an obvious anti-pattern to me, in contrast to adding an index to the original table. And then I was wondering whether I had a completely false understanding of what database normalization means (especially since this is the first time I’d heard of 3NF).

Re: A terrible schema from a clueless programmer

#362

Post by ummonk »

kodt wrote: post #52
stickfigure wrote: post #44

Assuming this needs to be optimized for massive scale, just hash the values to a single indexed field. And use something other than an RDBMS. Put the hash in Redis and expire the key; your code simply does an existence check for the hash. You could probably handle gmail with a big enough cluster. That super-normalized schema looks terrible.

I too recommend using Redis in 2002.

“Hash it and store in BerkeleyDB” would have been a ridiculously easy solution at the time.

Re: A terrible schema from a clueless programmer

#363

Post by ummonk »

chrischapman wrote: post #49

I think the 'terrible schema' thing is a secondary issue. The important take away for me was this: > Considering that we as an industry tend to chase off anyone who makes it to the age of 35, is it any surprise that we have a giant flock of people roaming around trying anything that'll work?

Maybe I’ll change my mind in 5 years, but I have a hard time believing engineers over the age of 35 get chased off. What actually seems to be the case is that the field skews young because it’s rapidly growing, and older engineers choose to retire early because they can afford to.

Which results in the same problems but the root cause is different.

Re: A terrible schema from a clueless programmer

#365

Post by ummonk »

hermitwriter wrote: post #340

I wonder how many people here are throwing stones while living in glass houses.

I’m glad people are critiquing the technical parts of the post because it had me questioning everything I know about databases, which really isn’t a whole lot.

Re: A terrible schema from a clueless programmer

#366

Post by [deleted] »

nlitened wrote: post #279

> The rub is that instead of just being slow, it also cost a fair amount of money because this crazy vendor system charged by the row or somesuch. So, by scanning the whole table, they touched all of those rows, and oh hey, massive amounts of money just set ablaze! Why _the hell_ is nobody mentioning that using a database that charges per row touched is absolute insanity? When has it become so normal that nobody ment…

[deleted]

Re: A terrible schema from a clueless programmer

#367

Post by ajnin »

twic wrote: post #211
alksjoflkajsom wrote: post #191

People in the comments are getting (rightfully) outraged about the poor understanding of indexing, but I'm a little surprised that everyone here doesn't seem to understand normalization either. The original schema is perfectly normalized and is already in 3NF: none of the columns shown has a dependence on any of the other columns outside of the primary key (in other words, if you knew eg the values of the ip, helo, a…

This is very slightly not quite true, because the HELO string and the remote address should go hand-in-hand.

Only if the SMTP client is following the RFC but being spammers they probably sent anything but the actual domain they sent from.

Re: A terrible schema from a clueless programmer

#368

Post by Aeolun »

alksjoflkajsom wrote: post #354
noduerme wrote: post #317

Earlier quoted context omitted.

>>> Mapping the string "address1@foo.bar" to a new value "id1" has no effect on the relations in your table. How do you mean? If id1 is unique on table A, and table B has a foreign key dependency on A.id, then yeah you still have id1 in twenty locations but it's normalized in that altering the referenced table once will alter the joined value in all twenty cases. This might not be important in the spam-graylisting us…

>> Mapping the string "address1@foo.bar" to a new value "id1" has no effect on the relations in your table. >How do you mean? If id1 is unique on table A, and table B has a foreign key dependency on A.id, then yeah you still have id1 in twenty locations but it's normalized in that altering the referenced table once will alter the joined value in all twenty cases. Ok, but that's not what normalization means. If you ha…

I think I’ve determined very long ago that the only normal form worth keeping in mind is the Boyce-Codd normal form.

While you may technically be correct, I think there’s few people that think of anything else when talking database normalization.

That said, I cannot quickly figure out if it’s actually true here.

Re: A terrible schema from a clueless programmer

#369

Post by ummonk »

havocsupreme wrote: post #54

I’m pretty sure all the people who are criticizing the database design haven’t read the ending. The article isn’t about the schema, it’s about helping those entry level programmers

The problem is that the technical narrative in the post contributes to imposter syndrome and gaslights entry level programmers into thinking they’re doing database design wrong.

Re: A terrible schema from a clueless programmer

#370

Post by noduerme »

ericbarrett wrote: post #345
noduerme wrote: post #307

Earlier quoted context omitted.

To me it seems that spreading it over four tables would lead to a lot more potential read locks while the big combined table is waiting for a join on each of the others, and some process is trying to insert on the others and link them to the main. This is assuming they were using foreign keys and the main table 4-column index was unique.

Old best practice for InnoDB performance was actually to never use foreign keys because of these locking issues. Not sure if that's the case in 2021.

InnoDB uses row-level locking, and foreign keys are (usually) a great feature to ensure data integrity. But using multiple foreign keys from tables `a`,`b` as a composite index for table `x` can cause deadlock if both are being updated in rapid succession, because an update on `a` gets a lock on `x`, which needs a read lock on `b` which is waiting for the lock from `a` to be released. I try to never structure multiple foreign keys as a composite index.
Post reply on HN