Storage strategy for combination of all table items Let's say I've one table with items with the following fields:


id_item
name
description


&amp; 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:


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



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