A terrible schema from a clueless programmer
rachelbythebay.com
A terrible schema from a clueless programmer
1–10 of 493 posts
Re: A terrible schema from a clueless programmer
#2The "NoSQL" movement -- which I wisely rejected -- is also responsible for this.
Re: A terrible schema from a clueless programmer
#3Why 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?
Re: A terrible schema from a clueless programmer
#4Re: A terrible schema from a clueless programmer
#5While 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.
Re: A terrible schema from a clueless programmer
#6[deleted]
Re: A terrible schema from a clueless programmer
#7The "NoSQL" movement -- which I wisely rejected -- is also responsible for this.
All "movements" have some merit, or they wouldn't exist in the first place. Nobody starts a "movement" just because. It exists because it solves something. "NoSQL" was a solution to something. Today you can benefit from best of all worlds, and still NoSQL has a place and a use case, just like anything else that exists.
Re: A terrible schema from a clueless programmer
#8Sorry, 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 compressed out and the resulting index will be no larger and no slower than the one with foreign keys.
If you include all of the keys in the index, then it will be a covering index and all queries will hit the index only, and not the heap table.
Re: A terrible schema from a clueless programmer
#9The 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: EVERYONE goes through this, particularly if operating in a vacuum with no mentorship, guidance, or reference points. Considering that we as an industry tend to chase off anyone who makes it to the age of 35, is it any surprise that we have a giant flock of people roaming around trying anything that'll work?
Re: A terrible schema from a clueless programmer
#10While 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.
[deleted]