Solved

Table Design

Posted on 2014-07-26
11
396 Views
Last Modified: 2014-07-28
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. I’m working on normalizing the tables. Please comment on the proposed design.

Proposed Tables
Employees
EmployeeID
EmplyeFirstName
EmplyeLastName

EmployeeTasks
EmplyeTaskID
EmplyeTaskStartTime
EmplyeTaskEndTime

Subjects
SubjectID
Subject

Supervisors
SupervisorID
SuperFirstName
SuperLastName

SupervisorTasks
SuperTaskID
SuperTaskStartTime
SuperTaskEndTime

SuperEmplyeTaskMtgs
SuperEmplyeTaskMtgID
SuperEmplyeTaskMtgStartTime
SuperEmplyeTaskMtgEndTime

Tasks
TaskID
Task

Topics
TopicID
Topic

TABLE NEEDED
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.

Here is a sample report.
Subject Topic Employee Date Time Accomplishment
Employee Payroll Mary Smith Jan. 1, 2014 1:00 p.m. Entered timesheet information into Quickbooks.

Supervisor meetings: 1:05 p.m. - 1:10 p.m., 1:20 p.m. - 1:28 p.m.


Here is some detailed information about how the database will be used.

Supervisor-Employee Meetings

The supervisor will often check the employee’s work and answer the employee’s questions. Here is an example of how this works. The supervisor may spend five minutes reviewing the employees work. Fifteen minutes later, the supervisor may spend five minutes answering the employee’s questions. The start time and end time of each of these meetings must be recorded. The reports that will be prepared must calculate the average time that the supervisor spent working with the employees on a particular topic. Here is a sample report:

Subject Topic Employee Date Time Accomplishment
Employee Payroll Mary Smith Jan. 1, 2014 1:00 p.m. Entered timesheet information into Quickbooks.
Supervisor meetings: 1:05 p.m. - 1:10 p.m., 1:20 p.m. - 1:28 p.m.


Employees

