[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Data Modeling (Entities) [employee db]

Posted on 2014-08-26
Medium Priority
Last Modified: 2014-08-27
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:
Question by:Mark01
  • 2
  • 2
LVL 11

Expert Comment

by:Joseph O'Loughlin
ID: 40285810
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.
LVL 14

Assisted Solution

by:Russell Fox
Russell Fox earned 2000 total points
ID: 40286072
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.

Author Comment

ID: 40286197
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.
LVL 14

Accepted Solution

Russell Fox earned 2000 total points
ID: 40286305
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.

Author Closing Comment

ID: 40287909
Thank you, Russell Fox.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question