everynerd.net

About 2 weeks ago I was given the opportunity to step away from Flash and Flex and get back to the basics of application development: designing a database.

For those that don’t know me, I’ve spent my entire software development career before May of this year developing custom software solutions for a wide variety of clients. Being in a small team, this rendered me the opportunities to develop through nearly all aspects of the SDLC including designing a data model.

Previously most of my experience had been using Microsoft’s SQL Server, but now this time I’d been handed the challenge of an open source solution in MySQL. A few versions ago this might have caused me some minor heart palpitations, but luckily with recent versions of the product, there are a lot of creature comforts that I can work with thanks to the InnoDB storage engine.

Stored Procedures

This topic is easily debatable, and it especially depends on the architecture and scope of the application, but I am a very large supporter of stored procedures in RDBMS. The reason for this is a few things:

1. They are compiled on the database server - meaning, an SQL string will not need to be passed every single time from the web application to the database server. This can increase performance by reducing the network load.
2. Easy to debug - running a stored procedure from MySQL Query Browser (or whatever your flavor is) is far easier than stepping through a PHP debugger or dumping variable values, and this can in turn make the problem solving step far simpler.
3. Separates web code from DB code - this is extremely important to me, and being able to leave the in-line SQL strings behind can greatly clean up and simplify an application.

Foreign Keys

It actually surprised me to uncover that foreign keys are relatively new to MySQL, and on top of that, the application that I’m in the middle of rebuilding the database for was built without any relationships between the tables at all.  There is a good possibility I’ve been spoiled by a paid product, but I had assumed data integrity was far more important than leaving out such a vital component for so long.

With these features at my disposal I felt a little more comfortable diving into MySQL Workbench and creating a working database model.  It’s one thing to build a model and improve upon an old product, but without the ability to form relationships between the tables and manage its data directly with stored procedures, it can be nightmarish.

My next task is to ensure the data migration (via stored procedure, I’ll plainly brag) has fit into the new site’s requirements and have retained all the necessary and previously assumed relationships between the entities of data.

To say this is a boring or stressful task would be doing the process a great disservice, but after spending so long developing RIAs with Flex, going back to the basics with some raw SQL and modeling an ERD, it has been a welcome change and has completely envigorated me professionally.

Here’s hoping I continue with more of the same.