用于存储大学评估和招生的代码评审数据库设计

Question

我想要一个用于大学测试工具的数据库--在云中运行测试，非常类似于CI系统。

它需要存储用户，课程，注册，评估，提交和结果。

这不是一个为整个机构提供动力的系统，但对于几个不同的课程来说，这是很重要的。对于未来的维护人员，我希望数据库具有良好的结构、可扩展性和“直观性”。

要求：

• 两个不同的课程可能有相同的名称(但在不同的年份/时期进行)
• 每段时间只能运行一次课程(其中每年有多个课程)。
• 评估名称在课程中必须是唯一的，但不能跨其他课程。
• 用户可以以讲师、导师或学生的身份注册课程，但不能同时注册多个。学生将来可能会成为同一门课程的导师。

我已经在下面列出了我的模式，并给出了我觉得需要它们的相关评论。

CREATE TYPE PERIOD          AS ENUM ('summer', 'first', 'autumn', 'winter', 'second', 'spring');
CREATE TYPE ASSESSMENT_TYPE AS ENUM ('assignment', 'lab');
CREATE TYPE TEST_RESULT     AS ENUM ('warnings', 'errors', 'perfect');
CREATE TYPE ROLE            AS ENUM ('student', 'tutor', 'convenor', 'admin');

CREATE TABLE users (
    uid      VARCHAR(20)  PRIMARY KEY,
    email    VARCHAR(255) UNIQUE NOT NULL,
    password BYTEA               NOT NULL
);

CREATE TABLE courses (
    -- id is used instead of (code, period, year) to
    -- uniquely identify a course, since its easier.
    id     SERIAL       PRIMARY KEY,
    -- code is used within the university to identify a course,
    -- but not when it was run (a single course can be run
    -- multiple times, with different students/assessments/etc)
    code   VARCHAR(20)  NOT NULL,
    name   VARCHAR(255) NOT NULL,
    period PERIOD       NOT NULL,
    year   INTEGER      NOT NULL,
    -- ensure that a single course can only be run once per period.
    CONSTRAINT once_per_period UNIQUE (code, period, year)
);

CREATE TABLE assessment ( 
    id           SERIAL          PRIMARY KEY,
    name         VARCHAR(255)    NOT NULL,  
    type         ASSESSMENT_TYPE NOT NULL,
    comments     TEXT,
    course_id    INTEGER         REFERENCES courses(id),
    -- assessment names must be unique within a course,
    -- but not across courses.
    CONSTRAINT unique_name_per_course UNIQUE (name, course_id)
);

CREATE TABLE test_results (
    id            SERIAL      PRIMARY KEY,
    result        TEST_RESULT NOT NULL,
    warnings      TEXT        NOT NULL,
    errors        TEXT        NOT NULL
);

CREATE TABLE submissions (
    id            SERIAL      PRIMARY KEY,
    title         TEXT        NOT NULL,
    description   TEXT        NOT NULL,
    feedback      TEXT        NOT NULL,
    uid           VARCHAR(20) REFERENCES users(uid),
    assessment_id INTEGER     REFERENCES assessment(id),
    result_id     INTEGER     REFERENCES test_results(id)
);

CREATE TABLE enrol (
    user_uid  VARCHAR(20) REFERENCES users(uid),
    course_id INTEGER     REFERENCES courses(id),
    role      ROLE        NOT NULL,
    -- ensure a user cannot have multiple roles for a single course
    CONSTRAINT one_role_per_course_run UNIQUE (user_uid, course_id)
);

我想要一些关于我的设计的反馈--我能做得更好吗？我错过了什么吗？有什么常见的/惯用的设计选择吗？

我本来打算在代码评审堆栈交换上发表文章，但我看到了之前的一个答案，其中提到这是一个更好的位置。

干杯

Answer 1

只是一些想法。

我不会使用ENUMs，我不想说“总是”(可以对此进行更正)，但是我会“总是”使用FOREIGN KEY。想想性别--25年前，你本可以拥有一个ENUM ('M'，'F') --现在你可以拥有：

• Unknown
• Intersex
• Non-binary
• Decline-to-answer
• Don't know
• Don't care :-)

&c，&c。

有了FOREIGN KEY，事情更容易转变为未来。此外，您要指出的是，您希望您的database to be well-structured, extensible, and "intuitive" for future maintainers(*) - FOREIGN KEYs将通过将您的系统移植到另一个关系数据库管理系统来做到这一点！经过一个FK，我会去一个CHECK CONSTRAINT！

(*)独自一人为这件事喝彩！如果所有的系统设计人员都这么体贴的话--我作为程序员的一些不眠之夜本可以避免！:-)

查看这个SO上的线程 (问题和从同一张海报上接受的答案)。它试图通过所有的选项(但错过一个-见下文！)而“共识”(如果说在这些网站上存在这样的事情)是，FKs，其次是CCs。看看这 --海报代表超过30万是有原因的(更别提这里的流量了)！

抓到了。MySQL (amazingly!)不支持CKs！另外，减轻CCs的另一个因素是，DBA和/或系统管理员经常是唯一允许更改系统表的操作符(这就是修改CCs所涉及的)，而普通程序员/用户可以更改普通表。

由此产生的一个推论是，如果您想要对磁盘活动进行严格控制，那么您可能希望在系统表上尽可能少地控制磁盘活动--涉及CC的每一项操作都涉及查询它们，因此查询FK表的开销毕竟不是什么大问题。

一个有利于CCs而不是ENUMs的因素是，你可以在他们身上使用LIKE和其他运算符--后者是不行的。最后，看看可怕的语法这里，将ENUMs中的值与其他值进行比较(记住它们是不同的类型！)如果您正在查找(比方说)下拉列表的值，则必须使用类似于这的方法：

select n.nspname as enum_schema,  
       t.typname as enum_name,  
       e.enumlabel as enum_value
from pg_type t 
   join pg_enum e on t.oid = e.enumtypid  
   join pg_catalog.pg_namespace n ON n.oid = t.typnamespace

相对于：

select my_field from my_table;

总之，我强烈建议您使用FKs或CCs在您的表上强制执行声明性引用完整性。

有一些限制表中的值的方法(仅为完整性而提及)--您可以使用DOMAINs或TRIGGERs。前者看起来像苏必利尔，但对于ENUMC50，而TRIGGERC50只应该用于复杂的业务逻辑！

最后，回答这个问题。

您的模式<#>看起来很好--很难用眼睛来判断这些事情。如果我是你，我会做的是开始使用/开发它，看看你是否遇到了问题。回到我们这里与具体的问题，如果你这样做-你的问题是有点宽泛！附注：欢迎来到论坛！