First post, so apologize if I have not chosen the best spot for it:

If I had table of values, let's say corresponding to a question number and its corresponding answer, what is the correct way to plan a database to allow users to store formulas to calculate a score based on said answers?


Let's say I have a table of answer given to a series of questions:


ID Answer 1 3 2 5 3 7 4 9

Then I have 3 different users, User1, User2, User 3.

If I want each user to be able to define how they want to weigh the answers to determine a final score

ex: User1: A1*3-A2*2+A3*7*A4=SCORE

what's the best way to handle storing a formula for each user?

Thank you for your time and help!


  • Will the number of questions always be the same? – Vérace May 4 '15 at 19:10
  • Whatever is most convenient for your application language. PHP? Java? Whatever? – Rick James May 4 '15 at 21:54
  • @Vérace Thanks for replying. No - this is more of a hypothetical. Was trying to better understand how to build this type interaction so I can expand on it. – Dan E. May 6 '15 at 22:45
  • 1
    @RickJames - I work mainly with PHP and Mysql. More of a database planning (diagramming) question. Apologies if this was in the wrong place. – Dan E. May 6 '15 at 22:46

You can have a table of weights -- that is, an association of user with each question and how much that answer is weighted. Then you can give each user their own weight for each answer.

create table Weights(
    UserID     int not null references Users( ID ),
    AnswerID   int not null references Answers( ID ),
    Weight     int default 1, -- For Ux: Ax * Weight,
    constraint PK_Weights primary key( UserID, AnswerID )

I've shown the weight as an integer but you can change that if you want fractional values (A1 * 3.5 + A2 * -2.75 + A3 * 7 + A4 * 3.1415 = SCORE ).

| improve this answer | |
  • Thank you @TommCatt. This was very helpful. Essentially break the weights down by User-and-Answer combo, and call individual weighting when calculating the score. I appreciate your time and help. Cheers. – Dan E. May 6 '15 at 22:49

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.