mysql - 需要数据库设计建议

Question

我有一个看起来像那样的问题表

如您所见，有 2 个几乎相同的 id 行:id、question_id。 id - 自动递增，每个问题的唯一 ID，question_id - 例如，类(class) 1 第 1 课有 5 个问题，例如:问题 1、2、3、4 , 5. 类(class) 1 第 2 课有 3 个问题:1、2、3 等。换句话说，它就像每个唯一的 course_id、lesson_id 组合的自动增量字段。我决定根据 course_id、lesson_id 手动添加 question_id 以保持数据库结构的可读性而不是弄乱数据库，在 之间有一堆关联表code>course-lesson-question unique id 值。

第一个问题是

你觉得这个解决方案好不好？你会如何设计这个数据库？

---

问题是，当我想用​​给定的 course_id、lesson_id 插入新问题时，id 字段会自动递增，但我得到了

第二个问题

如何根据唯一的 course_id、lesson_id 组合获取最后一个 question_id 列值。例如，如果第 1 课第 2 课有 3 个问题:1、2、3，我想添加第 4 个问题(正如我所说，在当前的数据库设计中，我手动插入 question_id 值)，如何我知道吗，类(class) 1 第 2 课的最后一题是第 3 题，并插入新题 (last_number=3)++=4？

更新

情况有点复杂，但我会尽力解释:

这是在线教程网站。有不同的类(class)，每门类(class)都有很多类(class)。现在我正在设计问答部分，老师发布问题，用户得到专门的问题。

Full size image here

现在，questions 表专用于类(class)>基于类(class)的问题。

question_from_to_assoc - 它用于在问题作者和接收者用户之间创建关联。例如管理员 (id=.) 向 id=5 的某个用户发送问题。

qa_assoc - 问答关联表。

Answer 1

首先，这不是一个最优的数据库设计。您的模式是非规范化的，这不是很好。

回答你的第一个问题。我会将类(class)、类(class)、问题和作者分成单独的表格。然后，我会在类(class)、类(class) 和问题 的主键旁边添加一个数字字段。 PK 只会确保一行的唯一性，但数字字段将是您的类(class)编号、问题编号等。例如，使用 PK 来表示问题编号在我看来不是一个好主意，因为它应该保持不变。例如，如果您的问题改为字母而不是数字，您的 PK 就必须更改，这可能会破坏参照完整性。

之后，我将对问题编号和 FK 添加唯一约束，例如 [question_no, lesson_id] 以确保您不能在同一课上有两个问题 1。 类(class)也是如此。 Course 对 course_no 有唯一约束。

最后，为了根据类(class)自动增加问题编号，我会使用一个触发器来执行如下操作:

CREATE TRIGGER tr_question_number BEFORE INSERT ON questions
FOR EACH ROW BEGIN
   SET NEW.question_no = (SELECT MAX(question_no)+1 FROM questions WHERE lesson_id = NEW.lesson_id FOR UPDATE)
END;

此触发器会将问题编号字段设置为最新值 + 1。FOR UPDATE 子句非常重要，因为它将锁定行以避免并发插入以获取相同的编号。

触发器只是一个草稿，但这只是我会做的事情的一般想法。

希望对您有所帮助。