CODEWITHSUNDEEP
phpmysqlmetaprogrammingmethodology
Antonio Laguna
Antonio Laguna

Reputation: 9290

Storing variable number of values of something in a database

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

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?

Upvotes: 0

Views: 76

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

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

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65332

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

Upvotes: 2

Related Questions