Designing an employee holiday / vacation database

[Chris Strug]

Hi,

As something of a pet project, I'm looking to develop a tool to track
employee holiday (or vacation for those of us in the US) for my company -
good for the company (if I get something usuable working) and good practice
for me.

Before I even open Access I want to get the relationships sorted.

First looks indicate two basic entities:

Employee
- ID
- Forename
- Surname
- Dept
- other employee details.... etc etc
- holiday allowance (number of days)

Holiday
- Holiday ID
- Employee ID
- Holiday Start Date
- Holiday End Date

Then its a simple one Employee to many Holiday records. Queries can handle
days taken by employee X and so on. Business rules handled by forms (not
letting X take more than their allowance, etc).

Now assuming that is a workable model, what I'd like to do (and this is
where I'm stuck) is to include a feature where I can link employees so for
example, employee X cannot be on holiday if employee Y is on holiday that
date or employee Z is. I.e. with-in a department there may be that only two
of its employees may be off at any one time.

Any ideas? I'm near certain I'm going to require another table linked to
employees but I'm stuck as to how to develop it. Can anyone suggest how I
should develop my table structures. I don't want to be spoon fed but some
pointers in the right direction would be greatly appreciated.

Any and all replies are gratefully received.

Thanks

Chris.

[Darryl Kerkeslager]

1. Your model contemplates all vacations taken in full days, doesn't it?
What about 2 hours at a time?

2. I would store holidays by single day, rather than start date and end
date, so a 5 day holiday would have 5 entries, not just one. For queries,
it would be much faster to query for a specific day, rather than a range of
days

3. I personally would not handle the conflicts by table constraints. If an
employee wants to take a holiday on Nov 25, then run a query to check if the
date is already in use, ex.

"SELECT Count(*) FROM holiday WHERE hol_date = #" & Me.txtDate & "#"
Darryl Kerkeslager
"Chris Strug" <ho*****@solace1884.com_SWAP_DOMAIN_AND_USERNAME > wrote in
message news:9h******************@text.news.blueyonder.co. uk...

Hi,

As something of a pet project, I'm looking to develop a tool to track
employee holiday (or vacation for those of us in the US) for my company -
good for the company (if I get something usuable working) and good practice for me.

Before I even open Access I want to get the relationships sorted.

First looks indicate two basic entities:

Employee
- ID
- Forename
- Surname
- Dept
- other employee details.... etc etc
- holiday allowance (number of days)

Holiday
- Holiday ID
- Employee ID
- Holiday Start Date
- Holiday End Date

Then its a simple one Employee to many Holiday records. Queries can handle
days taken by employee X and so on. Business rules handled by forms (not
letting X take more than their allowance, etc).

Now assuming that is a workable model, what I'd like to do (and this is
where I'm stuck) is to include a feature where I can link employees so for
example, employee X cannot be on holiday if employee Y is on holiday that
date or employee Z is. I.e. with-in a department there may be that only two of its employees may be off at any one time.

Any ideas? I'm near certain I'm going to require another table linked to
employees but I'm stuck as to how to develop it. Can anyone suggest how I
should develop my table structures. I don't want to be spoon fed but some
pointers in the right direction would be greatly appreciated.

Any and all replies are gratefully received.

Thanks

Chris.