Earnie
Here is a kcik at the can. I use Access which has some security issues than Oracle or MS*SQL, but I address this security issue in the design...
Design
tblEmployeeMaster
EmpID - primary key
EmpLN - last name, text, 25 char
EmpFN - first name + initial, 15 char
SenorityDate - date
ManagerID - foreign key to tblEmployeeMaster
Comments - memo field
Discussion:
General information for employee, no confidential info
tblAddress
AddressID - primary key
EmpID - foreign key to employee master file
Primary - yes / no
AddrType - text - mailing, home, parents, actual, summer, temp
AddrLine1 - text, 255 char
AddrLine2
City
StateProv
ZipPostCode = zip or postal code
ActiveTil - date
Comments - memo field
Dicussion:
Most employees will have one address, but you may want to capture the parent address, etc. For example, a retired or semi-retired employee may have more than one address. The ActiveTil is a date field for when the address is good for (example, summer address).
The "primary" boolean field is to determine what address to be used as the mailing address. Perhaps redundent for AddrType.
tblPhone
PhoneID - primary key
EmpID - foreign key to employee master file
Primary - yes / no
PhoneType - text, work, home, cell, emergency contact, pager, fax, etc
PhoneNo - text, 255 char
PIN
Comment - memo field
Discussion:
Like the address table, you want to be able to accommodate multiple phone numbers including the emergency number. The comment field can be used to document details such as the name of the emergency contact.
You can also use the Phone table to capture the eMail address(es), or create another table for this task. Not really much different from my perspective.
tblEmpConfidential
EmpID - primary key, same as from tblEmployeeMaster
ConfidentialPhone - confidential / unlisted phone number
CurrentPay - currency
PayType - hourly, salary
StartDate - date
EndDate - date
OTElegible - yes / no
BenefitCode - text, foreign key to Benefit table
Comment - memo field
+ over specific info for confidential info
Discussion:
Would include confidential material privy to management.
tblEmpPrivate
EmpID - primary key, same as from tblEmployeeMaster
CurrentPay - currency
TerminationCode - text
Discussion:
Would include highly confidential material privy to seniro and HR management.
tblCPayHistory
CPayHistoryID - primary key
EmpID - foreign key to tblEmpPrivate table
EffectiveDate - Date
AdjustRate - Decimal, percentage of adjustment
AdjustType - bonus, increase,
Adjustment - currency
PayType - text, hourly, salary
Comment - memo field
Discussion:
Tracks changes in pay history. Bouns or increase. Note that you can track when an employee was switched from hourly to salaried too.
tblCReview
ReviewID - primary key
EmpID - foreign key to tblEmpPrivate table
ReviewDate - date
ReviewReason - text, performance review, discipline, etc
ReviewNotes - memo field
ReviewAction - text, outcome of review - bonus, suspension
CPayHistoryID - foreign key to tblCPayHistory table
Comment - memo
ReviewLink - HyperLink to external documentation
Discussion:
Track reviews - performance or otherwise. Link to pay history for easy reference. External hyperlink for notes or reprimand, etc.
tblCAbsence
AbsenceID - primary key
EmpID - foreign key to tblEmpConfidential table
Authorized - yes / no
AbsType - reason for absence - vacation, suspension, sick
AbsDate - date of absence
AbsHours - hours of work missed
AbsIncident - interger
Comment - memo
Discussion:
Absence tracked by exception. Track hours to track partial days. Incident number is to track reportable offenses - for example, 3 day suspension but only one day would have a reportable offense of "1"; the other recorded dates would have "0".
tblCEmpHistory
EmpHistoryID - primary key
EmpID - foreign key to tblEmpPrivate table
EmpHistDate - date
EmpHistAction - text, type of action - promotion, sick leave
EmpHistDetail - text, 255 char
Comment - memo
Discussion:
Track details on employee in addition to reviews and pay history. For example, promotions, extended sick leave, etc.
tblEmpDocument
EmpDocumentID - primary key
EmpID - foreign key to tblEmpConfidential table
EmpDocLink - hyperlink to employee document
Comment - memo
Discussion:
A collection of linked documents. For example, scanned of signed policy statements, letters of reprimand, letters of acknowledgement, letter of offer. etc.
tblBenefit
BenefitCode - primary key
Vender - text
VenderAddress - numeric, foreign key to address table
EffectiveDate - date
GeneralCoverage - text, family, single, smoker, non-smoker
Comment - memo
Discussion:
Address found on address table. Effective date would be the contract expiration date. Companies do change venders. General coverage would be the overall package description.
tblBenefitDetail
BenefitDetailID - primary key
BenefitCode - foreign key to tblBenefit
CoverageCode - text
LimitAmount - currency
LimitPercet - decimal,
CoverageFrequency - interger (months)
Comment - memo
Discussion:
Details on benefit coverage. Child dental, family health, spouse optical, family support, etc. Limits would refer maximum dollar / euro amount, or maximum percentage amount. Frequency would refer to 6 months, 12 months allowed between occurences.
Beneift transactions would be managed by the vender and not necessary for this design.
tblTask
TaskID - primary key
TaskDesc - text
TaskType - text, critical, production
ReviewDate - date
Discussion:
For the purposes of cross training, responsibilities, etc, this table lists tasks critical to the business. The TaskType would categorize critical processes, production, ISO, etc. Review date would capture last time record was reviewed / updated to ensure data is current.
tblEmployeeTasks
EmpTaskID - primary key
EmpID - foreign key to tblEmpConfidential table
TaskID - foreign key to Tasks
EmpTaskType - owner, cross training, lead, manager, do-er
ReviewDate - date
Comment - memo
Discussion:
Many-to-many joiner or profile table on employee and tasks. This would allow HR to see who is trained to fill-in for sick employee, what critical tasks need to be covered during vacation or termination. After a termination, especially with cut backs, business critical processes may have no owner unless this type of work is documented. I am not sure if you wan to make this table "public" - link to employee master table or "confidential" - link to confidential table.
Security:
Use a front end / back end database with Access (group)login security. The back end should have three databases
- "Public" database can be in a shared location, and the contact information can be used by other Access databases. Would include Employee Master table, address and phone tables. It would allow all user to see who is the manager to the mployee too. The "public" area would include the Benefit tables so employees could see their benefits. I am not sure about the Tasks and Employee Tasks table -- perhaps move these to "production".
- "Production" confidential database would contain info required for production. In this case, tasks and the employee x tasks so management could call the an employee to cover for another employee off sick. It would also allow the creation of letters of reprimand by management. This folder would be more secure by the use of the operating system security - Active Directory, NT or Novell and would include tables... tblEmpConfidential, Task and Employee Tasks, Employee Documents and Absences.
- "Private" database would be for pay, reviews, pay and employee history. This database would be in a more restrictive directory, and would include tblEmpPrivate, tblCReview, tblCPayHistory and tblCEmpHistory.
Presentation
I feel a main form build from tmpEmpMaster table with a multi-tab form with embedded subforms.
The main form would look up the EmpID for tmpConfidential and tblEmpPrivate. If found, then the user has security, and linked tables can be accessible.
The either the tmpConfidential.EmpID and / or tblEmpPrivate.EmpID is not found, then a) the linked tables will not be accessible, b) you can use code to hide subform and pages that the end user is restricted from viewing.
The general public would see two or three tabs. Management would see a few more tables, and finally, HR and senior management would see all the tabs and subforms.
By splitting the data table into three, you can use both Access security and the security of the server / network operating system.
closing
I have not included some things such as training. But armed with the posted info, I am sure you can work these details out. Also, you may differ in opinion in what should be accessible. by all means....
I did not spend time on discussing Access security, but you could use Access security to restrict managers to viewing conifendtial info on their own employees.
Oh yea, I have not tested this design but from experience, I am confident it would work as expected.
Richard