Model for assessment for employee

sam2929
sam2929 used Ask the Experts™
on
Hi,
Want to create a data model or tables for employee assessment in oracle

have table employee like below
empliid firstname last name

Then want to ask question to employee

question table
1) how many years of service
2) level of satisfaction  with manager
etc

And based on the answers they will give total assessment score

They also want the database to be dynamic so they can add question every quarter or so

They also want to keep versions of assessment for employee

please let me know what all tables i need and also how to do version design
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Russ SuterSenior Software Developer

Commented:
There are many ways to handle something like this. One normalized way I could think of would require 3 tables. The first is the employee table you already have. The second is a table of questions to be asked. The third is a table which contains the answers to those questions.

The question table should include an ID column, a DATETIME column indicating when the question was added, and a column containing the actual question. You may also want to add more columns indicating if the question needs to be asked again or if it is no longer valid, stuff like that.

The answer table should contain at least 4 columns.
Id of the employee who answered the question.
Id of the question being answered.
Date and time of the answer.
The actual answer.

That should get you started.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
You are more likely to want at least three tables. An employee table, a question table (which may need breaking down further depending upon whether or not you want to specify question types, have possible multi-choice answers etc) and an employee answer table, which relates the two. You may even need a "questionnaire" table, which relates possible questions to a questionnaire and then links to multiple questionnaire events, if this is to be used for regular reviews.

So, firstly, define the problem. Don't worry about table designs yet. Record the entities as you see it and the attributes that belong to those entities. Then normalise. Once you have done this, you will have your basic design.

There is no right or wrong answer here. Jumping straight into table design before considering the problem is likely to lead to a poor design that requires early revision and does not match the problem domain.

Author

Commented:
Requirements are clear I am attaching documents
2917AF20-FC6A-4254-B879-5CF5CF386E7.jpeg
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I'll throw out a different design that makes purist database folks uneasy:
Store the questions and answers in XML or JSON with a version identifier.

The question table could be 1 column with XPATH or similar indexes for constraints.  I would probably go with something like:
version number (PK)
survey XMLTYPE (or JSON)

Possibly a couple of dates for start and end of when this survey was used.


Employee _response table goes something like:
emplid number
survey_date date
responses XMLTYPE (or JSON)  This possibly includes the questions and answers as well as the survey version (PK) from the question table.

>>Requirements are clear

To you, sure but not really to us.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
Requirements are clear I am attaching documents

Your attached document still leaves questions unanswered to me, and I included some of them in my initial comment. You have two questions on your sheet of paper, but state that more may be added in your question. I don't see reference to this on the attached "requirements specification". Depending upon what you want to do with the data, you could come up with different designs.

Russ has certainly given a good starting point above and slightwv has suggested an alternative approach.

Author

Commented:
This is what i am thinking for questions and anwser .

Now how can i fit versions of questions here.


CREATE TABLE `question` (
  `QUESTION_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `QUESTION_INDEX` varchar(128) NOT NULL,
  `LANG` varchar(26) DEFAULT NULL,
  `QUESTION_LABEL` varchar(4000) DEFAULT NULL,
  `IS_OPTION` tinyint(1) DEFAULT '1',
  `IS_VALUE` tinyint(1) DEFAULT '1',
  `IS_COMMENT` tinyint(1) DEFAULT '1',
  `IS_MANDATORY` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`QUESTION_ID`,`QUESTION_INDEX`),
  UNIQUE KEY `QUESTION_INDEX_UNIQUE` (`QUESTION_INDEX`)
)




CREATE TABLE `answer` (
  `ANSWER_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `QUESTION_INDEX` varchar(128) NOT NULL,(FK from questions)
  `OPTIONANSWER_ID` bigint(20) unsigned DEFAULT NULL,
  `VALUE_ANSWER` varchar(4000) DEFAULT NULL,
  `COMMENTS` varchar(4000) DEFAULT NULL,
   `EMPLOYEE_ID` bigint(20) unsigned DEFAULT NULL (FK from empolyee)
  PRIMARY KEY (`CAMPAIGNANSWER_ID`),
 )
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>This is what i am thinking for questions and answer .

I think that is pretty over-simplified.

Without column comments it is difficult to tell what is what.

I'm not a fan of surrogate keys just for the sake of having them.  What is the purpose of QUESTION_INDEX versus QUESTION_ID?

Can a question not be a value question and allow for comments?

I assume IS_OPTION, IS_VALUE and IS_COMMENT are for question "types"?  I would replace them with a question_type column and a new lookup table.

If IS_OPTION does mean a pick-list/multiple-choice type question, where are you storing those values?

>>Now how can i fit versions of questions here.

I create versions as decimals so I can have 1.0, 1.1, 2.4, etc.

For you, it might be years and/or quarters:  2018-1 or FY18-2.  All depends on how often they can change.

You also need to account for things like the question not changing but options/values changing.

For example a question is age_range.  This year it is 0-21 and next year it is 0-18 and 19-21.

Then there are the dates we have mentioned:  Date the question was active date it was made in-active.  Then you need to account for if/when one was brought back.

You also have the dates of the review.

You never mentioned if this is a supervisor review of self-assessment or both.  You need to account  for those.

This isn't everything but just things off the top of my head.  The model needs to account for future possibilities that might come up based on research of other employee assessment models?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial