Таблица SQL один-ко-многим против нескольких отношений один-к-одному

Question

Я работаю над проектом со следующей целью: Пользователь может создать Конкурс и выбрать опционального соперника, чтобы принять участие в этом вызове. Вызов генерирует ежедневные записи и будет отслеживать статистику по ним.

Основные сущности User и Entry выглядят так:

CREATE TABLE users (
    id (INT),
    PRIMARY KEY (id)
);

CREATE TABLE entries (
    challengeId INT,
    userId INT,
    entryDate DATE,
    entryData VARCHAR,
    PRIMARY KEY (challengeId, userId, entryDate)
)

Часть, с которой у меня возникли проблемы, - это задача Challenge с концепцией Rival. Я вижу два подхода.

// Hard code the concept of a Challenge Owner and Rival:
CREATE TABLE challenges (
    id INT,
    name VARCHAR,
    ownerId INT,
    rivalId INT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY (ownerId, name)
);

// Create Many-to-one relationship.
CREATE TABLE challenges (
    id INT,
    name VARCHAR,
    PRIMARY KEY (id),
    UNIQUE KEY (name)
)
CREATE TABLE participant (
    challengeId INT,
    userId INT,
    isOwner BIT,
    PRIMARY KEY (challengeId, userId)
)

Проблема с первым подходом заключается в том, что ссылочная целостность является сложной, поскольку теперь есть два столбца, в которых находятся идентификаторы пользователя (ownerId и rivalId). Мне нужно создать две таблицы для всего (owner_entries, rival_entries, owner_stats и т. Д.), Чтобы настроить внешние ключи.

Второй подход решает эту проблему и имеет некоторые преимущества, такие как допуск нескольких соперников в будущем. Тем не менее, одна вещь, которую я больше не могу сделать с этим подходом, - это обеспечить уникальность имени Challenge для одного пользователя вместо всей таблицы Challenge. Кроме того, такие задачи, как поиск владельца Challenge, теперь сложнее.

Какой правильный подход к таблице вызовов? Есть ли способ настроить эти таблицы в удобной для разработчика форме, или я должен просто перейти к наследованию таблиц классов и управлять там концепцией "владелец / соперники"?

Answer 1

Я думаю, что я бы настроил это следующим образом (используя второй подход):

CREATE TABLE challenges (id INT, 
                         name VARCHAR, 
                         owner_id INT, 
                         PRIMARY KEY (id),
                         UNIQUE KEY (name, owner_id))

CREATE TABLE participant (challengeId INT,
                          userId INT, 
                          PRIMARY KEY (challengeId, userId))

Это позволяет легко отслеживать, кто является владельцем задачи, но извлекает отдельных участников.
Это также позволит вам безопасно указать уникальное имя для вызова и внешние ключи на userId в participant легко "Соперники" - это все участники, которые не являются владельцами соревнований.

Answer 2

Я отношусь к первому подходу правильно. Вы можете иметь одну таблицу для пользователей и одну для задач.

Знаете ли вы, что вы можете ссылаться на одну таблицу дважды, как показано ниже?

SELECT * FROM CHALLENGES 
INNER JOIN USERS AS OWNERS ON OWNERS.ID = CHALLENGES.OWNERID
INNER JOIN USERS AS RIVALS ON RIVALS.ID = CHALLENGES.RIVALID

В этом случае вы можете ссылаться как на конкурентов, так и на владельцев, не создавая новые таблицы.