Am I designing this database correctly?

[Cole119]

Hi everybody. This is my first time working with databases and Access, so I'm having a little trouble with this project. I've been given an Excel file with multiple worksheets. The title for each sheet is a person's name, and each sheet contains a list of tasks assigned to that person. The only differences between the worksheets is the person each sheet is assigned to and the assigned/due dates for each individual task. Each person is given the exact same tasks, they just have different assigned/due dates. My job is to build a database out of these worksheets. So far I have made three tables in Access:

People:
-ID (PK, AutoNumber)
-FirstName (Text)
-LastName (Text)

Tasks:
-ID (PK, AutoNumber)
-Task (Text)



Dates:
-ID (PK, AutoNumber)
-AssignedTo (FK, ID field from People table)
-Task (FK, ID field from Task table)
-DateAssigned (Date)
-DateDue (Date)
-DateCompleted (Date)

When I click Tools > Analyze > Table, Access tells me that I have duplicating data, but suggests not splitting the table. Do you guys have any suggestions for my design? For all I know I'm going about this completely wrong. I've attached the database file I've created so far if you guys want to take a look. I appreciate any help.
Task Database.mdb

[jzwp11]

Welcome to the forum.

Overall you are off to a good start.

First, I would recommend that you use a more descriptive field name for the primary key field in your tables rather than just ID. Perhaps peopleID and taskID. That will make it easier to see your relationships when you start developing queries.

For your Dates table, I would recommend PeopleTasks since you are relating tasks and people. Also, in that table you have 3 date fields which technically refer to 3 events related to the task. That describes a one (task) to many (events) relationship which technically requires another table.

tblTaskDetail
-pkTaskDetailID primary key, autonumber
-fkPeopleTaskID foreign key to PeopleTasks table
-dteEvent (date of the event)
-txtEvent (detail of the event: assigned, due completed)

If the events related to a task are always the same, you could store a list of those events in a separate table and reference them with a foreign key

tblEvents (would hold 3 records for assigned, due and completed & you can add more in the future as necessary)
-pkEventID primary key, autonumber
-txtEvent

Your task detail table would now look like this:

tblTaskDetail
-pkTaskDetailID primary key, autonumber
-fkPeopleTaskID foreign key to PeopleTasks table
-dteEvent (date of the event)
-fkEventID foreign key to tblEvents

Your dates table would be renamed PeopleTasks & would look like this

PeopleTask
-pkPeopleTaskID (PK, AutoNumber)
-AssignedTo (FK, ID field from People table)
-Task (FK, ID field from Task table)

[Cole119]

Thanks for your reply. So since the dates related to the tasks aren't always the same, should I put them in their own table like this:

EventsTable
-EventID
-AssignedDate
-DueDate
-CompletedDate

And then use the EventID from that in tblTaskDetail as a foreign key, or should I just add three fields to tblTaskDetail for the assigned, due, and completed dates?

[jzwp11]

The actual dates of the events do not matter, just the events related to the task. At present, you have 3 events: Assigned, Due and Completed. Each event should be a record not a field. Open the form frmPeople in the attached database and then look at the task detail subform within frmPeople to see what I mean

[Cole119]

Ah okay, I see what you mean now. I apologize for my ignorance, but what is the benefit of having assigned, due, and completed as records as opposed to fields?

[jzwp11]

The structure I show is the normalized (see this site for more on normalization) structure. Having multiple event dates as fields would not be a normalized structure. The advantage of normalizing the structure as I have shown is that you can incorporate other events that you may want to track in the future without changing the structure of the table. For example, let's say you want to track not only the assign, due and completion dates but also other milestones/dates for a project. You would have to add more fields to the table, but you will also have to alter every related form, query or report to accommodate those additional fields. With the normalized approach, you just add new records to the event table and to the task detail table. There would be no need to alter the table design or the related forms, queries or reports.