If you haven’t got the time to do it right, when will you find the time to do it over?
– Jeffrey J. Mayer
If there is one lesson I have learned at my current job, it is that you should always, always, take the time to do things right the first time, especially if everything else you do will be built upon it.
For example, about a year ago, I was tasked with migrating an aging access database to MySQL. The Database was horrible. There were just about no checks in place to prevent inconsistent records (Changing an ID in one place would not update other references to that ID in the remaining parts of the database), a bunch of very inefficient data types and needlessly complicated ways to store values (For example, instead of using a boolean value to indicate if a course was offered at a specific university, the column would either contain NULL (or the access equivalent) or the name of the university.
Now, someone had already imported the database layout into MySQL, so I took a look at it. I wrote down several ideas on how to improve the database, including the usage of “Foreign Keys” to preserve consistency, boolean variables to store true/false-Values, the use of IDs instead of names as primary keys, and so on. Once that was finished, I talked those proposals through with my employer and we discussed the changes. Most were accepted, some were scrapped, including the foreign keys for some reasons I cannot remember (but which seemed somewhat logical at that time).
The database has been in use for a while now, and it has come the time to add some functionality (triggers to automate boring tasks and new ways to display the data, for example). So, for the first time in about 6 months, I took another look at the database, and at the data it contained.
My initial reaction:
Some bad decision on my part, combined with bad decisions when creating the frontend, combined with the fact that the database was accessed through three different interfaces, and only one of them having any form of sanity checks, the database had deteriorated into a state which made it nigh impossible to do anything without at least three different ways of abusing MySQL in ways that would have made my databases instructor weep.
The problem? The database is in use. I cannot change it, at least not within a reasonable timeframe, and without rebuilding most of the frontend (which was created with a piece of software called “PHP Generator for MySQL”, which has some nice features but also frequently throws a hissing fit if you do not use it in exactly the way it thinks it should be used, not to mention generating code that is vulnerable to the most basic of XSS (which has been reported by me for more than 9 months, by the way)). That would be an effort of at least 20 hours, which could have been avoided by one or two hours of work improving the database structure in the beginning of the project. And I don’t have those 20 hours.
My contract with my employer will end soon, and once me and my supervisor are gone, they will have no one left who can even understand MySQL. I pity the person who will inherit this database and try to make sense of it. I am trying to document things as good as I can, but there is only so much you can do for a database that should have never existed or been put out of its misery a long time ago.
Seriously. Do things right the first time, or you will regret it in the long run.