Mysql – How to store user defined scoring formulas

database-designMySQL

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

Best Answer

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

Related Solutions

Mysql – Group by reversible pair

You can use a UNION of two subqueries:

SELECT 
    user_id,
    MAX(ts)  AS last_timestamp,
    SUM(cnt) AS number_of_messages
FROM
  ( SELECT  to_user_id AS user_id,
            MAX(timestamp) AS ts,
            COUNT(*) AS cnt
    FROM chat
    WHERE from_user_id = 1
    GROUP BY to_user_id 
  UNION ALL
    SELECT  from_user_id AS user_id,
            MAX(timestamp) AS ts,
            COUNT(*) AS cnt
    FROM chat
    WHERE to_user_id = 1
    GROUP BY from_user_id 
  ) AS tmp 
GROUP BY 
    user_id ;

Two indexes, on (from_user_id, to_user_id, timestamp) and (to_user_id, from_user_id, timestamp) would help efficiency of the subqueries.

Mysql – Find the user with closest taste of mine

You can make a table which holds the users with similar taste for every user.

CREATE TABLE `similar_users` (
    `uid1` INT,
    `uid2` INT,
    `score` INT,
    PRIMARY KEY (`uid1`, `uid2`)
)

The algorithm for calculating the score can be anything, as long as a higher score means a closer taste.

You you should limit the amount of similar users to a fixed number and also exclude the ones with a score less than a certain value (the ones with a not so similar tastes).

The contents of the table will be calculated periodically (e.g. every day).

Then step 3 from your solution becomes something like this:

SELECT w.film
FROM similar_users su
JOIN wich w ON w.uid = su.uid2 AND w.rate > 2
LEFT JOIN wich wn ON wn.film = w.film AND wn.uid = {current_user}
WHERE su.uid1 = {current_user}
ORDER BY su.score * w.rate DESC

Example:

Best Answer

Related Solutions

Mysql – Group by reversible pair

Mysql – Find the user with closest taste of mine

Related Question