Working Hours database

hi scuba,

sorry again for the delay in response, workload a bit heavy at the moment,

sounds to me like you are pretty much their with your design now, you have
valid reasons for what at first appeared duplications in data storage,
hopefully with a few tweaks you have the best setup - maybe one last revised
post of table structure is it stands now?

TonyT..

"scubadiver" wrote:



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!

tble_dept
DeptID (PK)
Dept
CostCentre
Operations
Manager

tble_subdept
deptID (FK)
SubdeptID (PK)
Subdept
Manager

tble_employee
subdeptID
EmployeeID (PK)
Fname
Sname
Work
Workstat

tble_week
weekID (PK)
Date

tble_rate
RateID (PK)
type
Multiplier

tble_hrs
RecordID (PK)
EmployeeID (FK)
WeekID (FK)
RateID (FK)
Hrsworked

tble_hol
RecordID (FK)
type
leaveno




"TonyT" wrote:

hi scuba,

sorry again for the delay in response, workload a bit heavy at the moment,

sounds to me like you are pretty much their with your design now, you have
valid reasons for what at first appeared duplications in data storage,
hopefully with a few tweaks you have the best setup - maybe one last revised
post of table structure is it stands now?

TonyT..

"scubadiver" wrote:



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!

hi again,

based on what I now know, looks fine to me.

TonyT..

"scubadiver" wrote:

tble_dept
DeptID (PK)
Dept
CostCentre
Operations
Manager

tble_subdept
deptID (FK)
SubdeptID (PK)
Subdept
Manager

tble_employee
subdeptID
EmployeeID (PK)
Fname
Sname
Work
Workstat

tble_week
weekID (PK)
Date

tble_rate
RateID (PK)
type
Multiplier

tble_hrs
RecordID (PK)
EmployeeID (FK)
WeekID (FK)
RateID (FK)
Hrsworked

tble_hol
RecordID (FK)
type
leaveno




"TonyT" wrote:

hi scuba,

sorry again for the delay in response, workload a bit heavy at the moment,

sounds to me like you are pretty much their with your design now, you have
valid reasons for what at first appeared duplications in data storage,
hopefully with a few tweaks you have the best setup - maybe one last revised
post of table structure is it stands now?

TonyT..

"scubadiver" wrote:



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!

hmm,

From this design basic hours and overtime will be separate records so
attaching absence and holiday to records is not very feasible.

As well as the current tables and relationships set out below I have created
another junction table called "tble_absence" linked from the employeeID and
WeekID tables so I can record the leave type and number of days for each week:

tble_week
WeekID (PK)
Date

EmployeeID (FK)
WeekID (FK)
Leavetype
LeaveNum

I have created a form and subform.

Annual Leave - EmployeeID, Fname, Sname
Annual Leave subform - Date, LeaveType, LeaveNum

What confuses me is that the "date" field is a text box. I know I am
sounding ignorant but it has been my impression that information is picked up
from a source and placed into a field. The "Leavetype" field will pick
information from a list of values.

If I type a date into a text box whose control source is the [date] field in
"tble_week", how does it relate back to that field if all the weeks are
already stored?

Or am I missing something?


--
How many buildings collapsed on 9/11?

I can tell you the answer isn''t "2" !!


"TonyT" wrote:

hi again,

based on what I now know, looks fine to me.

TonyT..

"scubadiver" wrote:

tble_dept
DeptID (PK)
Dept
CostCentre
Operations
Manager

tble_subdept
deptID (FK)
SubdeptID (PK)
Subdept
Manager

tble_employee
subdeptID
EmployeeID (PK)
Fname
Sname
Work
Workstat

tble_week
weekID (PK)
Date

tble_rate
RateID (PK)
type
Multiplier

tble_hrs
RecordID (PK)
EmployeeID (FK)
WeekID (FK)
RateID (FK)
Hrsworked

tble_hol
RecordID (FK)
type
leaveno




"TonyT" wrote:

hi scuba,

sorry again for the delay in response, workload a bit heavy at the moment,

sounds to me like you are pretty much their with your design now, you have
valid reasons for what at first appeared duplications in data storage,
hopefully with a few tweaks you have the best setup - maybe one last revised
post of table structure is it stands now?

TonyT..

"scubadiver" wrote:



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!

Related to this is how I record the date in "tble_hrs".


"TonyT" wrote:

hi again,

based on what I now know, looks fine to me.

TonyT..

"scubadiver" wrote:

tble_dept
DeptID (PK)
Dept
CostCentre
Operations
Manager

tble_subdept
deptID (FK)
SubdeptID (PK)
Subdept
Manager

tble_employee
subdeptID
EmployeeID (PK)
Fname
Sname
Work
Workstat

tble_week
weekID (PK)
Date

tble_rate
RateID (PK)
type
Multiplier

tble_hrs
RecordID (PK)
EmployeeID (FK)
WeekID (FK)
RateID (FK)
Hrsworked

tble_hol
RecordID (FK)
type
leaveno




"TonyT" wrote:

hi scuba,

sorry again for the delay in response, workload a bit heavy at the moment,

sounds to me like you are pretty much their with your design now, you have
valid reasons for what at first appeared duplications in data storage,
hopefully with a few tweaks you have the best setup - maybe one last revised
post of table structure is it stands now?

TonyT..

"scubadiver" wrote:



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!

Hi,

