Software

Ask something
Question Locked

Employees Database

0 helpful
3 replies

by lovesil26

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi, I am a Workforce Analyst for a 311 Call Center. I am trying to set up Employees Database in Access, we have this information in Excel and I believe that would be much easier to handle this data in access. My problems are with Primary Keys I know I have to have one for each table to set up relationships betweeen tables; but the only common Id #s for the Employees is the Log In #, Can I use the same Primary Id for each table? Also because this is a employees database for shifts, skills and training needs; we would have include the employees names on most of the tables, would this repetition be a problem? Referential Intergrity would help us making sure that nothing is deleted in error, but we do loose some employees often, would it be a bad idea to use Referential Integrity in this case?.

Sorry I know I have a lot of questions.
Thank you for your time, any help would be really appreciate it.

CSR DATABASE
Employees Table
Log In Id,Ext No.,Last Name,First Name,Date of Hire,Phone Number

Employees by Supervisor
Log In ID, Last Name, First Name, Supervisor Name, Senior CSR
Shifts Table
Last Name,FirstName,Days Off,Start Time, Break, Lunch,Break, End Time, Shift

Shift Change
Last Name, FirstName, Old Shift, Old Days Off, New Shift, New Days Off, Start Date, Expires Notes

Terminations
Last Name, FirstName, Effective Date, Reason

Head Count
Last Name,FirstName,311 Pod, Hrs per Week, FTE Count

Pods
Last Name, FirstName, 311 Pod, Secondary, Third,Spanish, Spanish Secondary

Skills
LastName,FirstName,Level,Sills,Level,Skills,Level,Sills,Level

Temporary Schedules
Log In ID,Last Name,First Name,Start Time,Break,Lunch,Break,End Time,ShiftStart Time Break Lunch Break End Time

3 replies

by tony hopkinson

You need an EmployeeID, access has a feature called identity, (autonumber) baisically it will automatically give each employee a unique number.

Then login ID which is unlikely to be guaranteed unique can be a field in the Employees table.

Then in all the other tables add EmployeeID and remove any of the columns in the employees table. (LastName, Firstame) in most cases

So in a simpler example
Employees
EmployeeID, LogInID, LastName, FirstName

EmployeeSupervisors
EmployeeID, SupervisorID, SeniorCSR

Employees by Supervisors is then something like

Select sup.LastName, sup.FirstName,Emp.LastName,Emp.FirstName
From Employees sup, Employees Emp, EmployeeSupervisors empsup
Where emp.EmployeeID = empsup.employeeID
and emp.SupervisorID = empsup.supervisorid

This wouldn't return the top guy, (he who hath no boss), but that's get roundable.

The key point is you can get what you want, and if someone gets married, changes their name, or even reports that you've spelled it wrong , you don't have to change it in every table.

Worse still LoginID, is only unique in current employees, so you have to give up any historical data. Your Terminations Table works, but how do you know which terminated John Smith is which?
You could look at the data, or even remember the reason why they were terminated, but any computer system you care to design certainly can't.

HtHs. don't be scared by all that sql, you can pick that up as you go along, but if you keep the design you have now, you'll be in too much of a mess to get anything clever out of it.

by deity_chooch

Tony has some excellent advice for you. You want to normalize this database as much as possible by referring to IDs instead of names. Each table should have an ID field that can be referenced as a foreign key to other tables. The way database normalization was taught to me is you always want to have an one-to-many relationship between tables, never a many-to-many relationship. If you run across a many-to-many relationship, you will need to create a table in between that serves as an one-to-many for both tables. (An one-to-many relationship means one record of the table can relate to many fields of another table. For example, one manager may have multiple employees but that employee only has one manager. A many-to-many relationship would occur if employees had multiple managers as well as the managers having several employees to look after.)

Another thing I'd like to suggest is that you never delete a record. Instead, every table should have a "deleted" field that is a boolean (on or off) data type. This way, you simply update this field to remove a record from being displayed.

This discussion is closed to new replies.