Let's say I've one table with items with the following fields:

  • id_item
  • name
  • description

& one table with the user information, following like:

  • id_user
  • email
  • name

Now I'd like to let vote my user for any combination of all the items (like a head-to-head competition)

The thing is now that I'm struggling to find a suitable database model. Following I can't decide between the following two options:

  1. Create a relation table which creates for each combination of item an own hash. Then I would have a second table storing the results:
CREATE TABLE combination
    id_1 INT NOT NULL,
    id_2 INT NOT NULL,
    primary key (hash),
    FOREIGN KEY (id_1)
        REFERENCES items(id_item)
    FOREIGN KEY (id_2)
        REFERENCES items(id_item)
    id INT NOT NULL,
    hash INT NOT NULL,
    user_id INT NOT NULL,
    vote INT NOT NULL,
    primary key (hash),
    FOREIGN KEY (user_id)
        REFERENCES user(id_user)
  1. Create a result table which, creates for each combination and user vote.
    id INT NOT NULL,
    item_id_1 INT NOT NULL,
    item_id_2 INT NOT NULL,
    user_id INT NOT NULL,
    vote INT NOT NULL,
    FOREIGN KEY (item_id_1)
        REFERENCES itme(id_item)
    FOREIGN KEY (item_id_2)
        REFERENCES item(id_item)
    FOREIGN KEY (userID)
        REFERENCES user(id_user)

My thoughts are especially about:

  • checking for which combination the user already answered/voted -> finding the missing
  • triggers for creating new combination pairs
  • storage efficiency (saving hash + user or saving 2x ID + user)

1 Answer 1


Here's a way to count how many of each pair of items is upvoted:

    item1 ...,
    item2 ...,
    ct INT
    PRIMARY KEY(item1, item2) )

     (item1, item2, ct)
     (?, ?, 1)   -- value list
     ct = ct + 1;

It has a flaw of saying the pair (x,y) is different than (y,x). If that matters, there are two workarounds:

On input: Change "column list" to

    (LEAST(?,?), GREATEST(?,?), 1)

Or, on output: combine the counts for matching pairs.

(I don't know why you mentioned "users".)

  • Thank you for your answer, this would be my fall back option, because it accumulates the votes already during insert. Initially I wanted to store each answer from each user so that he also can change it afterwards, so the DB should remember each answer of each user for each pair... Overkill? May 1, 2020 at 8:42
  • @CanadaRunner - INSERTing a new row may be faster than UPDATEing an existing row. (This would be an interesting research project.) Updating involves more conflicts.
    – Rick James
    May 1, 2020 at 14:45
  • yes that can be, but with your solution I would have a total count of votes for a certain pair for all users, right? But i want to store every vote for every user seperate. I think with your solution this is not possible, or? I just have said also that I can have different response (like a range from strong agree to strong disagreement, and so the summing would not work) May 1, 2020 at 16:30
  • @CanadaRunner - I would periodically roll the list up into the counts. That is, I would keep the counts for "as of the last hour" (or whatever). But the maintenance of the counts would be low cost. Rolling up the last hour's counts is quite fast. (I did not mean to suggest counting over the entire table.)
    – Rick James
    May 1, 2020 at 17:49
  • the thing is that I’m not interested in the total counts of a pair… its more the user preferences which I’m interested in, thats why the summing is not planned. May 4, 2020 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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