ADUG Melbourne Exercise:

Basic Database Application Architecture

Introduction

While flexibility in approach is a requirement for contract programmers, we all have our professional toolbox of tricks, our programming patterns, determined by the requirements we have had to satisfy, our programming mentors and probably our own personalities. Some of us love the new, while others, including me, tend more to follow the old ruts.

The idea of this exercise is to contrast several approaches to the same set of requirements. Which takes longest? Which code is most maintainable? Which produces the best result? The problem in designing this exercise was to develop a set of requirements which could be satisfied without an undue time commitment. I'm not completely sure that I have succeeded, but we'll see.

The Requirements

The task is to write a desktop 'Todo list' application which sits on a relational database. This has the advantage of being not only simple but also meaningful, since we all understand todo lists. And while their are obvious alternatives to a relational database for this little app, in the real world the majority of us have to talk to one of these. Not including Roger, of course.

The specific application requirements are presented in the form of:

1. A user interface, described in the help file ToDoList.chm. Note the following:

• Take this as a definition of functionality. Duplicate it if this is sensible, but if the components you are using provide for similar functionality but presented differently then use that.
• Do not spend time trying to improve on the user interface: that is not the point of the exercise.
• Feel free to totally leave out the notion of 'Category'. With hindsight, it probably just adds work without adding to the educational value of the exercise.

2. A database design. Database contains just three tables:

1. Item: to hold to do items
2. Status: to hold a list of the possible statuses that items can have.
3. Category: to hold a list of the possible categories that can be assigned to an item.

The database is defined in a script. This script works for Firebird. You do not need to use Firebird though - use whatever server you have installed or are familiar with. I had no server installed so I installed Firebird 2. The script is here so you can have a quick look at it, but also included as a text file in the download.

Note that I couldn't get that entire script as presented here to run on Firebird ISQL - I had to create the database first and then run the rest of the script. No idea why.

I used D2007 and DBExpress. I found a post by Martinsg on the ADUG list ( 25/11/2007 at 12:35 pm ) which explained how to set DBExpress up to recognize Firebird.

A couple of notes:

• You can probably tell I'm not a database expert. My field lengths are all over the place, and auto incrementing keys seem like a good idea, but maybe they're not. I hold dates as a 'dd/mm/yyyy' string - seems more friendly than using Firebird's 'date' or 'timestamp' datatypes, mainly because I'm not sure of the conversion to TDateTime, but also because you can read them in SQL output.
• Feel free to vary the design a little, but not in its essentials.

Possible Approaches

Possible approaches to this type of task which have been mentioned are:

• Object oriented, custom persistence code.
• Use of a persistence framework.
• Various RAD approaches, use of ClientDatasets

I have written my version, which is of the 'object oriented, custom persistence' variety. The more alternative implementations we have the more we will gain from the comparison.

One thing I discovered is that even though this is a very simple app, it still took me more man hours than I expected - but I have always been a hoplessly optimistic estimator. Anyhow, a partial implementation of the requirements using your chosen approach could well be sufficient to facilitate discussion.

The download is here. Note that you may need to 'unblock' the .chm file. Just right click, and click the 'Unblock' button on the General tab.