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

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:

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.

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
``````

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

• Thanks for the Idea, but please not that the `sten` values are not equal for all `Factors`. Please see EDIT 1... Thank you – – Universal Grasp Nov 11 '14 at 10:09
• PLease click here to see a larger image: ctrlv.in/459785 – Universal Grasp Nov 11 '14 at 10:10
• @UniversalGrasp Please look in detail to STEN_Lookup table- you can set different values for each score AND factor. His solution is ok with your reqirements. It is just that the PK is (Factor_ID, Score_Value). – jynus Nov 11 '14 at 10:12
• I've just made it a little clearer so you can see. In cases where the STEN_Value differs from the previous factor score, you just need to change the data. So for Factor 2 students a score of one gives a STEN of 9, but for Factor 1 Students a score of one gives a STEN of one. – KidCode Nov 11 '14 at 10:15
• @KidCode Thank You... So if I understand your suggestion well, you mean, `Fact_ID->1`=`Score_Value->[0 OR 1 OR 2 OR..4]` = `STEN_Value->[1 OR ...2]`... This is Super great, however, following this, then we might have an issue with FactorB because, it's `STEN_Value` will start from `8`instead of `1`: i.e `Fact_ID->2`=`Score_Value->` = `STEN_Value->` which is meant to be `Fact_ID->2`=`Score_Value->` = `STEN_Value->`.... Thanks again though. – Universal Grasp Nov 11 '14 at 10:25