1

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? enter image description here

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

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.