Design assistance - Employee history

[Rob]

Hi all,

I have a question about a table and relationship design. I am
building a Task management system for my current workplace (well an
upgrade of an older version). I think that I have the tables well
developed and they seem to be working well for the actual task
management. Each task is assigned to a position rather than a person
as we have a high turn over of staff. What I need to track is what
people have been assigned to a particular position.

The relationship between our staff and positions are a M:M I think as
a staff member can hold different positions at different times (or in
rare cases at the same time). This is how I currently have the
relationship setup:

tblStaff - info about the person (PK - staffID)
tblPosition - info about the position (PK - positionID)
tblStaffPositionJoin - contains staffID, positionID, StartDate and
EndDate

This seems to be working, however to satisfy business rules, before
assigning somebody to a new position I check whether the position is
filled and if the user confirms the change I set the end date and then
add the new staff member. The problem is that I have a feeling that I
am overcomplicating the struture here and it could be done better. Is
there an easier way of doing this (I am sure it is a common
functionality of personnel dbs) or is this OK?

Cheers
Rob

 

[PWaddington6903]

Hi all,

I have a question about a table and relationship design.  I am
building a Task management system for my current workplace (well an
upgrade of an older version). I think that I have the tables well
developed and they seem to be working well for the actual task
management.  Each task is assigned to a position rather than a person
as we have a high turn over of staff. What I need to track is what
people have been assigned to a particular position.

The relationship between our staff and positions are a M:M I think as
a staff member can hold different positions at different times (or in
rare cases at the same time). This is how I currently have the
relationship setup:

tblStaff - info about the person (PK - staffID)
tblPosition - info about the position (PK - positionID)
tblStaffPositionJoin - contains staffID, positionID, StartDate and
EndDate

This seems to be working, however to satisfy business rules, before
assigning somebody to a new position I check whether the position is
filled and if the user confirms the change I set the end date and then
add the new staff member.  The problem is that I have a feeling that I
am overcomplicating the struture here and it could be done better.  Is
there an easier way of doing this (I am sure it is a common
functionality of personnel dbs) or is this OK?

Cheers
Rob

Sounds good. I think the structure you have is about the minimum for
what you describe. It sounds like there can never be two or more
people in the same position at the same time - hence the automatic End
Date. Also, you have a Start Date and End Date because it may be true
(I am guessing) that a position may go unfilled, so there may be an
End Date with no following Start Date with a new person. I would
suggest that you do not have the combination of PositionID and StaffID
on the Join table as any kind of unique key, just incase someone
leaves a position and then comes back to the same position, with a
different Start Date and End Date.

Looks like you have it covered.

 

[Rob]

HI,

Thanks for your rapid response and the tips. I initially had
positionID and startID as the PK, however I have now added a new PK to
allow someone to come back into the same position (I dont think it has
happened yet - but it most certainly could!) I was questioning the
need for an EndDate (just using StartDate) as a way of keeping a
history of employees, however I think I will keep it based on your
comments re the unfilled position. I could also use it as a trigger
to check tasks that have no owner (ie position unfilled!)

Thanks again,
Cheers
Rob

 

[Michael Gramelspacher]

HI,

Thanks for your rapid response and the tips. I initially had
positionID and startID as the PK, however I have now added a new PK to
allow someone to come back into the same position (I dont think it has
happened yet - but it most certainly could!) I was questioning the
need for an EndDate (just using StartDate) as a way of keeping a
history of employees, however I think I will keep it based on your
comments re the unfilled position. I could also use it as a trigger
to check tasks that have no owner (ie position unfilled!)

Thanks again,
Cheers
Rob

I would have Primary Key (StaffID, PositionID, StartDate), but even that is not
enough. You need a check for overlaps so that a StaffID cannot be assigned to a
PositionID it already is assigned to. I do not know what your new PK is, but I
do not believe any primary key can by itself solve this problem.

 

[Rob]

HI Michael,

I had thought of that and at the moment I am trying to stop this
condition occurring by error trapping at the user interface (checking
for a StaffID and PositionID combo). I agree that there is little I
can do to stop the underlying table being robust using just PKs.

This is the way I am currently thinking (pseudocode)

AddStaffToPosition
Check StaffID,PostionID in tblStaffPositionJoin
If Exists
IsNull(EndDate)
Error - User already exists.
Else
If EndDate > Now
Error - Corrupt data check that Position
Else
Assign the position to the staff member
Else
Assign the position to the staff member

---------------

It has been a long time since I have done any prorgamming so I am
enjoying the return and getting frustrated at the same time due to the
time taken to remember simple techniques!

Cheers
Rob

 

[Michael Gramelspacher]

HI Michael,

I had thought of that and at the moment I am trying to stop this
condition occurring by error trapping at the user interface (checking
for a StaffID and PositionID combo). I agree that there is little I
can do to stop the underlying table being robust using just PKs.

This is the way I am currently thinking (pseudocode)

AddStaffToPosition
Check StaffID,PostionID in tblStaffPositionJoin
If Exists
IsNull(EndDate)
Error - User already exists.
Else
If EndDate > Now
Error - Corrupt data check that Position
Else
Assign the position to the staff member
Else
Assign the position to the staff member

---------------

It has been a long time since I have done any prorgamming so I am
enjoying the return and getting frustrated at the same time due to the
time taken to remember simple techniques!

Cheers
Rob

You can try this. Creae a new blank database. create a new module and copy
this subprogran into it. Save. In Immediate Window type call CreateTables and
then press enter key.

Sub CreateTables()

With CurrentProject.Connection

.Execute _
"CREATE TABLE Staff " & _
"(StaffID VARCHAR (10) NOT NULL PRIMARY KEY);"

.Execute _
"CREATE TABLE Positions " & _
"(PositionID VARCHAR (10) NOT NULL PRIMARY KEY);"

.Execute _
"CREATE TABLE StaffPositions " & _
"(StaffID VARCHAR (10) NOT NULL " & _
"REFERENCES Staff (StaffID), " & _
"PositionID VARCHAR (10) NOT NULL " & _
"REFERENCES Positions (PositionID), " & _
"StartDate DATETIME NOT NULL, " & _
"StopDate DATETIME NULL, " & _
"PRIMARY KEY (StaffID, PositionID,StartDate));"

.Execute "ALTER TABLE StaffPositions ADD CONSTRAINT" & _
" Overlapping_periods_not_allowed CHECK (NOT EXISTS( SELECT *" _
& " FROM StaffPositions AS S1 WHERE 1 < (SELECT COUNT(*)" _
& " FROM StaffPositions AS S2 WHERE S1.StaffID = S2.StaffID" _
& " AND S1.PositionID = S2.PositionID" _
& " AND S1.StartDate <= S2.StopDate" _
& " AND S2.StartDate <= NZ(S1.StopDate,'3000-01-01'))));"

End With

End Sub

 

[Rob]

Hi Michael,

Thanks for that. I have never used this technique before. It has now
made me consider other ways of trapping errors within the database.
Is there a way of viewing the constraint conditions within the Table
design view, or does the table have to be built programatically.

I am now about to test the error from a form and see what error number
Access will return for me.

Thanks and I really appreciate your help,

Cheers
Rob

 

[Michael Gramelspacher]

Hi Michael,

Thanks for that. I have never used this technique before. It has now
made me consider other ways of trapping errors within the database.
Is there a way of viewing the constraint conditions within the Table
design view, or does the table have to be built programatically.

I am now about to test the error from a form and see what error number
Access will return for me.

Thanks and I really appreciate your help,

Cheers
Rob

There is a down side to using DDL. Access has never updated table design to
accomodate some features of Jet 4.0. This is even though these features go back
to Access 2000.

In order to drop table StaffPositions you will need to drop the constraint
first.

CurrentProject.Connection.Execute "ALTER TABLE StaffPositions " _
& "DROP CONSTRAINT Overlapping_periods_not_allowed"

If you import the table into another database, the constraint will be lost.

Most persons would also check for overlaps in their form code even though there
is a table constraint.