sql - 多表一对一关系

Question

我不熟悉使用数据库，并且正在尝试设计一个新的数据库，我认为我需要一个跨多个表的一对一关系。

为了演示我的设计，假设我正在构建一个时间表数据库作为示例。我首先为具有一对多关系的人创建一个表

CREATE TABLE person (
    person_id SERIAL NOT NULL, 
    name VARCHAR,
    PRIMARY KEY (person_id)
);

接下来，我创建一个事件表，其中包含人际关系的许多部分

CREATE TABLE events (
    event_id SERIAL NOT NULL, 
    type VARCHAR,
    name VARCHAR,
    person_id INTEGER,
    time TIMESTAMP WITHOUT TIME ZONE,
    PRIMARY KEY (event_id),
    FOREIGN KEY(person_id) REFERENCES person (person_id)
);

现在假设我有两种不同类型的事件，它们有不同的信息，比如吃饭和作业

CREATE TABLE meals (
    event_id INTEGER NOT NULL, 
    food VARCHAR,
    utensils VARCHAR,
    PRIMARY KEY (event_id),
    FOREIGN KEY(event_id) REFERENCES events (event_id)
);

CREATE TABLE homework (
    event_id INTEGER NOT NULL, 
    subject VARCHAR,
    completed BOOLEAN,
    score FLOAT,
    PRIMARY KEY (event_id),
    FOREIGN KEY(event_id) REFERENCES events (event_id)
);

现在，我尝试以这种方式设计我的数据库的原因是，有时您可能只想显示每个人的基本事件列表，而不管该事件是什么。例如，如果我按如下方式初始化我的表

INSERT INTO person (name) VALUES ('Brad');
INSERT INTO events (type, name, person_id, time) VALUES ('meal', 'lunch', 1, '12/28/2016 12:00:00')    
INSERT INTO events (type, name, person_id, time) VALUES ('meal', 'breakfast', 1, '12/28/2016 12:00:00');
INSERT INTO meals (event_id, food, utensils) VALUES (1, 'eggs', 'fork');
INSERT INTO meals (event_id, food, utensils) VALUES (2, 'turkey sandwich', 'hands');
INSERT INTO events (type, name, person_id, time) VALUES ('homework', 'final project', 1, '12/28/2016 18:00:00');
INSERT INTO homework (event_id, subject, completed, score) VALUES (3, 'Math', 'T', 0.93);

然后我可能想为 Brad 生成所有事件的列表

SELECT (events.time, events.type, events.name) FROM events 
LEFT JOIN person ON person.person_id = events.person_id 
WHERE person.name = 'Brad';

这很简单，我感到困惑的是，如果我想看看 Brad 吃了什么怎么办。我想我可以使用两个 JOIN person 之间的语句和 events和 events和 meals ，但是如果我只是想遍历 Brads 事件并获得关于每个事件的所有额外信息，(例如，如果事件是一顿饭，告诉我他吃了什么，如果是家庭作业，告诉我他的分数得到)？

总的来说，我有几个问题。

1. 这是一个好的数据库设计还是我应该考虑的其他事项？上面的每个潜在用例，再加上几个都是我需要使用数据库的标准事情。
2. 如何轻松确定要在哪个表中查找事件表中任何给定事件的更多信息？这里有几个想法——我可以存储另一个表的名称，其中包含事件表中事件的更多信息(即，用 type 列替换 table 列)但我想我在某处读到这是个坏主意。

还有一些其他注意事项，我正在为数据库使用 Postgresql。除了我在此处显示的内容之外，我正在构建的实际数据库对每个表都有更多详细信息。我只是想说明一下我要达到的目标。最后，我正在使用 sqlalchemy 的 ORM 构建/访问数据库，所以如果有一个漂亮的技巧，我可以使用 relationships 来做这将对此有所帮助，这也将非常有用。

Answer 1

如果您想获取每个事件的所有详细信息，就会遇到问题，因为保存事件详细信息的表具有不同类型的列。而且您当然不希望在代码中对各种事件详细信息表名称进行硬编码，毕竟当您想要添加或删除表或更改名称时会发生什么？你必须到处更新你的代码!

所以首先我会说你会想要这里的景色。像这样的东西:

CREATE OR REPLACE VIEW event_details AS
    SELECT * FROM meals
    UNION ALL
    SELECT * FROM homework;

这将允许您一次性选择所有事件类型的详细信息，例如

SELECT * FROM event_details WHERE event_id IN (
    SELECT event_id FROM events WHERE person_id = (
        SELECT person_id
        FROM person
        WHERE name = 'Brad'
    )
)

当然除了它不起作用，因为表结构不同。因此，您需要找到某种方式来以统一的方式表示数据；例如，对每条记录执行 ROW_TO_JSON:

CREATE OR REPLACE VIEW event_details AS
    SELECT ROW_TO_JSON(meals.*) AS details FROM meals
    UNION ALL
    SELECT ROW_TO_JSON(homework.*) AS details FROM homework;

现在这个查询:

SELECT * FROM event_details WHERE (details->>'event_id')::INTEGER IN (
    SELECT event_id FROM events WHERE person_id = (
        SELECT person_id
        FROM person
        WHERE name = 'Brad'
    )
)

给你:

{"event_id":1,"food":"eggs","utensils":"fork"}
{"event_id":2,"food":"turkey sandwich","utensils":"hands"}
{"event_id":3,"subject":"Math","completed":true,"score":0.93}

然后您可以解析 JSON 并用它做您想做的事。当您想要添加或删除或重命名表格时，您只能在 View 中进行。

请注意，我并不是说这是一种很好的(或唯一的)方法。我不清楚是否有充分的理由让每个事件类型都有一个单独的表，而不是只有一个 events 表并将特定于类型的数据放在 JSONB 字段中。这将使查询变得更容易和更快，如果您使用 JSONB，特定类型的数据也可以被索引。根据您展示的示例，我认为这会是一个更好的设计。