The "NoSQL" movement -- which I wisely rejected -- is also responsible for this.
Huh? I strongly prefer relational databases for most things but this particular example is actually begging for a simple key-value store.
A terrible schema from a clueless programmer
371–380 of 493 posts
Re: A terrible schema from a clueless programmer
#372Earlier quoted context omitted.
I recall reading a rant on another site about someone so upset they had to deal with clueless noobs at work. They then went on to list the errors that this brand new entry level employee had made when writing ... an authentication system ... I was more than a little shocked when I realized they were serious and hadn't realized the issue was sending the new entry level guy to do that job alone.
I would expect most seniors to want a set of eyes across their work doing any form of authk or z. If only to share blame :p
> authk
I've always seen authn. Where'd you pick this usage up?
Re: A terrible schema from a clueless programmer
#373Earlier quoted context omitted.
>> Mapping the string "address1@foo.bar" to a new value "id1" has no effect on the relations in your table. >How do you mean? If id1 is unique on table A, and table B has a foreign key dependency on A.id, then yeah you still have id1 in twenty locations but it's normalized in that altering the referenced table once will alter the joined value in all twenty cases. Ok, but that's not what normalization means. If you ha…
I think I’ve determined very long ago that the only normal form worth keeping in mind is the Boyce-Codd normal form. While you may technically be correct, I think there’s few people that think of anything else when talking database normalization. That said, I cannot quickly figure out if it’s actually true here.
Okay, this will be my last comment in this thread because I don't have any new way of presenting this and checking this is not a good use of my time.
This schema is also in Boyce-Codd normal form. It's normalized in every usual sense of the word. Trivially so, even. It's not a question of being "technically" correct. If you think the second schema is more normalized than the first one, you need to re-evaluate your mental model of what normalization means. That's all there is to it.
Re: A terrible schema from a clueless programmer
#374Earlier quoted context omitted.
If I've understood correctly it's the foreign key column itself that isn't indexed by default. Deleting a record then requires a table scan to ensure that the constraint holds
Example: if you delete a record from the customers table, you want an index on the foreign key in the orders table to delete the corresponding entries. This also means the performance of the delete can have the unfortunate property where the small work of deleting a single row cascades into the work of deleting many rows.
Which is why many elect to just ban deletes.
Re: A terrible schema from a clueless programmer
#375Related 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…
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.
Re: A terrible schema from a clueless programmer
#376Why 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?
Let's say the article is a critique to some system by someone that is not much better at databases. As many people also pointed out, a bad solution was replaced with a bad solution that works better for someone.
Re: A terrible schema from a clueless programmer
#377Feels 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…
Isn't that exactly what adding an index would do internally?
Depending on the RDBMS system, in most cases it does not.
Re: A terrible schema from a clueless programmer
#378The 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…
[deleted]
Re: A terrible schema from a clueless programmer
#379Why not put a hash index on the four columns? By normalising, you get an index but maybe b tree, which isn't best here for exact matching?
Re: A terrible schema from a clueless programmer
#380It's clear that she didn't know the first thing about databases. In fact its clear even her current self is pretty naive (almost certainly a few indexes on the first solution would have solved it, or if that was tried and didn't work its a giant hole in the story). She's being quite open and vulnerable here and its important to respect that.
So why was the job given to her at the time? Why wasn't there a mentor or lead or someone else to review it? Who thought it was a good idea? Even 5 minutes with an experienced DBA or senior engineer would have saved her this whole episode and given her a huge boost in learning.
She seems to have come out of it ok, but how brutal it must be for young people thrown into a bear cage like that and then finding themselves responsible for huge issues and impacts and coming away calling themselves "terrible". Of course, it was 20 years ago, but are things any better now? I hope so, but I'm not sure.