Designing a Flexible Task Management Database Part II
Introduction
In my last article titled “Designing a Flexible Task Management Database”, I shared my thoughts and reasoning behind the design of the database that I used in my task oriented software.
In this article I’d like to provide a summary of the information, some supporting graphics, and share some more details about how information stored in the database.
Items
A task, called an item, is the central piece of information. Workgroups must be able to decide what information is important for them to describe each item.
An item is designated by its unique ID generated automatically by the items table in the database and is displayed as the item number in the application.
Modules
A module is a description of an item and a grouping of information. A module can have multiple values where one or more value can be selected from a list. A module could also hold a numeric, text, or date value.
Values
Some values, such as issue date, close date, issuer, and closer are generated automatically by the application and therefore are stored with the item in the items table. All other information, or any value that is entered or manipulated by a user, is stored as a module in its own module table.
Databases
Although it is possible to use one database, two databases are normally used. One is for the configuration and one is for the data. In the configuration database a module table holds the values that may be selected from a list. In the data database a module table by the same name holds the values selected along with the item number of the item it is associated with.
Tables
Since I could not name module tables by the information they held (since it might be different for every workgroup) each module table would have a generic name. In the configuration database there is a modules table that holds the description of each module and each module has a database row ID. I used this ID to generate the name of each module table.
Module Data
Up until now, I’ve mainly talked about how the databases are configured and about some of the basic components of the system. Items are the central component by which data is organized. The flexible module configuration helps connect each item with a hierarchal set of workgroup-defined sets of values. This module layout can be configured with a management front-end application.
Additional Data
In addition to modules, other data tables can be attached to Items. Data such as Notes, Hours (for time management), and Resources (for resource management) can be attached to Items. These data tables can each be built to hold meaningful information that can be edited in the client system.
Diagrams
Following is a diagram of how data is stored and attached to Items:
Application
This application has been used to handle bug-tracking, timesheet, helpdesk, document tracking, and general task management by thousands of users. It's difficult to put into words how it all comes together without having you see it in action. If you would like to try this free application you can download the setup packages and SQL Scripts at:
