Working Hours database

Earlier in the year I devised a database to hold working hours information
for members of staff. Because I didn't know any better I used a 1:m
relationship between two tables.

In time for use after Christmas, I have decided to redesign it. I am using
all the same fields but re-organising.

At the moment I have the following:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
WeekID
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

A potential alternative:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_wk
EmployeeID (FK)
DeptID (FK)
WeekID (PK)

tble_hrs
WeekID
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

Any alternatives, improvements?


Cheers!

Or possibly

tble_Wk
WeekID (PK)

tble_employee
WeekID (FK)
EmployeeID (PK)
FName
SName
Work
WorkStat
Operations
Manager

tble_Dept
DeptID (PK)
Dept
Subdept
CostCentre
Manager

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo



"scubadiver" wrote:

Earlier in the year I devised a database to hold working hours information
for members of staff. Because I didn't know any better I used a 1:m
relationship between two tables.

In time for use after Christmas, I have decided to redesign it. I am using
all the same fields but re-organising.

At the moment I have the following:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
WeekID
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

A potential alternative:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_wk
EmployeeID (FK)
DeptID (FK)
WeekID (PK)

tble_hrs
WeekID
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

Any alternatives, improvements?


Cheers!

Hello again scuba,

Is this db integrated with the other one or separate?

quick draft design would be;

TblDept
DeptID (PK)
DeptName

TblSubDept
SubDeptID (PK)
SubDeptName
CostCentre
Operations
Manager
DeptID (FK)

TblEmployee
EmployeeID (PK)
FName
SName
Work
WorkStat
HrlyRate
Basic
CntrctHrs
BasicHolAll
ExtraHolAll -if extra can be earned from o/time etc
SubDeptID (FK)

TblWeek
WeekID (PK)
WeekNumber - 1 to 52?

TblRate
RateID(PK)
RateType eg Basic OT1 OT2 -allows for future
changes
BasicMultiplier eg 1.0 1.5 2.0

TblHrs
RecordID (PK)
EmployeeID (FK)
WeekID(FK)
RateID(FK) -allow Nulls
HolidayID(FK) -allow Nulls
HrsWorked

TblHoliday
HolidayID(PK)
LeaveType
LeaveNo

as before, if you post your e-mail on either thread, I'll be happy to post
dummy db with sample to data to aide in explanations.

Hope this helps,

TonyT..

"scubadiver" wrote:

Or possibly

tble_Wk
WeekID (PK)

tble_employee
WeekID (FK)
EmployeeID (PK)
FName
SName
Work
WorkStat
Operations
Manager

tble_Dept
DeptID (PK)
Dept
Subdept
CostCentre
Manager

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo



"scubadiver" wrote:

Earlier in the year I devised a database to hold working hours information
for members of staff. Because I didn't know any better I used a 1:m
relationship between two tables.

In time for use after Christmas, I have decided to redesign it. I am using
all the same fields but re-organising.

At the moment I have the following:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
WeekID
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

A potential alternative:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_wk
EmployeeID (FK)
DeptID (FK)
WeekID (PK)

tble_hrs
WeekID
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

Any alternatives, improvements?


Cheers!

Hello again,

I have posted my email on the other thread.

It makes sense to have a separate table for weekID.

The slight problem I have is that the hourly rate is in with the employee
info. In the current database, the rate is with the hours worked so if the
rate changes, it won't change the cost calculation for every record.

"scubadiver" wrote:

Earlier in the year I devised a database to hold working hours information
for members of staff. Because I didn't know any better I used a 1:m
relationship between two tables.

In time for use after Christmas, I have decided to redesign it. I am using
all the same fields but re-organising.

At the moment I have the following:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
WeekID
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

A potential alternative:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_wk
EmployeeID (FK)
DeptID (FK)
WeekID (PK)

tble_hrs
WeekID
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

Any alternatives, improvements?


Cheers!

This and the training database are separate.

"scubadiver" wrote:

Earlier in the year I devised a database to hold working hours information
for members of staff. Because I didn't know any better I used a 1:m
relationship between two tables.

In time for use after Christmas, I have decided to redesign it. I am using
all the same fields but re-organising.

At the moment I have the following:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
WeekID
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

A potential alternative:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_wk
EmployeeID (FK)
DeptID (FK)
WeekID (PK)

tble_hrs
WeekID
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

Any alternatives, improvements?


Cheers!

Another point: in my current database, the rate multiplier is done in a
query. Any reason not to?

thanks

"scubadiver" wrote:

Earlier in the year I devised a database to hold working hours information
for members of staff. Because I didn't know any better I used a 1:m
relationship between two tables.

In time for use after Christmas, I have decided to redesign it. I am using
all the same fields but re-organising.

At the moment I have the following:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
WeekID
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

A potential alternative:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_wk
EmployeeID (FK)
DeptID (FK)
WeekID (PK)

tble_hrs
WeekID
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

Any alternatives, improvements?


Cheers!

The other thing I have noticed is that the holiday table has the primary key.
During each week, an employee can have more than one reason to be absent so
surely the holiday table should have the foreign key?

"TonyT" wrote:

Hello again scuba,

Is this db integrated with the other one or separate?

quick draft design would be;

TblDept
DeptID (PK)
DeptName

TblSubDept
SubDeptID (PK)
SubDeptName
CostCentre
Operations
Manager
DeptID (FK)

TblEmployee
EmployeeID (PK)
FName
SName
Work
WorkStat
HrlyRate
Basic
CntrctHrs
BasicHolAll
ExtraHolAll -if extra can be earned from o/time etc
SubDeptID (FK)

TblWeek
WeekID (PK)
WeekNumber - 1 to 52?

TblRate
RateID(PK)
RateType eg Basic OT1 OT2 -allows for future
changes
BasicMultiplier eg 1.0 1.5 2.0

TblHrs
RecordID (PK)
EmployeeID (FK)
WeekID(FK)
RateID(FK) -allow Nulls
HolidayID(FK) -allow Nulls
HrsWorked

TblHoliday
HolidayID(PK)
LeaveType
LeaveNo

as before, if you post your e-mail on either thread, I'll be happy to post
dummy db with sample to data to aide in explanations.

Hope this helps,

TonyT..

"scubadiver" wrote:

Or possibly

tble_Wk
WeekID (PK)

tble_employee
WeekID (FK)
EmployeeID (PK)
FName
SName
Work
WorkStat
Operations
Manager

tble_Dept
DeptID (PK)
Dept
Subdept
CostCentre
Manager

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo



"scubadiver" wrote:

Earlier in the year I devised a database to hold working hours information
for members of staff. Because I didn't know any better I used a 1:m
relationship between two tables.

In time for use after Christmas, I have decided to redesign it. I am using
all the same fields but re-organising.

At the moment I have the following:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
WeekID
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

A potential alternative:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_wk
EmployeeID (FK)
DeptID (FK)
WeekID (PK)

tble_hrs
WeekID
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

Any alternatives, improvements?


Cheers!

Sorry for the messages!

Another thing I have noticed with the design is that each employee can only
work for one subdepartment.

Each employee can work for many subdepartments.

"TonyT" wrote:

Hello again scuba,

Is this db integrated with the other one or separate?

quick draft design would be;

TblDept
DeptID (PK)
DeptName

TblSubDept
SubDeptID (PK)
SubDeptName
CostCentre
Operations
Manager
DeptID (FK)

TblEmployee
EmployeeID (PK)
FName
SName
Work
WorkStat
HrlyRate
Basic
CntrctHrs
BasicHolAll
ExtraHolAll -if extra can be earned from o/time etc
SubDeptID (FK)

TblWeek
WeekID (PK)
WeekNumber - 1 to 52?

TblRate
RateID(PK)
RateType eg Basic OT1 OT2 -allows for future
changes
BasicMultiplier eg 1.0 1.5 2.0

TblHrs
RecordID (PK)
EmployeeID (FK)
WeekID(FK)
RateID(FK) -allow Nulls
HolidayID(FK) -allow Nulls
HrsWorked

TblHoliday
HolidayID(PK)
LeaveType
LeaveNo

as before, if you post your e-mail on either thread, I'll be happy to post
dummy db with sample to data to aide in explanations.

Hope this helps,

TonyT..

"scubadiver" wrote:

Or possibly

tble_Wk
WeekID (PK)

tble_employee
WeekID (FK)
EmployeeID (PK)
FName
SName
Work
WorkStat
Operations
Manager

tble_Dept
DeptID (PK)
Dept
Subdept
CostCentre
Manager

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo



"scubadiver" wrote:

Earlier in the year I devised a database to hold working hours information
for members of staff. Because I didn't know any better I used a 1:m
relationship between two tables.

In time for use after Christmas, I have decided to redesign it. I am using
all the same fields but re-organising.

At the moment I have the following:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
WeekID
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

A potential alternative:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_wk
EmployeeID (FK)
DeptID (FK)
WeekID (PK)

tble_hrs
WeekID
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

Any alternatives, improvements?


Cheers!

sorry scuba, been awol for a few days,

i'll try to answer all posts in 1

The other thing I have noticed is that the holiday table has the primary key.
During each week, an employee can have more than one reason to be absent so
surely the holiday table should have the foreign key?

No, hours worked aren't made up of holidays, but some hours worked may be
holidays.

Another thing I have noticed with the design is that each employee can only
work for one subdepartment.

Ahh now, my assumption was that an employee worked for a sub-department in a
heirachichal structure, so does the employee work for one department, or can
they also work for more than one? really determines the structure.

