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

21–30 of 493 posts

Re: A terrible schema from a clueless programmer

#21

Post by kasey_junk »

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…

She doesn’t mention the write characteristics of the system but she implies that it was pretty write heavy.

In that case it’s not obvious to me that putting a key prefix index on every column is the correct thing to do, because that will get toilsome very quick in high write loads.

Given that she herself wrote the before and after systems 20 years ago and that the story was more about everyone having dumb mistakes when they are inexperienced perhaps we should assume the best about her second design?

Re: A terrible schema from a clueless programmer

#22

Post by kasey_junk »

philliphaydon wrote: post #19
silisili wrote: post #12

Earlier quoted context omitted.

I think it depends a lot on the data. If those values like IP, From, To, etc keep repeating, you save a lot of space by normalizing it as she did. But strictly from a performance aspect, I agree it's a wash if both were done correctly.

Space is cheap now tho. Better to duplicate some data and avoid a bunch of joins than to worry about saving a few gb of space.

This system was written in 2002.

Re: A terrible schema from a clueless programmer

#23

Post by jerjerjer »

tomsmeding wrote: post #3

Why exactly would it be so bad to just put a suitable index on the table containing strings? The time complexity of the resulting search would be the same, so I assume there will be some constant factor slowdowns. Is it that indices over string fields are stored inefficiently on disk? (If so, can that not be fixed in the db engine directly?) Or is this fine today but wasn't fine 15 years ago?

Yeah, while this is not optimal (ip should be converted into integer, time should be ts), the table would be small (as old entries could be safely deleted). The only real issue is the lack of indices.

Also is helo field even needed?

Re: A terrible schema from a clueless programmer

#24

Post by Grimm1 »

Heh at the end I think I may have been called out for my harsh response to the post being referenced at the start here on HN since it seemed to raise some hackles. Borrowing from other social media terminology here, "subtweeted" but by a blog post, not sure how I feel about that.

Honestly it almost makes me feel compelled to start writing more for the industry but to be honest, my opinion is most ideas are not great, or not novel, up to and including my own. So writing about them seems likely not to be beneficial. I guess you could argue I could let the industry be the judge of that though.

Re: A terrible schema from a clueless programmer

#25

Post by bigbillheck »

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

> 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)

Re: A terrible schema from a clueless programmer

#26

Post by junon »

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.

Re: A terrible schema from a clueless programmer

#27

Post by late2part »

emerongi wrote: post #18
late2part wrote: post #16

Earlier quoted context omitted.

Deleted

To a degree, this is true though. An engineer's salary is a huge expense for a startup, it's straight up cheaper to spend more on cloud and have the engineer work on the product itself. Once you're bigger, you can optimize, of course.

Deleted

Re: A terrible schema from a clueless programmer

#28

Post by gadrev »

What I got from this post is a headache after reviewing the wikipedia entries on 3NF (all all of it's dependencies: 1NF, 2NF, also 3.5NF). Man the language is dense.

j/k, always nice to review some of this "basic" stuff (that you barely use in everyday work but it's good to have it in the back of your head when changing something in the db schema)

Re: A terrible schema from a clueless programmer

#29

Post by late2part »

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)

When do the rest of the folks figure it out?

Re: A terrible schema from a clueless programmer

#30

Post by gfodor »

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…

The point is she was being hyper critical of her past schema to make a larger point.
Post reply on HN