SQL:建模模板继承

Question

我想问一问，是否有可能建模一个模板化的数据结构，如果必要的话，它可以被覆盖。

假设我们有一个包含以下项目的列表：

模板列表

• 项目1职位0
• 项目2职位1
• 项目3职位2
• 项目4职位3

现在，我想创建一个使用模板列表作为基础的列表，但是修改其中的一些部分：

具体列表，基于模板列表

• 项目1位置0 //从模板列表继承
• 第5项-职位1/新-仅在具体清单中提供
• 第4项位置2/继承自模板列表，但具有不同的位置
• 项目3位置3 //从模板列表继承，但具有不同的位置

在此列表中，缺少模板列表中的第2项不应是结果列表的一部分。

是否可以在SQL中对这些关系进行建模？(我们正在使用PostgreSQL)

Answer 1

做你想做的事情是可能的，但它不一定是一个好的解决方案，也不一定是你所需要的。您所要求的内容看起来像元模型，但是关系数据库是为一阶逻辑模型设计的，虽然SQL可以在一定程度上超越它，但通常最好不要太抽象。

话虽如此，举个例子。我假设列表项目的身份是基于位置或插槽的。

CREATE TABLE template_list (
    template_list_id SERIAL NOT NULL,
    PRIMARY KEY (template_list_id)
);

CREATE TABLE template_list_items (
    template_list_id INTEGER NOT NULL,
    slot_number INTEGER NOT NULL,
    item_number INTEGER NOT NULL,
    PRIMARY KEY (template_list_id, slot_number),
    FOREIGN KEY (template_list_id) REFERENCES template_list (template_list_id)
);

CREATE TABLE concrete_list (
    concrete_list_id SERIAL NOT NULL,
    template_list_id INTEGER NOT NULL,
    FOREIGN KEY (template_list_id) REFERENCES template_list (template_list_id),
    UNIQUE (concrete_list_id, template_list_id)
);

CREATE TABLE concrete_list_items (
    concrete_list_id INTEGER NOT NULL,
    template_list_id INTEGER NOT NULL,
    slot_number INTEGER NOT NULL,
    item_number INTEGER NULL,
    PRIMARY KEY (concrete_list_id, slot_number),
    FOREIGN KEY (concrete_list_id, template_list_id) REFERENCES concrete_list (concrete_list_id, template_list_id),
    FOREIGN KEY (template_list_id, slot_number) REFERENCES template_list_items (template_list_id, slot_number)
);

现在，要获取具体列表中的项，可以使用如下查询：

SELECT c.concrete_list_id, x.slot_number, x.item_number
FROM concrete_list c
LEFT JOIN (
  SELECT ci.concrete_list_id,
         COALESCE(ci.template_list_id, ti.template_list_id) AS template_list_id,
         COALESCE(ci.slot_number, ti.slot_number) AS slot_number,
         COALESCE(ci.item_number, ti.item_number) AS item_number
  FROM concrete_list_items AS ci
  FULL JOIN template_list_items AS ti ON ci.template_list_id = ti.template_list_id
                                     AND ci.slot_number = ti.slot_number
) x ON c.concrete_list_id = x.concrete_list_id OR c.template_list_id = x.template_list_id;

这是一个用于演示的SQL小提琴。请注意，为了演示的简单性，我用整数和硬编码值替换了串行类型。