2011
04.11

SQL Poetry

The other day, my boss sends me a couple of .xlsx files, and says, you’ve got to import this data to the database of this site..

This should be pretty easy I thought… were in Portugal we have a say, that goes something like this, “A donkey died from thinking” ( this translation is awful, but I hope you get the point ).

I get the files from the server, dump the database to the local development server, change the configuration, and try to run the site..

Guess what, the site is using the Macromedia/Adobe KT library’s, which doesn’t work in the current version of PHP and Apache.. awesome! I managed to run the backoffice, I hacked and hacked away, and at least it’s working..

Time to study the database, fire up MySQL Workbench, to a Reverse Engineer on the database, and my jaw hits the floor:

All Tables

Not even one relation is established between the 98 tables!!!

But wait, it get’s better..after the initial shock, and after half a pack of cigarettes, I start to separate the tables into logical unit’s, trying to understand the great scheme of things:

Logic Group1

The big table on the left, is the main table, the 4 smaller ones, right next to it, are 4 relationships, and the other two, are 2 secondary tables:

If you are looking with sufficient attention you are going to notice 2 big errors:

Main Table

First there is no primary key on this table, so guess what, every query made to this table is going to result on a full table scan, now how awesome is that?

Second, the column that is supposed to be the primary key is a VARCHAR(255), which is a value inserted by the user, so this means that we have a lot of cool things on that column, like: ‘123123’ or ‘12319_123’ or ‘1231231 423’, I’m surprised there isn’t more garbage on that column, but maybe the system was just lucky..

After another half pack of cigarettes I’m ready to grab the bull by the horns, and start dealing with all the relations between the various table.. if you kept paying attention, you noticed something very wrong with the pictures I posted above:

Relations

The red ellipses are the connections between the various tables, look how cool, the main table as the column defined as a VARCHAR(255), but some of the relation tables have that column defined as a INT(11), my eyes turned, and rolled, and I was just a little step from throwing my computer out of the window…

Went downstairs, and pushed another pack of cigarettes down my throat to calm the nerves down, and trying not to present my resignation letter to my boss.

I don’t even have anything else to say… what can I say when I’m presented with this?

How is this level of imcompetence possible?

PS: I’m sure that I’m going to find more of this things in the future.. the only thing I don’t know, if I’m going to survive this kind of things..