melfneerg.com
- 'cos life is like that
[Blog]
[Pictures]
[Links]
[About]
About the Author
Tudor Davies
Tudor is a techie turned manager who fights like mad to keep his tech skills honed and relevant. Everything from web hosting, networking, *nix and the like. Constantly developing and co-ordinating with others to make the web a better (and easier to use) place.
Tuesday, 11th Jun 2013
Posted @ 10:30
We have a portal system at our office which was programmed some time ago and we are slowly going around fixing some of its problems.
Todays lesson - how not to design a database table.
SQL Server: mySQL
Scripting language: PHP
Requirement: Log particular information about who did database updates.
Data: who, when, what
You would think that would be simple enough, yes?
Apparently not.. Here is the table definition:
CREATE TABLE `logs` (`id` int(10) NOT NULL AUTO_INCREMENT, `user` text NOT NULL, `date` text NOT NULL, `time` text NOT NULL, `message` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
and here is a sample piece of data:(140077, 'someone', '18/10/2011', '1318927714', 'UPDATE qrel SET qty='12', ftype='0', bprice='11.60', sprice='13.80' WHERE id='9597'')
Erm, erm! What? I mean seriously WTF?!? Where to start?
1 - Our usernames are only 8 characters long, so "text" is a bit of an overkill. Ideally, it would be INT that linked to the id of the user in another table, but I really dont think that was though of at any point.
2 - A column called "date" - defined as "text". Fairly sure that could have been a column defined as "date".
3 - A column called "time" - again defined as "text". Fairly sure that could have been a column defined as "time".
But it gets worse, the "time" field is actually storing unixtime! So it could have been 1 column defined as "datetime" but no - lets frig it all in the code and store it as ruddy text!
And don't even get me started on indexes. On the page to view the logs, there are 2 filter field - who and when. Neither of which are indexed (and if they were they would have to be FULLTEXT under the existing schema)!!!
I havent even looked at the code yet, cos I'm getting a little scared.
I despair sometimes, I really do :)
[ 1 comments : Add | Read ]
layout and initial css based on the
Qtractor page