A terrible schema from a clueless programmer • Hacker News Board

Re: A terrible schema from a clueless programmer

#61

Post by loeg » Sun, Nov 07, 2021, 4:11 PM UTC

havkd wrote: post #53

hn_throwaway_99 wrote: post #11

This post is bizarre, precisely because there is nothing particularly wrong about the original schema, and the author seems to believe that the problem is that the column values were stored as strings, or that the schema wasn't in "third normal form". Which is nonsense. The problem with the original DB design is that the appropriate columns weren't indexed. I don't know enough about the problem space to really know i…

Is this correct? Would indexing the columns instead of moving the values to another table lead to the same increase in performance?

The same O(N) -> O(log N) improvement for queries, yes. The constant factor on the separate tables might be better. It’s also a more complicated design.

Re: A terrible schema from a clueless programmer

#62

Post by kofejnik » Sun, Nov 07, 2021, 4:12 PM UTC

mwcampbell wrote: post #17

globular-toast wrote: post #14

> That's right, I was that clueless newbie Questionable use of past tense here.

Why? She's clearly not a clueless newbie now.

Right, not a newbie anymore

Re: A terrible schema from a clueless programmer

#63

Post by havkd » Sun, Nov 07, 2021, 4:12 PM UTC

taeric wrote: post #59

havkd wrote: post #53

Earlier quoted context omitted.

Is this correct? Would indexing the columns instead of moving the values to another table lead to the same increase in performance?

An index is often easily viewed as another table. So, should. (Some subtleties on projected values and such, but the point stands.)

Then this post is very misleading.

Re: A terrible schema from a clueless programmer

#64

Post by adfgaertyqer » Sun, Nov 07, 2021, 4:13 PM UTC

havkd wrote: post #53

hn_throwaway_99 wrote: post #11

This post is bizarre, precisely because there is nothing particularly wrong about the original schema, and the author seems to believe that the problem is that the column values were stored as strings, or that the schema wasn't in "third normal form". Which is nonsense. The problem with the original DB design is that the appropriate columns weren't indexed. I don't know enough about the problem space to really know i…

Is this correct? Would indexing the columns instead of moving the values to another table lead to the same increase in performance?

Sure would! I think it would be marginally better, in fact, because you would just need to look at the index rather than five tables. Access would be more local.

Re: A terrible schema from a clueless programmer

#65

Post by crispyambulance » Sun, Nov 07, 2021, 4:13 PM UTC

kofejnik wrote: post #57

IMO she’s still pretty clueless (sorry!), but over the years got better at self-promotion So the real lesson is to be very careful as to who you listen to

She has never been "clueless" and has written insightfully for years.

Re: A terrible schema from a clueless programmer

#66

Post by alisonkisk » Sun, Nov 07, 2021, 4:14 PM UTC

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?

That canard isn't true.

Re: A terrible schema from a clueless programmer

#67

Post by kofejnik » Sun, Nov 07, 2021, 4:15 PM UTC

The initial design was fine and btw it did not violate 3NF, actually (if you add auto increment pk). A single composite index would’ve most likely solved all problems

Re: A terrible schema from a clueless programmer

#68

Post by cuu508 » Sun, Nov 07, 2021, 4:15 PM UTC

> The observation was that we could probably store the IP address, HELO string, FROM address and TO address in a table, and send back a 4xx "temporary failure" error the first time we saw that particular tuple (or "quad"). A real mail server which did SMTP properly would retry at some point, typically 15 minutes to an hour later. If it did retry and enough time had elapsed, we would allow it through.

I've run into this form of greylisting, it's quite annoying. My service sends one-time login links and authorization codes that expire in 15 minutes. If the email gets delayed, the user can just try again, right? Except I'm using AWS SES, so the next email may very well come from a different address and will get delayed again.

Re: A terrible schema from a clueless programmer

#69

Post by pgeorgi » Sun, Nov 07, 2021, 4:16 PM UTC

junon wrote: post #26

Err. Wat. The original schema was (mostly) fine. It had no indexes. The second schema looks like it tried to work around lack of database optimization features. It's in no way "better" from a data design standpoint. A database with good string index support isn't doing string comparisons to find selection candidates - at least, not initially. What a bizarrely confident article.

> lack of database optimization features

That's a perfection description of mysql as of 2002.

Re: A terrible schema from a clueless programmer

#70

Post by matsemann » Sun, Nov 07, 2021, 4:17 PM UTC

> The first time you encounter something, you're probably going to make some mistakes. There's a post going around tonight about how someone forgot to put an index on some database thing and wound up doing full table scans (or something like that).

Which post is referenced here?

I also believe this to be a tooling issue. It's often opaque what ends up being run after I've done something in some framework (java jpa, django queries whatever). How many queries (is it n+1 issues at bay?), how the queries will behave etc. Locally with little data everything is fine, until it blows up in production. But you may not even notice it blowing up in production, because that relies on someone having instrumented the db and push logs+alarms somewhere. So it's easy to remain clueless.