The slight problem I have is that the hourly rate is in with the employee
info. In the current database, the rate is with the hours worked so if the
rate changes, it won't change the cost calculation for every record.

The main reason for putting it into the employee table is that the
employee's salary can be seen as one of their attributes, and may vary for
each employee. If all employee's now and forever in the future are going to
be paid the same hourly rate, then keeping it in the rate table is not a
problem.

Another point: in my current database, the rate multiplier is done in a
query. Any reason not to?

Yes, if the rate changes to let's say a 1.75 multiplyer for hours between
19.00 and 21.00 hours on a saturday, you only have to add an extra line in a
table rather than create a whole new query, granted, this depends how you
have coded the query and form designs as to whether a redisgn would be
necessary or not, but if you work now assuming it can change in the future
you will have a more robust database.

Let me know on the relationship between employee and department, and also
how a sub-department actaully relates to a department, and i'll try to make
time over the weekend to e-mail those designs.

TonyT..

"scubadiver" wrote:

Another point: in my current database, the rate multiplier is done in a
query. Any reason not to?

thanks

"scubadiver" wrote:

Earlier in the year I devised a database to hold working hours information
for members of staff. Because I didn't know any better I used a 1:m
relationship between two tables.

In time for use after Christmas, I have decided to redesign it. I am using
all the same fields but re-organising.

At the moment I have the following:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
WeekID
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

A potential alternative:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_wk
EmployeeID (FK)
DeptID (FK)
WeekID (PK)

tble_hrs
WeekID
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

Any alternatives, improvements?


Cheers!

The other thing I have noticed is that the holiday table has the primary key.
During each week, an employee can have more than one reason to be absent so
surely the holiday table should have the foreign key?

No, hours worked aren't made up of holidays, but some hours worked may be
holidays.

What I mean is that each hours record can be associated with more than one
type of absence.

In my current database absence information is included in the same table as
the hours information. If I want to calculate productivity based on actual
hours worked then I need to subtract the cost of any paid holiday from the
total hours so I get the actual cost worked. Make sense?

Another thing I have noticed with the design is that each employee can only
work for one subdepartment.

Ahh now, my assumption was that an employee worked for a sub-department in a
heirachichal structure, so does the employee work for one department, or can
they also work for more than one? really determines the structure.

Each employee does work for one department but can work for others *when
required*. In my current database I have the department and subdepartment in
the employee information *and* in the working hours subform.

If I record both, it helps in understanding how efficient the departments
are in terms of who is working where at any one time and how it may provide
information on improving efficiency.

For example, looking at the trends of my current database, I have already
found out that one department has a constantly high turnover of extra temp
staff every week taken from other departments. I also found out that this
particular department is consistently making the most mistakes in the enquiry
process!

I hope this makes sense! If there is a better method let me know.

The slight problem I have is that the hourly rate is in with the employee
info. In the current database, the rate is with the hours worked so if the
rate changes, it won't change the cost calculation for every record.

The main reason for putting it into the employee table is that the
employee's salary can be seen as one of their attributes, and may vary for
each employee. If all employee's now and forever in the future are going to
be paid the same hourly rate, then keeping it in the rate table is not a
problem.

What happens if a member of staff gets a pay rise halfway through the year?

Another point: in my current database, the rate multiplier is done in a
query. Any reason not to?

Yes, if the rate changes to let's say a 1.75 multiplyer for hours between
19.00 and 21.00 hours on a saturday, you only have to add an extra line in a
table rather than create a whole new query, granted, this depends how you
have coded the query and form designs as to whether a redisgn would be
necessary or not, but if you work now assuming it can change in the future
you will have a more robust database.

ok, that makes sense.

TonyT..

"scubadiver" wrote:

Another point: in my current database, the rate multiplier is done in a
query. Any reason not to?

thanks

"scubadiver" wrote:

Earlier in the year I devised a database to hold working hours information
for members of staff. Because I didn't know any better I used a 1:m
relationship between two tables.

In time for use after Christmas, I have decided to redesign it. I am using
all the same fields but re-organising.

At the moment I have the following:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_hrs
EmployeeID (FK)
DeptID (FK)
RecordID (PK)
WeekID
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

A potential alternative:

tble_employee
EmployeeID (PK)
FName
SName
Work (combobox)
Workstat (combobox)
Operations (checkbox)
Manager (checkbox)

tble_Dept
DeptID (PK)
Dept (Combobox)
SubDept (Combobox)
CostCentre (combobox)
Manager (Combobox)

tble_wk
EmployeeID (FK)
DeptID (FK)
WeekID (PK)

tble_hrs
WeekID
RecordID (PK)
Rate
Basic
CntrctHrs
OT1
thlfhrs
OT2
dblehrs

tble_hol
RecordID (FK)
LeaveType
LeaveNo

Any alternatives, improvements?


Cheers!