Generating automatic entries (holidays) for Employee AttendanceDatabase

[alexcuse]

Hi,
I'm in the very early process of creating an employee attendance
database that I hope can record holidays as attendance entries
automatically.

Here are the relevant tables and fields {primary key in brackets}:
Employee: {SSN}, first name, last name, etc.
Working Date: {SSN, Start Date}, End Date, Position
Attendance: {SSN, Year, Payroll Week, Month, Day, Attendance Code},
Numerical Value, Comments
Holidays: {Year, Month, Day}, Holiday Name, Attendance Code (always
have a default value of "H")

relationships:
Employee:SSN has a one-to-many relationship to Working Date:SSN
Employee:SSN has a one-to-many relationship to Attendance:SSN
Holidays:Year,Month,Day has a one-to-many relationship to
Year,Month,Day; wanted to include "Attendance Code" in the
relationships but it wouldn't let me due to not being a primary key in
"Holidays"

Basically, before the beginning of the year I'll enter the list of
holidays into the Holidays table. Upon entering this information, I
would like Attendance table to automatically generate new entries for
each holiday for each existing employee. Also, if a new employee is
added during the year, the Attendance table should automatically
create new records of holiday absence for this employee for all
holidays listed from the Holidays table.

Can this achieved in the table design process or does it require more
some kind of more complicated batch-process using forms? Should there
be some kind of relationship between Working Date table and Attendance
table so that it doesn't include terminated employees?

Thank you very much with your help! I hope I am not completely off-
track in my whole thought-process and direction.

--Alex

 

[Arvin Meyer [MVP]]

A few observations:

1. Unless this is your accounting and payroll database, you should not store
SSN's. That is private information, which if stolen could leave you and/or
your company in an unenviable liability situation. If you are using the
database for accounting purposes, make sure that it is secure, and the
network and servers are secure.
2. There is no reason to actually store the holiday dates more than once.
The purpose of a relational database is to never have to store data in more
than 1 place. You should be able to link to the holiday table in a query
showing days worked.