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 Part II

By Jamie Voss,

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:

Items Data Diagram

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:

http://www.itemaction.com
Total article views: 4646 | Views in the last 30 days: 1
 
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

configuration

configuration

FORUM

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

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

FORUM

Report Server Database Configuration

Report Server Database Configuration : Error

Tags
basics    
database design    
 
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