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

201–210 of 493 posts

Re: A terrible schema from a clueless programmer

#201

Post by daly »

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 of the "business logic" depended on the stored procedures... well, things got "delayed" for quite a while... and we ended up having a major re-design of the back end.

Fun times.

Re: A terrible schema from a clueless programmer

#202

Post by jjice »

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…

The way she kept referring to the programmer made me suspicious that that would be the ending. I personally really like that as a way of conveying the message of us all having growing pains and learning the hard way from some inefficient code.

I know I've also had these encounters (specifically in DBs), and I'm sure there are plenty more to come.

Re: A terrible schema from a clueless programmer

#204

Post by mst »

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?

Given this is a "from the start of her career" story, I'm guessing she was running similar versions of mysql to the versions I started with, and if my guess is correct then probably not.

On anything you're likely to be deploying today, just throwing a compound index at it is likely quite sufficient though.

Re: A terrible schema from a clueless programmer

#206

Post by mst »

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?

It was fine on -some- databases 20 years ago. mysql of that era, less often.

Re: A terrible schema from a clueless programmer

#207

Post by mst »

msqlqthrowaway wrote: post #40
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…

> all major databases do these days Did everyone on HN miss that the database in question was whichever version of MySQL existed in 2002?

Which I was using in production at the time, and, yeah, what she ended up doing was a much better plan for that thing.

Re: A terrible schema from a clueless programmer

#208

Post by jjice »

mmaunder wrote: post #190

Oh well played!! I was setting up to give the author a hard time about being judgemental, particularly because storing IPs or UUIDs as strings is a mistake I've seen some pretty darn good devs make. Some folks just aren't super strong on schema design and performance but are great at other things. Plus MySQL kind of rocks. Fight me. There are some interesting optimizations that Percona has written about that may impr…

> Plus MySQL kind of rocks.

Agreed. I prefer Postgres for personal projects, but MySQL is a fine database. Honestly, even a relational DB I wouldn't want to use again (DB2...) is still pretty solid to me. The relational model is pretty damn neat and SQL is a pretty solid query language.

I wonder how many people disagree with that last part in particular...

Re: A terrible schema from a clueless programmer

#209

Post by mst »

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…

I love DBD::Oracle so much I've always arranged to have other people test oracle related DBIx::Class features for me.

Writing the pre-ANSI-JOIN-support SQL dialect code was pretty good fun though.

Re: A terrible schema from a clueless programmer

#210

Post by mst »

newaccount74 wrote: post #179
hn_throwaway_99 wrote: post #131

Earlier quoted context omitted.

The reason "the details are important" here are not because of the nitty gritty around what mistakes a "novice" programmer made. They are important because the present incarnation of the author is making all the wrong diagnoses about the problems with the original implementation, despite doing it with an air of "Yes, younger me was so naive and inexperienced, and present me is savvy and wise".

I think she inadvertently made a different point, which is that even experienced developers sometimes misunderstand the problem and make mistakes. Or an even better argument: you don't need to actually understand the problem to fix it, often you accidentally fix the problem just by using a different approach.

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