Model for assessment for employee

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

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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russ SuterCommented:
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 DirectorCommented:
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.
sam2929Author Commented:
Requirements are clear I am attaching documents
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) 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 DirectorCommented:
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.
sam2929Author 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,
  `IS_OPTION` tinyint(1) DEFAULT '1',
  `IS_VALUE` tinyint(1) DEFAULT '1',
  `IS_COMMENT` tinyint(1) DEFAULT '1',

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)
slightwv (䄆 Netminder) 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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.