Normalization Help

[CancerStick]

I'm about to create a new database for my company. I have thought it out and planned how I want everything to work. I just want to run it by somebody and see if everything seems okay or if I need to rethink my strategy.

The database is an employee database and I'm creating it for employee information, employee training, equipment issue, company billing, company payroll, employee write-ups, and employee operating licenses.

The Entities and its fields are as follows

Employee Info
Employee ID
Last Name
First Name
Em Address
Em City
Em State
Em Zip Code
Em Date of Hire
Em Date of Birth
SSN
Driver's License
Tax Status
Primary Phone
Secondary Phone
email
Guard Card (Y/N)
Position
Manual Issued (Y/N)
Pay Rate
Photo (attatchment)

Work History
Prev Employer Name (Relationship to Employee ID in Employee Info Entity)
Prev Employer Address
Prev Job Title
Prev Dates Employed
Prev Job Description

Emergency Contact
Employee Name (Relationship to Employee Info Entity)
Emerg Contact Last Name
Emerg Contact First Name
Emerg Contact Relationship to Employee
Emerg Contact Primary Phone
Emerg Contact Secondary Phone

Guard Card
Employee Name (Relationship to Employee Info Entity)
Em ID (relationship to Employee Info Entity)
Card Number
Date Issued
Expiration Date

Not all employees have a guard card

Training
Employee Name (Not sure if I need to build relationship to Empoyee Info Entity because not everyone qualifies for this training.
Date
Class Name
Training Hours Recieved
Instructor Name
Test Taken (Y/N)
Test Score

Class
Class Description
Class ID

Employee Write Ups
Last Name
First Name
Employee ID
Write Up Date
Employee Job Title
Warning Type (look up)
Offense (look up)
Job Location
Event Name
Description (memo)
Employee Sign (Y/N)
Witnessed by
Supervisor making report
Attatchment

Equipment Issue
Last Name
First Name
Employee ID
Equipment Issued (Relationship to Equipment Inventory Entity)
Qty
Date Issued

Equipment Inventory
Equipment ID
Equipment Description
Qty in Stock
Qty in field

Employee Loan
Last Name
First Name
Employee ID
Loan Amount
Date Received
Reason For Loan

Billing
Date
Invoice #
Fed ID
Client Name
Attention
Client Address
Primary Phone
Secondary Phone
Services Rendered
P/O #
Number of Personnel
Rate
Hours Worked
Billing Total

Payroll
Employee ID
Last Name
First Name
Pay Rate
Date
Job Name
Job Location
Time In
Time Out
Reg Hours
OT Hours
DT Hours
Pay Modifier
Modifier Description
Pay Total
Notes


That's it. I'm not sure if I'm going to keep the payroll and billing in the same access file with Employee info. What do you guys think?

any advice would be appreciated.

Thanks

 

[namliam]

1) Dont use spaces in Field/table names
2) First and Second phone??
what happens when someone has a third phone? Or just one?
Maybe its better create a relational table to store phone numbers/email addresses?
3) Training employee name
Use the ID anyway, Name is a part of the employee weather or not everyone qualifies for training doesnt matter.
4) Equipment Issue/Loan / payroll
Firt name/ last name they are part of the employee as well?? If so use the ID only

At quick glance thats it for me...

 

[CancerStick]

Thanks for the critique, appreciate it