RE: Working Hours database

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!
.

Relevant Pages

  • RE: Working Hours database
    ... SubdeptID ... RecordID ... an employee can have more than one reason to be absent so ... Workstat (combobox) ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Working Hours database
    ... DeptID ... RecordID ... an employee can have more than one reason to be absent so ... Workstat (combobox) ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Working Hours database
    ... an employee can have more than one reason to be absent so ... In my current database absence information is included in the same table as ... Workstat (combobox) ... RecordID ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Working Hours database
    ... My current database stores total hours on a weekly basis (as opposed to every ... SubdeptID ... an employee can have more than one reason to be absent so ... Workstat (combobox) ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Working Hours database
    ... The other thing I have noticed is that the holiday table has the primary key. ... an employee can have more than one reason to be absent so ... Workstat (combobox) ... RecordID ...
    (microsoft.public.access.tablesdbdesign)