mysql - 我应该复制数据还是使用复杂的查询

Question

我正在尝试从头开始制作一个论坛，主要是为了练习，也许以后会重用它。
我目前正在研究数据库，有一件事让我烦恼。我有一个表线程，其中必须包含第一篇文章、作者等的引用...但我不知道如何正确执行它。
(1) 我的第一个想法是“让我们尽可能减少重复”，所以我想我应该有一个基本的线程表

CREATE TABLE Thread(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(40) NOT NULL,
    category VARCHAR(40) NOT NULL,
);

和一个表格帖子

CREATE TABLE Post(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    author VARCHAR(40) NOT NULL,
    thread INT NOT NULL,
    creationDate DATETIME NOT NULL,
    content TEXT NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_Post-author_UserProfile-name FOREIGN KEY (author)
    REFERENCES UserProfile(name) ON UPDATE CASCADE,
    CONSTRAINT fk_Post-thread_Thread-id FOREIGN KEY (thread) 
    REFERENCES Thread(id)
);

这样，当我想知道某个线程的第一篇文章时，我会检索该线程中按日期排序的所有帖子并获取第一篇。易币钱!但后来我想，如果我只想知道一个线程、它的作者和创建日期(对我来说这似乎很合法)知道它的标题，那将是一个带有连接的巨大查询，并且可能非常缓慢和麻烦。

SELECT Thread.title, posts.creator, posts.creationDate
FROM Thread
JOIN (
    SELECT Post.thread as thread, Post.creationDate, UserProfile.name AS creator
    FROM Post
    JOIN UserProfile
        ON Post.author = UserProfile.id
) AS posts
    ON Thread.id = posts.thread
WHERE Thread.title = 'Boy, that''s one hell of a query you''ve got here !'
ORDER BY creationDate
LIMIT 1;

(我不确定这个查询，我是新手)

(2) “好吧!只需在 Thread 表中添加一个 'firstPost' 列”您会告诉我，但是为什么不复制作者引用呢？这在哪里停止？
我的猜测是，选项(1)太重了，也许选项(2)仅引用第一篇文章可能是更好的选择，但不要将作者引用直接复制到线程表上。如果我没有记错的话，应该会给出类似以下内容的内容，但会添加一列。

SELECT Thread.title, UserProfile.name, Post.creationDate
FROM Thread
JOIN Post
    ON Thread.firstPost = Post.id
JOIN UserProfile
    ON Post.author = UserProfile.id
WHERE Thread.title = 'Boy, that''s one hell of a query you''ve got here !';

您对此有何看法，请记住，作为一个教学项目，我不仅希望它能够工作，而且还希望它“干净”？

Answer 1

我理解你的想法。是的，一个帖子由一系列帖子组成，但第一个帖子与其他帖子不同，因为这是该帖子的主题，而其他帖子只是对第一个帖子的回复。

从这个角度来看，我们可以认为不仅帖子属于一个线程(因此帖子表必须包含线程 ID)，而且线程也是关于(第一个)帖子的，因此应该包含该帖子ID。这是一个可能的模型，但会导致母鸡和鸡蛋的问题:每个帖子必须属于一个现有的线程，并且每个线程必须引用一个现有的帖子。人们可以通过编写一个没有帖子引用的线程来解决这个问题，然后编写第一篇帖子，然后更新线程。这可行，但不是一个优雅的解决方案。另一种选择是在一个事务中写入两条记录(线程已经包含帖子 ID；帖子包含线程 ID)，并且约束仅在提交时起作用。这称为延迟约束，仅在少数 DBMS 中可用。我很确定，MySQL 不支持这些。

但是当然，您可以保持简单，说一篇文章与数据模型中的另一篇文章一样好，并且其中一篇恰好是第一篇。这是你已经拥有的，并且没有错。这是一个有效的模型。不过，我建议使用带有复合键和帖子编号而不是 ID 的模型:

• 话题(thread_id、标题、category_id)
• 帖子(thread_id、post_num、author_id、creation_time、内容)

其中主键为粗体。当然，第一篇文章的 post_num 为 1，第二篇文章为 #2，依此类推。这将使检索线程的第一个帖子变得非常简单。

但是我想到了另一件事。在许多论坛中，主题并不是由帖子链组成，而是由帖子树组成。这意味着:除了第一个帖子之外，一个帖子总是引用它正在回复的另一个帖子。这可能会导致以下模型:

• 话题(thread_id、标题、category_id)
• 帖子(thread_id、post_num、author_id、creation_time、内容、parent_post_num)

因此帖子表中的一条记录可以引用帖子表中的另一条记录(它回复的帖子)。在这里，复合键带来了另一个优势:post_num 和 parant_post_num 都只与 thread_id 结合使用才具有相关性。因此，约束 FOREIGN KEY (thread_id,parent_post_num) REFERENCES (thread_id, post_num) 效果很好，因为帖子在外部线程中不能有父帖子(可以使用非复合键) .