Search code examples
phpmysqlmetaprogrammingmethodology

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.

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


Solution

  • 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