The employees work on trust administration tasks. The start time and end time of each task must be recorded. Every task that an employee works on is part of a category. An example of a category is “Employee.” Every task that the employee works on is also part of a more narrowly defined topic. An example of a topic is Payroll. The more narrowly defined topics are part of the broad general categories of work. An employee may work on one or more tasks during any time period and the tasks can involve more than one area of trust administration. For example, an employee may spend 10 minutes working on dictation and the next seven minutes typing notes not related to the dictation. Sometimes employee timesheets combine multiple brief tasks into a single period.
0
Comment
Question by:Mark01
11 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 40221755
your design is showing employees and supervisors as separate entities, but the supervisors are also employees, just with a special "status".
even a "supervisor" may have a supervisor (even if it's not in the actual question, rather in real-life), so you should rework that part of the design to start with.

meetings could be between several people, not just 1 supervisor and 1 employee (again, this is not told as such in the question), you need to rework that idea
0
 

Author Comment

by:Mark01
ID: 40221874
You make a good point that a supervisor is an employee with a special "status".

For this database, there is only one supervisor, who is the sole Trustee. The Trustee is not paid using Quickbooks in the usual employee payroll. The Trustee will be paid later with funds from the Trust.

Should there be a Trustee table?

The Supervisor-Employee Meetings are interactions between the sole Trustee and employees. The interactions are always one-on-one.

Here is a revised narrative from above:
The Trustee will often check the employee’s work and answer the employee’s questions. Here is an example of how this works. The Trustee may spend five minutes reviewing the employees work. Fifteen minutes later, the Trustee may spend five minutes answering the employee’s questions. The start time and end time of each of these meetings must be recorded. The reports that will be prepared must calculate the average time that the Trustee spent working with the employees on a particular topic. Here is a sample report:

Subject: Employee
Topic: Payroll
Employee: Mary Smith, Jan. 1, 2014 1:00 p.m. Entered timesheet information into Quickbooks.

Time Trustee spent with employees: 1:05 p.m. - 1:10 p.m., 1:20 p.m. - 1:28 p.m.
0
 
LVL 13

Accepted Solution

by:
Russell Fox earned 300 total points
ID: 40221962
Typically, the Employees table would have a SupervisorID column that would just be NULL for the trustee, and would be the trustee's EmployeeID for everyone else. Here's how I might do it, with how I tend to do naming conventions (Employee instead of Employees, ID instead of EmployeeID, etc.). The last two tables are kind of the "meat" of the solution, so let me know if you don't understand what's going on there.

Proposed Tables      
Employee      
ID      
FirstName      
LastName
SupervisorID            = EmployeeID of the trustee
IsActive                  Boolean, Default = 1 (allows you to remove from views/code without deleting the record)
DateCreated            Default = GETDATE()
UserCreated            = an [Employee].[ID]
DateModified            Create and UpdateEmployee stored procedure, update this as part of that code.
UserModified            = an [Employee].[ID], also part of the UpdatEmployee code
      
Subject
ID      
Name      
Description            It's often nice to have a short [Name] to appear on reports and a longer [Description] to appear on the user interface.
IsActive      
DateCreated      
UserCreated      
DateModified      
UserModified      
      
Task      
ID      
Name      
Description      
IsActive      
DateCreated      
UserCreated      
DateModified      
UserModified      
      
Topic      
ID      
Name      
Description      
IsActive      
DateCreated      
UserCreated      
DateModified      
UserModified      
      
TaskOccurrence      A specific work item or meeting that happened.
ID      
SubjectID      
TaskID      
TopicID      
StartTime      
EndTime      
      
EmployeeTask            One entry for each employee who did the task or was in the meeting, incl. the trustee.
TaskOccurrenceID      
EmployeeID
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
ID: 40222804
I have to disagree with Russell regarding naming conventions.  I prefer to have primary keys have meaningful names.  That makes them less confusing in queries and helps you easily decide which FK matches which PK.  Also "Name" is a reserved word and will cause nothing but trouble.  "Description" may be also.  So, be careful of simple, common words.  The best method (unless you are into memorization) to avoid reserved words is to make compound names.  So SubjectName and SubjectDesc will not cause any conflict.

Your original tables contained prefixes on all column names.  Although this is not wrong, I do not recommend it since in the long run they just get in the way.  You will soon tire of typing the prefix, especially since yours are longish.  It's also that many more characters you have to type before intellisense can kick in.  When looking at tables/queries in datasheet view, the meat of the name will rarely be visible and you'll have to constantly widen the columns to see the entire name.  I understand the purpose of the prefixes and in fact when I was working in COBOL, I was religious about them.  But, COBOL didn't have intellisense and I had to type everything anyway.  When working with a language such as VBA, I would use suffixes instead.  They accomplish the same thing and won't get in your way.

PS - we can help with school assignments.  We just can't do them for you.  Asking for a critique is a good start.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:Mark01
ID: 40223087
Southmod, this is not an academic assignment. Can you provide a link to your policies regarding academic assignments?
0
 

Author Comment

by:Mark01
ID: 40223097
Thank you, Guy Hengel, PatHartman and Russell Fox. PatHartman, this is not a school assignment. I am using the advice from the link shown below for a production database. Can you recommend a better database design book?

Amazon link:
http://www.amazon.com/Database-Design-Mere-Mortals-Relational/dp/0201752840/ref=sr_1_2?ie=UTF8&qid=1406498634&sr=8-2&keywords=database+design+for+mere+mortals
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40223119
The book is excellent.  It is your problem statement that seems contrived and reads like a school assignment.  As long as you are asking for help with specific problems, no one will complain whether it is a school assignment, self help, or work related.  We occasionally get students who have slacked off all semester and then panic the last week as they are trying to complete their class project and then expect us to complete the project for him.

It sounds like you are trying to track very small units of time.  What you really need is a time clock.  This app will not be useful at all unless it is always open.  If you are tracking time in increments of minutes, you can't stop what you are doing to open the application.  It would have to be always open to the "punching" screen.  You would need start and stop buttons as well as a combo for task.  You can't even expect people to type a task name if they are working in 5 minute increments.  Even better than a combo would be a listbox or radio buttons with a very limited number of options.
0
 

Author Comment

by:Mark01
ID: 40223205
Pat, I am serving as a trustee and it is critical that all units of time are tracked. It is very important that the beneficiaries receive reports that indicate how much time has been spent on various tasks. Averages will be calculated and included in future reports.

I currently use a database that is similar to the design proposed by Russell Fox. Handwritten timesheet data is entered into an Access database.

In future questions, I will discuss your comment about prefixes on all column names. I was following the author's (Michael J. Hernandez) advice about field names.

I appreciate all of the advice that I have received on EE.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40224233
Regarding prefixes.  I did not say they were wrong only that in languages that support intellisense they get in the way.  In those languages, suffixes (which perform the same function) are a better choice.  The other reason I don't use prefixes is because they get in the way of my documentation reports.  I have one that checks for consistency across all tables to ensure that columns that exist in multiple tables always have the same definition.  With prefixes, you can't group them together.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

12 Experts available now in Live!

Get 1:1 Help Now