Storing variable number of values of something in a database

Question

I'm developing a QA web-app which will have some points to evaluated assigned to one of the following Categories.

• Call management
• Technical skills
• Ticket management

As this aren't likely to change it's not worth making them dynamic but the worst point is that points are like to.

First I had a table of 'quality' which had a column for each point but then requisites changed and I'm kinda blocked.

I have to store "evaluations" that have all points with their values but maybe, in the future, those points will change.

I thought that in the quality table I could make some kind of string that have something like that

1=1|2=1|3=2

Where you have sets of ID of point and punctuation of that given value.

Can someone point me to a better method to do that?

Answer 1

This table is not normalized. It violates 1st Normal Form (1NF):

Evaluation
----------------------------------------
EvaluationId | List Of point=punctuation
   1         |   1=1|2=1|3=2
   2         |   1=5|2=6|3=7

You can read more about Database Normalization basics. The table could be normalized as:

Evaluation
-------------
EvaluationId 
   1         
   2         

Quality
---------------------------------------
EvaluationId | Point | Punctuation
   1         |   1   |   1   
   1         |   2   |   1
   1         |   3   |   2 
   2         |   1   |   5  
   2         |   2   |   6
   2         |   3   |   7

Answer 2

As mentioned many times here on SO, NEVER PUT MORE THAN ONE VALUE INTO A DB FIELD, IF YOU WANT TO ACCESS THEM SEPERATELY.

So I suggest to have 2 additional tables:

CREATE TABLE categories (id int AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL);
INSERT INTO categories VALUES (1,"Call management"),(2,"Technical skills"),(3,"Ticket management");

and

CREATE TABLE qualities (id int AUTO_INCREMENT PRIMARY KEY, category int NOT NULL, punctuation int NOT nULL)

then store and query your data accordingly