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

11–20 of 493 posts

Re: A terrible schema from a clueless programmer

#11

Post by hn_throwaway_99 »

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 if a more normalized DB structure would be warranted, but from her description I would say it wasn't.

Re: A terrible schema from a clueless programmer

#12

Post by silisili »

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…

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.

Re: A terrible schema from a clueless programmer

#13

Post by nerdponx »

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…

And even if this is somehow not an ideal use of the database, it's certainly far from "terrible", and to conclude that the programmer who designed it was "clueless" is insulting to that programmer. Even if that programmer was you, years ago (as in the blog post).

Moreover, unless you can prove with experimental data that the 3rd-normal-form version of the database performs significantly better or solves some other business problem, then I would argue that refactoring it is strictly worse.

There are good reasons not to use email addresses as primary or foreign keys, but those reasons are conceptual ("business logic") and not technical.

Re: A terrible schema from a clueless programmer

#15

Post by tyingq »

Feels like you could just concatenate and hash the 4 values with MD5 and store the hash and time.

Edit: I guess concatenate with a delimiter if you're worried about false positives with the concat. But it does read like a cache of "I've seen this before". Doing it this way would be compact and indexed well. MD5 was fast in 2002, and you could just use a CRC instead if it weren't. I suppose you lose some operational visibility to what's going on.

Re: A terrible schema from a clueless programmer

#16

Post by late2part »

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

Re: A terrible schema from a clueless programmer

#18

Post by emerongi »

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

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.

Re: A terrible schema from a clueless programmer

#19

Post by philliphaydon »

silisili wrote: post #12
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…

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.
Post reply on HN