If I were you, I wouldn't have separate tables for each kind of
holiday/leave etc, I would lump them all in to your tble_rate, adding entries
with 0 or 1 multiplyers and the description holiday/absence etc, then store
the data as if it were just another paid day/week in table_hours.

This way you can calculate much more easily total leave vs holiday vs
absences, there is no reason why you can't have multiple records per employee
per week. A query will soon give your totals or a crosstab query can show
hours breakdown by rate by week.

Your date text field should really be a combobox, displaying date but
actually recording the bound column of WeekID.

TonyT..

"scubadiver" wrote:

hmm,

From this design basic hours and overtime will be separate records so
attaching absence and holiday to records is not very feasible.

As well as the current tables and relationships set out below I have created
another junction table called "tble_absence" linked from the employeeID and
WeekID tables so I can record the leave type and number of days for each week:

tble_week
WeekID (PK)
Date

EmployeeID (FK)
WeekID (FK)
Leavetype
LeaveNum

I have created a form and subform.

Annual Leave - EmployeeID, Fname, Sname
Annual Leave subform - Date, LeaveType, LeaveNum

What confuses me is that the "date" field is a text box. I know I am
sounding ignorant but it has been my impression that information is picked up
from a source and placed into a field. The "Leavetype" field will pick
information from a list of values.

If I type a date into a text box whose control source is the [date] field in
"tble_week", how does it relate back to that field if all the weeks are
already stored?

Or am I missing something?


--
How many buildings collapsed on 9/11?

I can tell you the answer isn''t "2" !!


"TonyT" wrote:

hi again,

based on what I now know, looks fine to me.

TonyT..

"scubadiver" wrote:

tble_dept
DeptID (PK)
Dept
CostCentre
Operations
Manager

tble_subdept
deptID (FK)
SubdeptID (PK)
Subdept
Manager

tble_employee
subdeptID
EmployeeID (PK)
Fname
Sname
Work
Workstat

tble_week
weekID (PK)
Date

tble_rate
RateID (PK)
type
Multiplier

tble_hrs
RecordID (PK)
EmployeeID (FK)
WeekID (FK)
RateID (FK)
Hrsworked

tble_hol
RecordID (FK)
type
leaveno




"TonyT" wrote:

hi scuba,

sorry again for the delay in response, workload a bit heavy at the moment,

sounds to me like you are pretty much their with your design now, you have
valid reasons for what at first appeared duplications in data storage,
hopefully with a few tweaks you have the best setup - maybe one last revised
post of table structure is it stands now?

TonyT..

"scubadiver" wrote:



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!

Sounds to me like perhaps you should scrub the tbl_week and just have a
single date field in tbl_hrs, adding one entry per day per rate, otherwise
WeekID means nothing if you are actually storing values on a daily / part
weekly basis.

TonyT..

"scubadiver" wrote:


Related to this is how I record the date in "tble_hrs".


"TonyT" wrote:

hi again,

based on what I now know, looks fine to me.

TonyT..

"scubadiver" wrote:

tble_dept
DeptID (PK)
Dept
CostCentre
Operations
Manager

tble_subdept
deptID (FK)
SubdeptID (PK)
Subdept
Manager

tble_employee
subdeptID
EmployeeID (PK)
Fname
Sname
Work
Workstat

tble_week
weekID (PK)
Date

tble_rate
RateID (PK)
type
Multiplier

tble_hrs
RecordID (PK)
EmployeeID (FK)
WeekID (FK)
RateID (FK)
Hrsworked

tble_hol
RecordID (FK)
type
leaveno




"TonyT" wrote:

hi scuba,

sorry again for the delay in response, workload a bit heavy at the moment,

sounds to me like you are pretty much their with your design now, you have
valid reasons for what at first appeared duplications in data storage,
hopefully with a few tweaks you have the best setup - maybe one last revised
post of table structure is it stands now?

TonyT..

"scubadiver" wrote:



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!

My current database stores total hours on a weekly basis (as opposed to every
day) and I do have a record clone code that automatically adds on 7 days for
the next record.

"TonyT" wrote:

Sounds to me like perhaps you should scrub the tbl_week and just have a
single date field in tbl_hrs, adding one entry per day per rate, otherwise
WeekID means nothing if you are actually storing values on a daily / part
weekly basis.

TonyT..

"scubadiver" wrote:


Related to this is how I record the date in "tble_hrs".


"TonyT" wrote:

hi again,

based on what I now know, looks fine to me.

TonyT..

"scubadiver" wrote:

tble_dept
DeptID (PK)
Dept
CostCentre
Operations
Manager

tble_subdept
deptID (FK)
SubdeptID (PK)
Subdept
Manager

tble_employee
subdeptID
EmployeeID (PK)
Fname
Sname
Work
Workstat

tble_week
weekID (PK)
Date

tble_rate
RateID (PK)
type
Multiplier

tble_hrs
RecordID (PK)
EmployeeID (FK)
WeekID (FK)
RateID (FK)
Hrsworked

tble_hol
RecordID (FK)
type
leaveno




"TonyT" wrote:

hi scuba,

sorry again for the delay in response, workload a bit heavy at the moment,

sounds to me like you are pretty much their with your design now, you have
valid reasons for what at first appeared duplications in data storage,
hopefully with a few tweaks you have the best setup - maybe one last revised
post of table structure is it stands now?

TonyT..

"scubadiver" wrote:



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!