Storage strategy for combination of all table items

Question

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
(
    hash VARCHAR NOT NULL,
    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)
);

CREATE TABLE result
(
    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)
);

2. Create a result table which, creates for each combination and user vote.

CREATE TABLE result
(
    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)

Answer 1

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

CREATE TABLE pairs (
    item1 ...,
    item2 ...,
    ct INT
    PRIMARY KEY(item1, item2) )

INSERT INTO pairs
     (item1, item2, ct)
     VALUES
     (?, ?, 1)   -- value list
ON DUPLICATE KEY UPDATE
     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".)