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

221–230 of 493 posts

Re: A terrible schema from a clueless programmer

#221

Post by jeremyjh »

dehrmann wrote: post #167
mewpmewp2 wrote: post #78

Earlier quoted context omitted.

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

Yeah, I'm not clear on how multiple tables fixed it other than allowing you to scan through the main table faster. Multiple tables could be a big win if long email addresses are causing you a data size problem, but for this use case, I think a hash of the email would suffice.

Well she did say she had indexes on the new table. It could have been fixed with an index on the previous table, but a new table with indexes also fixed it.

Re: A terrible schema from a clueless programmer

#223

Post by hinkley »

daly wrote: post #201

We had a test database that contained 1 record. Nobody paid much attention since the focus was on the problem, not the database. The database included several newly developed "stored procedures". Time elapsed... and it was nearing the time to ship the code. So we tried to populate the database. But we could not. It turned out that the stored procedures would only allow a single record in the database. Since a portion…

Why do so many of us “forget” to populate the database with representative quantities of data until after we’ve baked in all of our poor design decisions?

Re: A terrible schema from a clueless programmer

#224

Post by alksjoflkajsom »

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.

You might be right (I don't actually know what a HELO string is, I don't know anything about SMTP :). I was just going off how the author presented the data, as a tuple of four completely independent things.

Of course the main point still stands, that the two schemas are exactly as normalized as each other.

Edit: rereading the original post, the author mentions that "they forged...the HELO"--so perhaps there was indeed no relationship between HELO and IP here. But again, I don't know anything about SMTP, so this could be wrong.

Re: A terrible schema from a clueless programmer

#225

Post by jeremyjh »

avl999 wrote: post #178

The responses in this thread are classic pedantic HN with the actual point whoooshing over so many people's head.

I think it actually makes quite a bit of a difference if 20 years later the author still does not understand the actual problem and solution she is using as an example. It actually does undermine her argument that people make mistakes, learn from them and grow etc if the lack of growth is still in evidence 20 years later in the singular example under consideration.

Re: A terrible schema from a clueless programmer

#226

Post by [deleted] »

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…

[deleted]

Re: A terrible schema from a clueless programmer

#227

Post by mst »

jeremyjh wrote: post #218
mst wrote: post #210

Earlier quoted context omitted.

I would argue instead that this comment thread is making the point that people forget that things that work now wouldn't've worked then and design decisions have to be made based on the database engine you're running in production.

MySQL could index strings in 2002. It would have worked fine.

"mysql could index strings" and "using a compound index over four varchar columns would've worked out well" are significantly different propositions.

Re: A terrible schema from a clueless programmer

#228

Post by WJW »

Philip-J-Fry wrote: post #89
maddynator wrote: post #32

Earlier quoted context omitted.

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…

>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 Are you implying that isn't the case today? Thousands of (big) companies are still like that and will continue to be like that. I write my own SQL, design tables and stuff, submit it for a review by someone 10x more qualified than myself, and at the end of the day I'll get a m…

If you have one, more power to you. I'm currently making a good living as a freelance DBA/SRE for startups. With a team of 3-5 devs, some of which frontend and/or mobile devs, proper database knowledge is definitely thin on the ground in some places.

Re: A terrible schema from a clueless programmer

#229

Post by bizzarojerry »

avl999 wrote: post #178

The responses in this thread are classic pedantic HN with the actual point whoooshing over so many people's head.

No I'd say the responses in this thread are classic pedantic HN with users like you taking the high moral ground and looking down on people who actually are trying to address the technical issues with a post in a technical forum.

Re: A terrible schema from a clueless programmer

#230

Post by hinkley »

wdr1 wrote: post #109

Related to database indexes, but not the post: a busted database index brought down ticket sales of the 2008 Olympics Games. This was the first time regular people could go buy tickets for events & they had been lining up overnight at Bank of China locations through the country. We were down for over a day before we called it off. Apparently this led to minor upheaval at several locations in Beijing & riot police wer…

The one that always got us is much more mundane. Deleting a row requiring an index in every table with a FK to avoid tables scans.

Near as I can tell, we assume there is some bit of magic built into the foreign key concept that handles this for us, but that is not the case.

Post reply on HN