Mysql – How to this table possibly be converted to a MySql table

MySQLtable

I got a TABLE in Excel format like:
enter image description here

This is a STEN TABLE containing FACTORS (A,B,C,D,E....etc)

Each Factor is like a separate test.

Each Factor i.e. Factor A has a [Raw Score] and a [Sten]

Supposing Factor A, had a question like:

-----------------------------------

Why do humans have Eyes?

The answer options could be like:

a) To Watch movies = [Raw Score] -> 10,

b) To Read Novels = [Raw Score] -> 5,

c) To close them while sleeping = [Raw Score] ->0

So if they Chose a) then, the system will go to the STEN TABLE to get the STEN Equivalent under Factor A, in this case, the sten equivalent will be 4. (See Factor A->Row score 10-> Sten column)

What could be the most practical way to have this STEN TABLE with Factors and their Raw Scores and Stens created?

Smothing like

              **STEN TABLE**
                         |
                         |
                **FACTORS (A,B,C,D...)**
                      /     \
                     /       \
                    /         \
                   /           \
                  /             \
            **[Raw Score]      [Sten]**
                    

EDIT 1:

To See a larger image, please click here: http://ctrlv.in/459785

Please note that, the stens are not equal for all Factors, though the Raw Scores are the same. i.e. in Factor A, [Raw Score]->3 = [sten]->2 but in Factor C [Raw Score]->3 = [sten]->1 and in Factor F [Raw Score]->1 [sten]->2 whereas in Factor E [Raw Score]->1 = [sten]->1.

Thank you.

Any Suggestion is highly honored.

Best Answer

I'd pull this data into two tables: Factor's and STEN Lookup's.

Your Factors table would look like:

Factor_ID(PK) | Factor_Name
--------------|------------
  1           |      A
  2           |      B
  3           |      C
  4           |      D

Your `STEN_Lookup'

Factor_ID(FK) | Score_Value | STEN_Value
--------------|-------------|-------------
  1           |       0     |       1
  1           |       1     |       1
  1           |       2     |       1
  1           |       3     |       2
  1           |       4     |       2
  2           |       0     |       8
  2           |       1     |       9
  2           |       2     |       11
.....

I'd suggest you do some reading on normalization, as it helps massivly when designing your tables. A great Video Resource is MVA, (Video 2 on this hyperlink).