Development methodology

In the ideal world, a database project has the most chances of success when a thorough analysis and design is done. A formal specification is written - it's the blueprint for building the database. It can account from anywhere between 20 to 35% of the total project cost.

The specification lays out what is expected from the developer and the client. 

My development methodology varies from each project. I don't think you can apply any one formula for developing systems. However, without exception, I make sure enough time is spent designing the underlying relational database correctly. At the end of the day, it doesn't matter how fancy the front-end is; if the back-end database hasn't been built properly, the application will always be weak, and probably cost more to maintain.

There are many scenarios, including:

  • Scenario 1: Very tight budget

The most important thing is get something that works up and running fast. Normally the cost of doing a thorough analysis and design is beyond the total amount the customer can afford.

I use Access 2003 to prototype just the tables, their fields and their relationships. Access has a great feature called 'table sub-datasheets'. This lets you sit down with the user and input realistic data and see on screen how we can turn this data into information. Once the design is proved,  basic data entry and search screens can be developed. 

I normally include a generic Ad-Hoc Query Tool that lets you print reports to screen or Excel. Custom reports will be written if the budget allows (usually around NZ$100 per report). If not, then this tool at least let the user get the data into a familiar format where it can be massaged for printing, graphing etc.

  • Scenario 2: Requirements uncertain

Normally when the requirements are unclear, the cost (perceived by client or developer) will be unclear.

Analysis and design fixes this: it defines the boundaries of the system, and its purpose. Often, expectations will be changed.

Prototyping can help here. Often an output of the analysis and design is a prototype data model. This can be used later when developing the system, or by another developer.

  •  Scenario 3: Buying an off-the-shelf system might be better?

For doing common tasks, sure. You'd be mad to develop a new Word processor or e-mail application.

It gets harder when you are doing something different. If an off-the-shelf system can do 80% of what you want, go for it. Many people are in positions where constant change and drive for improvement means coming up with new and better ways to do things.

Analysing your current system is the best way to decide if an off-the-shelf system will work for you. Vendors and their agents will be the first to claim their system can do everything. Chances are it will have a different data model to yours. One  output of a system analysis is a data model; you can use this to get vendors to prove their system will satisfy your requirements.

  • Scenario 4: New process

Again, the most important thing is to analyse how data in the new process is going to be stored electronically. Prototyping can help - users of the system can get a feel for their data and the way it relates to the process.

Regardless of budget or any other factor, there is a common theme to my development methodology: make sure the underlying database is designed correctly. If this isn't done, the developer has to code the application to make things work. The result? A spaghetti maze of code and fudges to make the application work. It becomes unwieldy, inflexible and a nightmare to document.

  • Scenario 5: The business needs the system: nobody wants ownership

These are the ones I try to avoid !

The failure rate is high when nobody wants to own the system. The scope of the project, and it's cost, can spiral out of control. An owner is needed to make sure the scope is contained and to govern requests for enhancements. Also, someone in the business needs to take responsibility for the integrity of the data.

  • Scenario 6: I've created my own Access  database, now I'm stuck

Access can be a bit of a doubled-edge sword. On the one hand, it is a fairly easy tool to learn and to create databases with. However, knowing Access gets you only half-way there : you need to understand relational database theory to come up with a good database design.

Often novices will treat Access tables a bit like the Excel spreadsheet they are trying to convert. A one table database is easy to create - it is the wrong design however, if you are recording customers and their purchases, for example.

Often the whole system needs to be re-written. In most cases, however, existing data can be preserved and loaded into the new relational structure.

There are other aspects too: designing for multiple users, security, error handling, etc.