How to store user defined scoring formulas?

Question

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?

Example:

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

Question_and_answer_table

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!

Cheers

• Vérace: Will the number of questions always be the same?
• Rick James: Whatever is most convenient for your application language. PHP? Java? Whatever?
• Luck: @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.
• Luck: @RickJames - I work mainly with PHP and Mysql. More of a database planning (diagramming) question. Apologies if this was in the wrong place.

Answer 1

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

• Luck: 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.