# How can this table possibly be converted to a MySql table?

Posted on

### Question :

I got a TABLE in Excel format like:

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 `sten`s 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.

### 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).