Changing Paperwork for Employees- Structure Help

[bgmiller]

I have an HR database that lists all Employees. Different types of employees require different kinds of paperwork to be turned in. And like any organization, additional paperwork and requirements are added and subtracted constantly.

I currently have all the Employees in one table by ID. Each employee has 1 paperwork record in a paperwork table with all the paperworkdocuments as their own T/F field. I keep yet another database that links what paperwork is required for each employee type. As you can imagine this is pain to keep updating for changing paperwork.

Here's new structure I thought about, although I'm not sure this is most efficient:

tblEmployee
- EmployeeID
- Employee Type
- Name, etc...

tblPaperworkOptions
- PaperworkOptionID
- Description
- EmployeeType1Required (T/F)
- EmployeeType2Required (T/F)
- EmployeeType3Required (T/F)
- EmployeeType4Required (T/F)

I would then list all paperwork as separate records in the tblPaperworkOptions and put a check next to their EmployeeType if that document was required.

tblPaperwork
- tblPaperworkID
- tblPaperworkOptionID
- tblEmployeeID

- this table would be used to track if they actually have the paperwork turned in. It would create a separate record for each employee for each document turned in by them.

I can then compare the records in tblPaperwork with tblPaperworkOptions to see if they have all the required paperwork based on their employee type.

The thing I'm concerned about is the number of records I will need for tblPaperwork. Also, how to list all the paperwork that is required for an employee on a form so that HR can check off what they have. That check off will create the record in tblPaperwork.

Is there a better way to do this?

Thanks
Brian

 

[Wolfryder88]

Your structure looks good to me. At first I questioned the need for the PaperworkOptionID in the tblPaperwork, but then re-read your problem - changing paperwork requirements. Makes sense.

You're right, you'll end up with lots of records in tblPaperwork. You'll need to be comfortable with the fact that you'll only be able to view paperwork records meaningfully in your queries and forms, as opposed to the table view (if that's a concern).

-kevin

 

[Fornatian]

There seems to be some duplication in fields of similar character, what do these refer to:

- EmployeeType1Required (T/F)
- EmployeeType2Required (T/F)
- EmployeeType3Required (T/F)
- EmployeeType4Required (T/F)

On first glance, if these are exclusive you are better with a lookup field named EmployeeType joined to a table with 1 field called Type with entries Type1, Type2. Type3, Type 4. After all, what happens when Type 5 is added. Do you want to add it in one place as a field entry or have to redesign your db. My first glance may be wrong though.

I wouldn't worry too much about getting too many records, you already have too many records, that's why you need to store them in a database, otherwise you'd have an A4 folder

Size-wise, access had a max capacity of 1gb independently and is system capacity limited when linked to other db's.

 

[Pat Hartman]

Let me reinforce Fornatian suggestion that tblPaperworkOptions needs to be normalized. I would change the structure to:

tblEmployee
- EmployeeID
- EmployeeType
- Name, etc...

tblEmployeePaperwork
- EmployeeID
- PaperworkID
- CompletedDate
- etc.

tblPaperworkEmployeeType
- PaperworkID
- EmployeeTypeID

tblPaperwork
- PaperworkID
- PaperworkDesc

tblEmployeeType
- EmployeeTypeID
- EmployeeTypeDesc

This structure allows you to define employee types (tblEmployeeType), paperwork (tblPaperwork), and then relate those two entities to each other with a relation table (tblPaperworkEmployeeType). This constitutes your definition of paperwork the the types of employee who are required to complete it. The final piece is to connect the paperwork to a specific employee so that you can record compliance. That is done with tblEmployeePaperwork.

The most difficult part of this process will be to define how rows get added to tblEmployeePaperwork. You can allow users to add these rows manually but that is fraught with danger. I would use several things to trigger the automatic adding of these rows.
1. The AfterUpdate event of the form when either a new employee record was added to the table or when the EmployeeType was changed.
2. When a new required document is added to tblPaperworkEmployeeType, the requirement record could also be added to tblEmployeePaperwork.
3. When the EmployeeType is changed, if the new type does not require the same set of paperwork, the old no longer required documents need to be either deleted or flagged as not required and the new requirements need to be added.

I realize that I have outlined some seemingly difficult logic. However, you always needed to consider these problems and your current structure does not allow you to accomodate these types of changes. If you adopt a normalized structure similar to what I have suggested, you will be well placed to actually accomodate the HR business rules.

 

[bgmiller]

THanks.
I discovered the Problem with the multiple EmployeeTypes in tblPaperwork when making some moch-up tables. I also added a date field like Pat.... we must have been synched!

I ended up with:
tblEmployee - all employees
- EmployeeID
- EmployeeTypeID
- etc...

tblEmployeeTypeOptions- all employee Types
- EmployeeTypeID
- EmployeeTypeDesc

tblPaperworkOptions- lists all the paperwork options
- PaperworkOptionsID
- Description

tblPaperwork - connectes paperwork option to employee w date
- PaperworkID
- EmpoloyeeID
- PaperworkOptionsID
- Date

tblPaperworkRequired - sets paperwork options required for emp type
- PaperworkRequiredID
- PaperworkOptionsID
- EmployeeTypeID

I created 2 Forms...
Required Manager
Has EmployeeType Filter (comboBox) at top
A List of Paperwork options on the left and Required Paperwork list on right with Add/Remove buttons inbetween lists.

EmployeePaperwork
Lists all required paperwork for employee on left, lists all completed paperwork for employee on right with Add/Remove buttons inbetween lists.
To make sure they have all their paperwork in, I'll can do a rowcount on the required box's sql.. !>0

It works Great!

Finally no more modifying fields all the time!