Table Design

[tyrobrio]

I'm in charge of a reporting database project and I am having some problems deciding on the design of my tables. This project is sort of related to the Tasks Database part of Access' Templates.



I need a database that collects data for several predefined Tasks (about 30). Each Task is associated with different fields for example:

Task1: Field1,Field3,Field5
Task2: Field1,Field4,Field5,Field6
Task3: Field2,Field6,Field11,Field12

Right now I have one main table with the ability to enter each individual task. I have a composite key for the TaskID and Date. And each entry has an employee associated with it.

My First question is with so many different tasks being associated with different Fields should I keep it all in One Table or Split each task into their own tables. What is good practice here? If I don't split I will have many null entries. If I split then I will make the whole database too complex.

My Second is question has to do with the employee who completed the task. I need to create reports that show what Task each employee did for a determined amount of time. But the problem is that some of the Tasks can be completed by several different employees per day. Which throws out my composite key of TaskID and Date out the window.

[June7]

Only 12 fields? What data is in the fields?

[tyrobrio]

The fields are basic Number fields. They contain things that employees manually posted or how many things posted through an automated process.

So is it safe to say that a table with only 12 or so fields with null values isn't a big deal?

[orange]

My suggestion would be to write down what output is expected from such a database.
Look for some sort of themes or groupings that make sense. Abstract those groupings into meaningful tables and relationships and try to match them to some processes.

Prototype what you are thinking and get users/management to agree/adjust until they are happy. Keep people in the communications and let them be so involved as to set the pace of development/adjustment.

Good luck

[June7]

Lots of blank fields indicates non-normalized structure, not necessarily 'bad' but maybe not optimal. Depends what you want to do with the data. A normalized design could be:

RecID (autonumber)
TaskNum (task1, task2, etc)
ItemType (the field1, field2, etc name)
Amount
Source (employee, auto)
DatePost

Normalization vs ease of data entry/output can be a balancing act. I have tables with empty fields because the structure suits my primary input/output requirements.

[ellisthomas86]

If you are getting hooked up with loads of tasks, my recommendation will go with implementing an online web based task management software which will eat up all the problems hurdling you.

[orange]

You do realize that this thread, before your post, was about 22 months old?
Looks like tyrobrio has moved on.....