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 :
