Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Designing a Flexible Task Management Database

By Jamie Voss,

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
Total article views: 5981 | Views in the last 30 days: 3
 
Related Articles
FORUM

Database Design Question

Customer databases or module databases

BLOG

sys.sql_modules vs. INFORMATION_SCHEMA.Routines

Consider the following two queries meant to search for a keyword in SQL programmability objects. se...

FORUM

best way to inform the user about newly released application.?

best way to inform the user about newly released application.?

FORUM

Hardware Information

Hardware Information

FORUM

CPU data used by the application databases

CPU data used by the application databases

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones