Job Appraisal database setup

[ngan]

I need some guidance in setting up a database to house our employees annual
job appraisal review.

We currently have a Word doc that all the employees fill out for a
self-appraisal and there is another doc that the supervisor does for each
employee.

There is 10 categories and each category has about 8-10 things to appraisal
and each category would have a comment field. There is a scale of 1-5 for
each thing.

So we're talking about at least 100 fields.

How should the database be set up? We will want to do some stats based on
the scale and such.

Are there any sample dbs out there?

 

[Steve]

TblDepartment
Department
Department

TblEmployee *** includes supervisors
EmployeeID
EmployeeFirstName
EmployeeLastName
<Othe relevant employee fields>

TblAppraisalCategory
CategoryID
Category

TblCategoryItem
CategoryItemID
CategoryID
CategoryItem

TblAppraisalBY
AppraisalByID
AppraisalBy **self, supervisor

TblAppraisal
AppraisalID
EmployeeID
SupervisprID linked to appropriate EmployeeID in TblEmployee
AppraisalByID
CategoryID
CategoryItemID
Rating 1 t0 5

Steve
(e-mail address removed)

 

[Fred]

Actually that about 200 blanks per review x each of them completed by 2
people = 400 blanks. The mother of all reviews!

I'm assuming that you want to store reviews for multiple years per employee.


This is a rare case where a properly normalized DB can still have a zillion
fields. But with Access, a zillion can't be over 255.

Two linked tables, one with employees (PK = EmployeeIdNumber) and the other
with each instance of an annual review would be OK, except that the latter
table would have 400 fields. You could make the latter table one that
contains a record for each instance of a review complete by a person. Add a
field that indicates whether that instance was completed by the employee or
the supervisor.

This wil be unwieldy, but thats because you have a form with 200 blanks on
it, not because of poor DB structure. You could use the form design to
organize it.

You could avoid having that many fields in a table by dividing this at more
levels (such as an intermediate division by categories). But in my opinion,
that would make it more complicated for little or no reason/benefit.

 

[ngan]

Ok, I'm still digesting both of the above comments...

Here are a few things I left out that may change things:

1. Each of the items has to be rated. So they have to be displayed for the
user to see all at once, in case they want to pick which to do first. I
could have them see one category at a time so the page isn't so long.

2. Yes, each item would have rating from self and the supervisor.

3. the data will be in a sql 2005 database. so we wouldn't have the limit of
255 fields.

4. In terms of security, is it better to do the front end as a website and
have the data in a sql table? If I have an Access mdb, isn't it easy for
people to see the table and each other's data? Would there be alot of
security configuration to make it that secure?

Fred thought dividing up the categories into tables may not have much
benefit. Why's that? too confusing and hard to get stats?

Would keeping all the fields in one table be easy to get stats from?

Thanks!

Are there any sample dbs out there?

 

[Steve]

See comments below .......

Steve

Ok, I'm still digesting both of the above comments...

Here are a few things I left out that may change things:

1. Each of the items has to be rated. So they have to be displayed for
the
user to see all at once, in case they want to pick which to do first. I
could have them see one category at a time so the page isn't so long.

For your data entry form for emtering appraisals, you could have a combobox
in the header of the form that filters your form to a Category.

2. Yes, each item would have rating from self and the supervisor.

You could have a data entry form for entering appraisals for self and
another data entry form for entering appraisals by the supervisor. OR, you
could have two subforms on an unbound form where you would enter the self
appraisal in one subfom and enter the supervisor's appraisal in the other
subform. Doing this you would see both appraisals on the screen at the same
time.

3. the data will be in a sql 2005 database. so we wouldn't have the limit
of
255 fields.

No Comment.

4. In terms of security, is it better to do the front end as a website
and
have the data in a sql table? If I have an Access mdb, isn't it easy for
people to see the table and each other's data? Would there be alot of
security configuration to make it that secure?

No Comment.

Fred thought dividing up the categories into tables may not have much
benefit. Why's that? too confusing and hard to get stats?

Would keeping all the fields in one table be easy to get stats from?

With the suggested tables, you would get all your stats from a query that
included TblAppraisal and other appropriate tables. With a little code you
could specify and get whatever stats you want in one form or you could build
a separate query and form for each set of stats.

Steve

 

[Michael Gramelspacher]

Are there any sample dbs out there?

There is a database I did called Patient Assessments here:

http://www.psci.net/gramelsp/temp/Sample Access Databases for Access 2003.htm

It is just something to look at. I think it could be migrated to SQL 2005. I
really cannot say whether it will help you.

 

[Fred]

Hello Nquan

One or two notes on your question on my response.

99% of databases using over 255 fields need those because they are
mis-designed. But 99% isn't 100%.

The simplicity vs. sophistication question at each level of use is always a
question. (it comes to this only when both choices are sound) The answer
should be dictated by your situation. I've been running small (20-50
person) technical companies for 26 years (getting deeper in on databases is
my hobby/indulgence) and so I tend to be more biased towards "simple". The
other extreme is that some designers like it to be so complicated and poorly
documented that they are the only person who can touch it. Or, there are
folks who, because they are fully immersed in a technology to the point of
seeing "simple" as boring, and see sophistication as a goal rather than a
means to an end. But, again, this should be decided based on your particular
situation.

Sincerely,

Fred

 

[ngan]

Is there a litmus test to figure out which would fit my situation better?

 

[Fred]

Nothing cut and dry; I gave you my best guess before which was leaning
towards the simpler giant table method.

One new question is to what extent you are comfortable with understanding
and implementing the solution that Steve recommended. And, if anyone
besides you is going to be involved in maintaining it. the same question for
them.

 

[ngan]

Thanks to everyone for their opinions. I'll take them into consideration. I
am leaning towards the simpler solution because it isn't really mission
critical data.

What about the issue with having the FE in Access vs website (simple asp) in
regards to security?

 

[Fred]

Regarding USING the data (reports, summaries etc.) I think that an Access
front end would let you do a lot more with a lot less work / learning curves.


Regarding inputting the data, I guess "security" coverst two categories:

Limiting access to confidential / managerial data while still giving access
for input. I think that this can be done with either front end

Security from hackers and other ill intentioned people. I'd have to plead
ignorance on this one.

Sincerely,

Fred