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?

 

[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?

 

[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