Data Modeling (Entities) [employee db]

Posted on 2014-08-26
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
    LVL 11

    Expert Comment

    by:Joseph O'Loughlin
    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 13

    Assisted Solution

    by:Russell Fox
    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

    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 13

    Accepted Solution

    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

    Thank you, Russell Fox.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Creating and Managing Databases with phpMyAdmin in cPanel.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now