Designing a Flexible Task Management Database

,

Designing a Flexible Task Management

Database

Introduction

Several years ago I used Microsoft

Access to create a database for a Visual Basic desktop application.  This application was for bug tracking or action item

management.  I had already used

Microsoft Access to build a similar application as a temporary solution and now

that I was no longer on site I wanted to provide a more flexible solution.

This meant I needed the database to hold

information that could be manipulated by some kind of management utility, making

it simple for the team to make changes to information displayed in drop down

lists used within the application.  It

would also make it possible for the team to decide if they want to use custom

information specific only to their team.  I’ll

get to this in more detail in a minute.

First, its important to recognize what

will be at the core of all information in the database. 

I’ve found that there are a lot of different names that a team might

call this type of application, but essentially they are all just task

management.  A team needs a way to

take things that have to be done and make sure they are completed. 

So in this case the core of the database would be the action item or the

task and everything else in the database would revolve around this task as its

center.

Now I know two things. 

I know that my task will be my central piece of information and I also

know that some information in the database that is used to describe the task

might not be the same for every workgroup. 

Therefore, it must be flexible.  However,

there will be some information that will be common to every team using the

application.  I can include this

information in the table with the description of the task.

Items

My original design was for an

application to track what our team called action items. 

Therefore, item became the term I’ve continued to use as a name for my

central piece of information.  An

item can be anything the team wants to describe and track such as a bug, issue,

request, or task.

I determined the information common to

each item would be its unique ID, the Item Number, used not only for

identification within the database, but also among the team itself. 

In this table I also included the origination date, the completion date,

the identification of the team member who originated the task, and the

identification of the team member who completed the task.

Other information might be common to

each item, also.  However, this

information might also have additional related information to add further

description.  For example, the

status of a task will almost undoubtedly be important to every group, but

exactly what the status is called and what it means will be different depending

on the team.  While this piece of

information is similar to, say, a team member who originates an item, I am not

going to store the identification of the status with the item itself.  There is a reason for this. 

I’ll explain why in the next section.

Modules

A module is a description you need to

attach to an item. A module can be something like product, version, assigned

user, status, priority, or due date.  As

I discussed earlier, some of this information is specific to the development

team and some of it, such as status and priority, will probably be important to

every team that uses the application.

As I said earlier, even though status

and the originator of an item are both pieces of information used by most teams,

there is still a difference and so I store the information differently. 

The difference is that the status of an item is something that is chosen

or selected from a list.  The issuer of an item simply is the team member who

originated the item.  That is, the

issuer is automatically determined and the information is entered into the

database by the application.

For this reason I handle status the same

way I handle all of the other values that are manipulated by team members. 

These values are collected in modules. 

Each module is stored in its own table to make maintenance and

organization easier.  It also makes

it easier to manipulate the data associated with an item for display in the

application.

Staying Flexible

Now the basic elements of our database

design are determined.  I know that

the item is the center of my database and it will have some information stored

with it in its table.  Most of the

information that describes the item will be its own table. 

These groupings of information are called modules.

This brings me to another feature I

wanted to provide to give my application flexibility. 

I decided to use one database for configuration and another database for

data. This allows me to easily manipulate my configuration and my data

separately. If I want to create a new data store with the same configuration, I

can simply create a new database for the data. 

This is especially helpful when testing or initially setting up an

installation for a team.

In the configuration database the module

table would provide a list of the values that could be selected by a team member

for an item.  In the data database

the values selected for an item would be held in a table bearing the same name

as in the configuration database.  If

necessary or desired the configuration and data could all be kept in the same

database with different names for the list values and the values selected for an

item.

I also had to account for the fact that

not all values would be selected from a list. 

There are many module variations that need to be accounted for.  Some modules might not have a list of values, but might be

freely entered text or a date selection by the team member.  In this case the configuration database module table would be

empty and the application would need to know what kind of dialog to present to

the user.

Conclusion

With all of this flexibility I needed a

way to let the application know what was going on. 

I had said at the beginning of this article that my plan called for a

management utility so that teams could determine exactly what information they

would store.  This information would

need to be configured and the configuration would need to be saved in a location

in the database.  I chose to save

this information in a table called Modules in the configuration database along

with all of the list values.

Each module in the modules table has an

ID.  In the original application I

decided to name each module table, list values and item associated values

tables, as generic numbered table, relating to the module ID.  I could not name the tables by the data stored in the table

because for each group that could be different.

The Modules table, therefore, determined

what a module was named, if it was active or inactive (some groups might not as

much information as others), what type of values it stored, whether multiple

values or only single values could be selected, and whether an alternative table

name was used for the module table (allowing teams to name their tables

something more descriptive).

This concludes a summary of the design

considerations I used in creating a flexible task management database.  Since I developed the database 3 years ago I’ve made small

changes and I still have small improvements planned to give myself an acceptable

balance of flexibility, sense, and data integrity. 

Since I started as flexible as I did in the beginning I gave myself

enough room to satisfy this balance and still maintain a compatibility with

teams that have been using the database for years.

I invite you to take a look at the

current implementation of my task management database and application at:

SQLServerCentral.com users receive a free 10 user license to ItemAction (the program Jamie is talking about in this article) by using the following registration code and company name:

Company Name: FREE from SQLServerCentral.com

Reg Code:

IA-E922CC20-AEABF18192AF16199150-CBAFDD0D49AB2D1E233C

Download ItemAction at :

http://www.itemaction.com

Rate

Share

Share

Rate