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

421–430 of 493 posts

Re: A terrible schema from a clueless programmer

#421

Post by evilotto »

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…

Two engineers I interact with on a regular basis:

1. Me from six months ago. Clueless about a lot of stuff, made some odd technical choices for obscure reasons, left a lot of messes for me to deal with.

2. Me six months from now. Anything I don't have time for now, I can leave for him. If I was being considerate I'd write better docs for him to work with, but I don't have the time for that either.

Re: A terrible schema from a clueless programmer

#423

Post by rk06 »

webstrand wrote: post #5

While the normalized version is more compact and doesn't store redundant data, it _also_ needs an index on the four columns or it'll have to check every row in the table. A similar index added to the original denormalized table would have given comparable query performance. The table schema isn't terrible, it's just not great. A good first-pass to be optimized when it's discovered to be overly large.

that is not true. the sub tables guarantee that value is unique in the entire column.

while main table did not have any such guarantees, so, there will be a lot of indexing and duplication in atleast 3 (of 4 sub tables)

Moreover, indexing would be much faster and efficient as there are only unique values in sub tables

Re: A terrible schema from a clueless programmer

#425

Post by int_19h »

rk06 wrote: post #423
webstrand wrote: post #5

While the normalized version is more compact and doesn't store redundant data, it _also_ needs an index on the four columns or it'll have to check every row in the table. A similar index added to the original denormalized table would have given comparable query performance. The table schema isn't terrible, it's just not great. A good first-pass to be optimized when it's discovered to be overly large.

that is not true. the sub tables guarantee that value is unique in the entire column. while main table did not have any such guarantees, so, there will be a lot of indexing and duplication in atleast 3 (of 4 sub tables) Moreover, indexing would be much faster and efficient as there are only unique values in sub tables

Why wouldn't it have such guarantees, if the indices in question are defined as UNIQUE?

Re: A terrible schema from a clueless programmer

#426

Post by int_19h »

Jolter wrote: post #188
mceachen wrote: post #159

Actually, the Correct Answer is a bloom filter. (And, yes, we had math in the early 2000s.) Snark aside, I'm frustrated for the author. Her completely-reasonable schema wasn't "terrible" (even in archaic MySQL)—it just needed an index. There's always more than one way to do something. It's a folly of the less experienced to think that there's only One Correct Way, and it discourages teammates when there's a threat of…

To be fair, the “correct way” to do databases at that time was to use third normal form. Putting indices on string columns would have been considered a hack, much like using MySQL was.

3NF is largely orthogonal to indices on string columns, though. If you need to look something up by string fast, there's no avoiding an index of strings somewhere.

Re: A terrible schema from a clueless programmer

#427

Post by ummonk »

ok123456 wrote: post #383
ummonk wrote: post #353

Earlier quoted context omitted.

“Periodically” here would have to be quite frequent, as you have to rebuild the database before the next retry.

A couple times a day at most?

From the post, you'd have to be rebuilding it every 15 minutes:

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

Re: A terrible schema from a clueless programmer

#428

Post by superjan »

AdrianB1 wrote: post #375
superjan wrote: post #326

Earlier quoted context omitted.

At my previous job, a particular database was designed using ascii strings fields for a particular field rather than unicode. If you then query with a string in unicode format, the database decided that the comparison should be done in unicode. The only way was to table scan and convert all ascii fields to unicode on the fly. It was found only in production. Given that you mention China in your story, did GB 18030 ha…

That sounds like newbie developers wrote that and no DBAs were involved in checking the code, not a database design problem, you are throwing blame without knowing enough about the matter. If your string fit in a varchar, it makes absolutely no sense to change it to nvarchar because your query has unicode as input.

Most, but not all of the team were young, and the DB schema was designed by themselves. I did not intend to throw blame. It is more the kind of situation where a sensible decision (at first glance) creates a footgun down the road.

Re: A terrible schema from a clueless programmer

#429

Post by raxxorrax »

> this crazy vendor system charged by the row or somesuch

I don't really understand the text, but from what I could gather this is far worse than a forgotten index.

You should set them in most cases, but it doesn't really reduce lookup speed if you don't also add foreign keys.

Re: A terrible schema from a clueless programmer

#430

Post by goto11 »

damagednoob wrote: post #261
crispyambulance wrote: post #58

Earlier quoted context omitted.

Yep. Folks are getting lost in the weeds discussing indexing of database tables. That's _totally_ beside the point here. The thing is, the first implementation was a perfectly fine "straight line" approach to solve the problem at hand. One table, a few columns, computers are pretty fast at searching for stuff... why not? In many scenarios, one would never see a problem with that schema. Unfortunately, "operating in a…

Yes and no and this post highlights a subtle issue with mentorship (which I think is important): Technology does not stand still. What was true in 2002, might not be true today. While adopting the naïve approach was detrimental back then, today databases recognise that this happens and provide easy workarounds to get you out of trouble that didn't exist back then. I've experienced this just by switching languages. C#…

Totally off topic I know, but what is the concern with String.Concat ?
Post reply on HN