G
Guest
With this table setup, you could have someone work for any
department/subdepartment, for any amount of time, with any type of factor
(straight time, double time) applied to their salary. Since you don't want
to keep track of starting/ending times, you will have to manually calculate
the times worked for anyone, and enter those yourself. You will also have to
manually choose whether someone is working straight time, time-and-a-half, or
double time.
tbl_Personnel (all personnel, whether temporary or permanent)
PersonnelID (PK)
PersonnelName (don't make the name the primary key)
PersonnelStatus (temporary, permanent, inactive)
PersonnelRate (hourly salary)
tbl_Departments
DepartmentID (PK)
DepartmentName
tbl_Subdepartments
SubdepartmentID (PK)
SubdepartmentName
tbl_CostCentres (this table combines various departments and subdepartments)
CostCentresID (PK)
DepartmentID (FK)
SubdepartmentID (FK)
tbl_HoursWorked
HoursWorkedID (PK)
PersonnelID (FK) (who has worked)
CostCentreID (FK) (who is paying for the work)
DateWorked (this could be for either daily or weekly entry) (when did they
work)
HoursWorked (how long did they work)
HoursWorkedTypeID (FK) (what type of work was it)
tbl_HoursWorkedTypes (depending on type of work, there is a factor that is
used to determine the total salary for that time)
HoursWorkedTypeID (PK)
HoursWorkedTypeText (such as standard, time-and-a-half, double)
HoursWorkedTypeFactor (such as 1.0, 1.5, 2.0, for multiplying the base salary)
department/subdepartment, for any amount of time, with any type of factor
(straight time, double time) applied to their salary. Since you don't want
to keep track of starting/ending times, you will have to manually calculate
the times worked for anyone, and enter those yourself. You will also have to
manually choose whether someone is working straight time, time-and-a-half, or
double time.
tbl_Personnel (all personnel, whether temporary or permanent)
PersonnelID (PK)
PersonnelName (don't make the name the primary key)
PersonnelStatus (temporary, permanent, inactive)
PersonnelRate (hourly salary)
tbl_Departments
DepartmentID (PK)
DepartmentName
tbl_Subdepartments
SubdepartmentID (PK)
SubdepartmentName
tbl_CostCentres (this table combines various departments and subdepartments)
CostCentresID (PK)
DepartmentID (FK)
SubdepartmentID (FK)
tbl_HoursWorked
HoursWorkedID (PK)
PersonnelID (FK) (who has worked)
CostCentreID (FK) (who is paying for the work)
DateWorked (this could be for either daily or weekly entry) (when did they
work)
HoursWorked (how long did they work)
HoursWorkedTypeID (FK) (what type of work was it)
tbl_HoursWorkedTypes (depending on type of work, there is a factor that is
used to determine the total salary for that time)
HoursWorkedTypeID (PK)
HoursWorkedTypeText (such as standard, time-and-a-half, double)
HoursWorkedTypeFactor (such as 1.0, 1.5, 2.0, for multiplying the base salary)