SQL 一对多表与多个一对一关系

Question

我正在开发一个具有以下目标的项目：用户可以创建挑战并选择一个可选的对手来参加该挑战。该挑战赛会生成每日条目并跟踪这些条目的统计数据。

基本的用户和条目实体如下所示：

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

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

我遇到问题的部分是具有竞争对手概念的挑战部分。我可以看到两种方法。

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

第一种方法的问题是引用完整性很困难，因为现在 userId 驻留在两列（ownerId 和竞争对手Id）。我必须为所有内容创建两个表（owner_entries、竞争对手_entries、owner_stats 等）才能设置外键。

第二种方法解决了这个问题，并且具有一些优点，例如允许未来有多个竞争对手。然而，我不能再用这种方法做的一件事是在单个用户而不是整个挑战表中强制挑战名称唯一性。此外，像寻找挑战的所有者这样的任务现在变得更加棘手。

挑战表的正确方法是什么？无论如何，是否可以以开发人员友好的方式设置这些表，或者我应该直接跳到类表继承并在那里管理所有者/竞争对手的概念？

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

这样可以轻松跟踪谁拥有挑战，同时提取出各个参与者。
这还允许您安全地由所有者唯一地确定挑战名称，并且 participant 中的 userId 上的外键很容易。 “对手”是所有非挑战所有者的参与者。

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

在这种情况下，您可以引用竞争对手和所有者，而无需创建新表。