Primary Key & foreing key relationship

[Greg]

Hi,

I am trying to set up a system that tracks employee vacation usage. Our
employees accrue 5 days vacation after every 2,000 hrs.

I have set up two tables. The first table, I call EMPLOYEE table.

EMPLOYEE table consists of the following fields:
SS# (Primary key)
Employee Name
Employee Type
Start Date
End Date
Yes/No

The second table I called VACATION LOG.
VACATION LOG table consists of the following fields:
SS# (Foreign key)
Employee Name
Hours Taken
Date Taken
Week Ending Date

My question is this, I have a primary key (SS#) in the EMPLOYEE table and a
foreing key (SS#) in VACATION LOG table that relates them. But I do not have
a primary key in the VACATION LOG table. Is it possible not to have a
primary key in a table? I have thought about using the EMPLOYEE ID as a
primary key in the VACATION LOG but I am unsure of what use that would be
since I have already used the Employee SS # as an identifier in the EMPLOYEE
table.

I would appreciate any helpful insights on how to go about this.

Thanks

 

[Tom Wickerath]

Greg,

If you have an internal Employee ID number available, might I suggest that
you eliminate the use of the SSN all-together? I shudder when I read that
people store such sensitive information in JET databases!

Why Social Security Numbers should not be stored in an Access database
http://www.access.qbuilt.com/html/security.html#DoNotStoreSSNs

Is it possible not to have a primary key in a table?

In Access/JET, yes it is certainly possible. Is it adviseable? No. If
nothing else, just add an Autonumber field.

You should not need to store the Employee Name in the Vacation Log table.
Also, the Week Ending Date can always be calculated, based on the Date Taken,
so you don't need to store this either.

Our employees accrue 5 days vacation after every 2,000 hrs.

Only 5 days for basically a full year (2,080 hours)? Just "okay" for entry
level, but not exactly so generous.
</Soapbox>


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Greg]

Hi Tom,

Thanks for the quick response. Yes we do have an internal employee Id that
I can use instead of the SS# and I will start implementing that asap. I
appreciate the warning and concerns you raised.

Yes I work for a temporay staffing company and we give our employees 5 days
vacation when they accrue 2,000 hrs. It's not the most generous like you
said but it is something when most don't.

Thanks again