Should I separate table with a lot of columns and 3 index column?

Question

I'm new to database stuff so I apologize if I say anything ridiculous. I have a table with 300 columns, which stores the score of 300 questions. There is also an "id-user" columns which is the primary key, and three more columns named "weekScore", "monthScore" and "yearScore". Every time a user answer a particular question the id will found and score will added to the particular column, and the score added to weekScore, monthScore and yearScore too. As you see there is a lot of queries go to this table. Question: I want to index three score columns (weekScore, monthScore and yearScore). is it better to separate these three columns and make another table because of the indexing?

Or I change the design to this, and index three columns (weekScore, monthScore and yearScore).

Answer 1

I'm doing a bit of guessing here, but I imagine you have a table like:

CREATE TABLE ANSWERS
( USER_ID ...
, TIME_OF_TEST ...
, QUESTION1 ...
, QUESTION2 ...
...
, QUESTION300 ...
,    PRIMARY KEY (USER_ID, TIME_OF_TEST)
);

and now you want to add attributes for weekScore, monthScore and yearScore.

I think it is a bad idea to add those attributes to this table, and in fact I suggest you rethink your whole design. I would have thought that there is an entity TEST that acts as a placeholder for a number of questions, but since you don't mention it I'm going to ignore that. I would suggest something like:

CREATE TABLE QUESTIONS
( QUESTION_ID ...
, WORDING_OF_QUESTION ...
, SCORE ...
, CORRECT_ANSWER ...
....
)

Dependent of what kind of questions there are, you might even normalize it further with a table QUESTION_ITEMS

CREATE TABLE ANSWERS
( USER_ID ...
, QUESTION_ID ...
, ANSWER ...
, TIME_OF_ANSWER ...
);

It is difficult giving any more specific advise without knowing more details, but hopefully it will give you some ideas.

One common mistake is to think of the database as a spread-sheet that represents the report (your first suggestion above). I suspect that naming a relation table in SQL is the reason for much of this confusion.

For the new attributes weekScore, monthScore and yearScore, I would start by determining them in runtime, i.e. don't store them anywhere. You can create a view for them, and if it turns out to slow, make a table out of it that you update with a batch-process.