A terrible schema from a clueless programmer • Hacker News Board

Re: A terrible schema from a clueless programmer

#51

Post by ccleve » Sun, Nov 07, 2021, 4:04 PM UTC

late2part wrote: post #16

ccleve wrote: post #8

Sorry, no. The original schema was correct, and the new one is a mistake. The reason is that the new schema adds a great deal of needless complexity, requires the overhead of foreign keys, and makes it a hassle to change things later. It's better to stick the the original design and add a unique index with key prefix compression , which all major databases do these days. This means that the leading values gets compre…

Deleted

If you're suggesting that we should double the size of the hardware rather than add a single index, then I respectfully disagree.

But your point is well-taken. Hardware is cheap.

Re: A terrible schema from a clueless programmer

#52

Post by kodt » Sun, Nov 07, 2021, 4:06 PM UTC

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.

Re: A terrible schema from a clueless programmer

#53

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

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?

Re: A terrible schema from a clueless programmer

#54

Post by havocsupreme » Sun, Nov 07, 2021, 4:08 PM UTC

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

Re: A terrible schema from a clueless programmer

#55

Post by lifeisstillgood » Sun, Nov 07, 2021, 4:08 PM UTC

The one thing I would add here is that the devlead (or equivalent - there always is someone) should have added at least one test. Not a unit test or 99% co drags test but a "does it do what we want" test - a golden path test.

I came across something like this with an apprentice recently. I let the lack of a repeatable test pass because speed / time / crunch etc and the obvious how can that possibly fail code of course failed. The most basic test would have caught it in the same way some basic performance testing would have helped rachel however many years back.

It's hard this stuff :-)

Re: A terrible schema from a clueless programmer

#56

Post by carapace » Sun, Nov 07, 2021, 4:08 PM UTC

welder wrote: post #9

The ending is the most important part. > Now, what do you suppose happened to that clueless programmer who didn't know anything about foreign key relationships? > Well, that's easy. She just wrote this post for you. That's right, I was that clueless newbie who came up with a completely ridiculous abuse of a SQL database that was slow, bloated, and obviously wrong at a glance to anyone who had a clue. > My point is: E…

(It seems a lot of folks are getting nerd-sniped by the set-up and missing the moral of the story, eh?)

I don't know the actual numbers, but it's been pointed out that at any given time something like half of all programmers have been doing it less than five years, for decades now.

That, plus the strident ignorance of past art and practice, seem to me to bring on a lot of issues.

Re: A terrible schema from a clueless programmer

#57

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

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

Re: A terrible schema from a clueless programmer

#58

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

welder wrote: post #9

The ending is the most important part. > Now, what do you suppose happened to that clueless programmer who didn't know anything about foreign key relationships? > Well, that's easy. She just wrote this post for you. That's right, I was that clueless newbie who came up with a completely ridiculous abuse of a SQL database that was slow, bloated, and obviously wrong at a glance to anyone who had a clue. > My point is: E…

Yep. Folks are getting lost in the weeds discussing indexing of database tables. That's _totally_ beside the point here.

The thing is, the first implementation was a perfectly fine "straight line" approach to solve the problem at hand. One table, a few columns, computers are pretty fast at searching for stuff... why not? In many scenarios, one would never see a problem with that schema.

Unfortunately, "operating in a vacuum with no mentorship, guidance, or reference points", is normal for many folks. She's talking about the trenches of SV, it's even worse outside of that where the only help you might get is smug smackdowns on stackoverflow (or worse, the DBA stackexchange) for daring to ask about such a "basic problem".

Re: A terrible schema from a clueless programmer

#59

Post by taeric » Sun, Nov 07, 2021, 4:10 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?

An index is often easily viewed as another table. So, should.

(Some subtleties on projected values and such, but the point stands.)

Re: A terrible schema from a clueless programmer

#60

Post by rustc » Sun, Nov 07, 2021, 4:10 PM UTC

bigbillheck wrote: post #25

late2part wrote: post #16

Earlier quoted context omitted.

Deleted

> compute is cheap There was a whole thread yesterday about how a dude found out that it isn't: https://briananglin.me/posts/spending-5k-to-learn-how-databa... (also mentioned in the RbtB post)

Depends on the provider. I've checked the pricing of 4 top relational database cloud vendors (Google, Amazon, Azure, IBM) and they all charge for the number of CPU cores and RAM you buy, not by the number of rows scanned like PlanetScale did in the post you refer to. They'll be more expensive than buying your own server but not nearly what PlanetScale charges for full table scan queries.