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

71–80 of 493 posts

Re: A terrible schema from a clueless programmer

#71

Post by zhoujianfu »

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…

Phew.. I was reading this post thinking “is basically every table I’ve ever made wrong?!”

Re: A terrible schema from a clueless programmer

#72

Post by adfgaertyqer »

maddynator wrote: post #32
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…

One thing that worth taking into consideration is that this happened in 2002. When the databases were not in cloud, the ops was done by dba’s and key prefix compression thats omnipresent today was likely not that common or potentially not even implemented/available. But i don’t think the point of the post is whats right/wrong way of doing it. The point as mentioned by few here is that programmers makes mistakes. They…

You don't really need compression. Rows only need to persist for about an hour. The table can't be more than a few MiB.

We can debate the Correct Implementation all day long. The fact of the matter is that adding any index to the original table, even the wrong index, would lead to a massive speedup. We can debate 2x or 5x speedups from compression or from choosing a different schema or a different index, but we get 10,000x from adding any index at all.

Re: A terrible schema from a clueless programmer

#73

Post by loeg »

cuu508 wrote: post #68

> 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 th…

You could have your codes expire after an hour or day instead?

Re: A terrible schema from a clueless programmer

#75

Post by [deleted] »

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…

[deleted]

Re: A terrible schema from a clueless programmer

#76

Post by rustc »

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?

Moving the data into another table would still require indexes: one on the original table's column (which now stores the new id) and one on the new table's primary key.

In most cases I'd expect just adding an index to the original table to be more efficient, but it depends on the type of the original column and if some data could be de-duplicated by the normalization.

Re: A terrible schema from a clueless programmer

#77

Post by loeg »

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

It would be more effective at making that point if it didn’t have the confused and arguably incorrect section in the middle. If as a writer, the audience misses your point, maybe it’s a problem with your writing rather than the audience.

Re: A terrible schema from a clueless programmer

#78

Post by mewpmewp2 »

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…

I think if anything, all of it could've been put into a single indexed column since the query was AND ... AND ... not OR.

So you could've had a indexed column of "fingerprint", like

ip1_blahblah_evil@spammer.somewhere_victim1@our.domain

And indexed this, with only single WHERE in the query.

I don't understand at all how multiple tables thing would help compared to indices, and the whole post seemed kind of crazy to me for that reason. In fact if I had to guess multiple tables would've performed worse.

That is if I'm understanding the problem correctly at all.

Re: A terrible schema from a clueless programmer

#80

Post by bitexploder »

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…

Yeah… technically that’s (the solution) normalization, but really not a bad design originally. If you never want a particular atom of data repeated, yes you have to normalize each column, and that is efficient in the long run for storage size, but in more normal data storage that still means a lot of joins and you still want indices. On a modern SSD you could use that one table, throw on some indices as you suggest and not notice it again until you hit millions of rows.

Anything you are doing conditional logic or joins on in SQL usually needs some sort of index.

Post reply on HN