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