Considering the Database

I've just started reading Pro MySQL by Jay Pipes and Michael Kruckenberg, Apress were kind enough to send me a free copy but what with the first copy going missing in the post and being busy it's only now I've had the chance to have more than a flick through. I have of course started with chapter one, Analyzing Business Requirements, you may be thinking why a book about MySQL starts with a chapter on a subject which is more related to application design, I don't think many people would have even noticed if it had been missing and the book kicked off with chapter two Index Concept. But this raises a really important point, and one which is often missed in the development process. The database is an essential part of most IT projects, it should be kept in mind from the very start.

In my experience of projects I've worked on the database has often been an after thought. Take for example a recent project I worked on, it was a large e-commerce site built on Linux, PHP and Oracle. There was a very detailed specification with endless screen shots and mock-ups of the site design but the database design was nothing more than a simple schema diagram, apart from the odd line joining tables there was next to no information about how the database was going to function, what referential integrity was to be included, no information about indexes and having read the specification it was clear there were large parts of the database which hadn't been included. What was significant was the amount of effort which had gone into the look and feel of the site and in contrast the lack of time that had been spent on the database.

As usual I came into the project at the coding stage, far too late to be able to spend time looking at the requirements and working out the impact on the database. The database evolved organically, the database schema was used to create the base tables but these changed on a daily if not hourly basis. I did my best to add the necessary referential integrity, add indexes where appropriate but as we were into the coding phase it was all a little bit of a fire fighting exercise. Despite the fact we had a detailed specification the nitty gritty of how some of the functionality was to be achieved was missing, the search functionality for example evolved over time and it became increasingly difficult to optimize the database to deal with this.

Things came to a head when we started to stress test the site, anything over 20 concurrent users and the site crashed. We identified some bottlenecks which were addressed and this fixed the immediate problem. But when the site went live we had other problems related to periods of high usage. This meant periods of panic where we had to analyse the site and find the problems, often this involved big changes to the database which in some cases had a serious impact on the site.

The site launch was a success and the performance problems we had didn't seem to cause a negative impact in terms of visitor numbers, but it would have been far better to have taken just a quarter of the time we took fixing problems retrospectively in designing the database in the first place and the subsequent problems we had could have been avoided.*

So I'm more than happy that Jay and Michael decided to kick of Pro MySQL with "Analyzing Business Requirements". The moniker Pro seems more than justified.

*I should say I had a great time working on the project, the client was extremely happy with result and the project was well run. I feel a little guilty about using it as an example but it was a classic case of where it could have been even more successful and stress free if time had been take to consider the database right from the very start.