Data Modeling (Entities) [employee db]

This is not an academic assignment.

This question involves entities only, no attributes. Do you agree with the proposed entities?

I am designing an employee database. The purpose of the database is to maintain the data used to track information about the work performed by the employees and the supervisor. Both the employees and the supervisor work for two different trusts.

Multiple tasks must be tracked. They can occur in a single time period. Here is a sample report that shows two tasks that were completed in 25 minutes.

Mary Smith, Jan. 1, 2014 1:00 p.m. to 1:25 p.m. (a) Entered timesheet information into Quickbooks and (b) Typed notes for attorney.

Here is a second sample report and a description of it:
The employee, subject, topic and task must be associated with each time period. It also has to show the start and stop times of the supervisor and employee task meetings.

Sample report:
Employee: Mary Smith; Subject: Payroll; Jan. 1, 2014 1:00 p.m.; Task: Entered timesheet information into Quickbooks.
Supervisor meetings: 1:05 p.m. - 1:10 p.m., 1:20 p.m. - 1:28 p.m.

Proposed entities:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Joseph OLoughlinIT Support SpecialistCommented:
Time is an illusion, lunchtime doubly so.  
Flag interruptions (reactive) as it takes time to get back into the work flow, and also flag other work as proactive.
Russell FoxDatabase DeveloperCommented:
I assume the Employee table will have a Supervisor field that's just another EmployeeID? That's good. One possible suggestion is combining Subjects and Topics into a single table with a ParentID key back to itself (like the SupervisorID to an Employee). That way you could have any number of levels: Cutting Payroll Checks --> Payroll --> Accounts Payable --> Accounting. Here would "Cutting Payroll Checks" be a topic or a task? Is a task generic, like cutting payroll checks, or a specific instance of a task, like "Bob cut the payroll checks yesterday"? It might also be more annoying because you would need a recursive CTE to walk through the hierarchy. That gives you the flexibility to report time at any level of the hierarchy, like comparing how many man-hours were spent in AP versus AR, or figuring out what percent of Payroll time is spent cutting the checks.

I also think you're going to get into trouble with having TimePeriods as a table. How long is a time period? I think you'd be better off just having a start/stop time for each instance of a task.
Mark01Author Commented:
Yes, I think the Employee table will have a Supervisor field that's just another EmployeeID. The reason for the Time Periods entity is to track the multiple tasks that can occur in a single time period.
Russell FoxDatabase DeveloperCommented:
I would probably do:

Activity: actual, like a meeting that happened today, has a [StartTime] and [EndTime].
Topic: something to discuss or work on, but not an activity.
Task: specific tasks that can be done ("cut payroll checks").
Accomplishment: tasks done during the activity, [ActivityID], [TaskID], many-to-many.
ActivityEmployee: people involved in the activity, [ActivityID], [EmployeeID], many-to-many.
ActivityTopic: topics involved in the activity, [ActivityID], [TopicID], many-to-many.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark01Author Commented:
Thank you, Russell Fox.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.