mysql - 可能由其他相同实体组成的实体的数据库设计

Question

我有一些体育设施，里面有可以踢 5x5 足球的 field 。我正在尝试为他们制作一个简单的预订系统。我的问题是，某些字段合并并形成更大的字段，设施的管理者希望将它们视为自己的实体(这是有道理的，如果他们这样预订它们为什么不呢)。

举一个实际的例子。我们有设施FA。它们有 3 个相邻的 5x5 字段，我们称之为 sa, sb, sc 并且它们中的任何两个都可以组合成一个 7x7 字段，我们称之为 dd 并且将这三者结合起来形成一个 10x10 的字段，我们称之为 te。其他设施也会发生这种情况，但这是更极端的情况。

当我进行预订并处理它时，我一直在尝试思考如何对字段的表进行建模，但我不确定。

我的一个解决方案是为字段建立一个表格

CREATE TABLE IF NOT EXISTS field (
    id              SMALLINT(5) UNSIGNED    NOT NULL AUTO_INCREMENT,
    arena_id        SMALLINT(4) UNSIGNED    NOT NULL,
    internal_id     TINYINT(3) UNSIGNED     NOT NULL,
    is_composite    BOOLEAN                 NOT NULL DEFAULT FALSE,

    PRIMARY KEY (id),
    UNIQUE (arena_id, internal_id),

    CONSTRAINT fk_field_arena_id FOREIGN KEY (arena_id) REFERENCES arena(id) ON UPDATE CASCADE ON DELETE CASCADE
)
;

并且它们与另一个表具有一对一或零的关系

CREATE TABLE IF NOT EXISTS field_component (
    field_id        SMALLINT(5) UNSIGNED    NOT NULL,
    component       SMALLINT(5) UNSIGNED    NOT NULL,

    PRIMARY KEY (field_id, component),

    CONSTRAINT fk_field_component_field_id FOREIGN KEY (field_id) REFERENCES field(id) ON UPDATE CASCADE ON DELETE CASCADE,
        CONSTRAINT fk_field_component_field_id2 FOREIGN KEY (component) REFERENCES field(id) ON UPDATE CASCADE ON DELETE CASCADE

)
;

将包含包含组成组件字段的字段的条目。仅当 field 表中的标志 is_composite 为 true 时，此处才会存在条目。

我认为一个更简单的解决方案需要更多的手动操作，而不是使用第二个表和标志，而是只使用一个字符串列，在其中放置使复合字段以逗号分隔的字段的 ID列表。

另外，我正在考虑将标志复合移动到名为 field_info 的第三个表中，我可能与该字段具有一对一的关系，并将包含有关每个字段的信息。即地面的大小、 Material 、是否是复合 Material 、相关注释等。

欢迎任何想法建议、批评、替代方案。

Answer 1

我会考虑以下内容，确保复合表field_component中的子竞技场和复合竞技场至少相同。请注意，不强制执行 InnoDB 检查约束。

第 1 点:下面的 field_component 隐式指向真正复合的内容的 is_composite 质量并未强制执行。它可能需要更多的合成(意味着更多的表格)。

第 2 点:在现阶段或以后，数据类型不应过度设计为小型和微小的 INT。尤其是对于 mysql 新手来说。

第 3 点:当 FK 关系不存在于子表中时，FK 关系倾向于自动为您创建 KEY。我们在 field_component 中明确拥有的唯一键实际上有两个目的。它强制执行非重复操作，并充当生成 FK 自动生成索引时使用的索引。另一种是自动生成的，如 show create table 中所示。因此，我们的唯一 key 有几个用途。

CREATE TABLE IF NOT EXISTS field (
    id              SMALLINT(5) UNSIGNED    NOT NULL AUTO_INCREMENT,
    arena_id        SMALLINT(4) UNSIGNED    NOT NULL,
    internal_id     TINYINT(3) UNSIGNED     NOT NULL,
    is_composite    BOOLEAN                 NOT NULL DEFAULT FALSE,

    PRIMARY KEY (id),
    UNIQUE (arena_id, internal_id),

    CONSTRAINT fk_field_arena_id FOREIGN KEY (arena_id) REFERENCES arena(id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS field_component (
    field_id        SMALLINT(5) UNSIGNED    NOT NULL,
    component       SMALLINT(5) UNSIGNED    NOT NULL,

    PRIMARY KEY (field_id, component),

    CONSTRAINT fk_field_component_field_id FOREIGN KEY (field_id) REFERENCES field(id) ON UPDATE CASCADE ON DELETE CASCADE,
        CONSTRAINT fk_field_component_field_id2 FOREIGN KEY (component) REFERENCES field(id) ON UPDATE CASCADE ON DELETE CASCADE

);

TweakA:

CREATE SCHEMA TweakA;
USE TweakA;

-- drop table arena
CREATE TABLE IF NOT EXISTS arena
(   id INT PRIMARY KEY,
    aName varchar(200) NOT NULL
);

-- drop table field
CREATE TABLE IF NOT EXISTS field
(   id INT AUTO_INCREMENT PRIMARY KEY,
    arena_id INT NOT NULL, -- like the Arena #
    internal_id INT NOT NULL, -- 1, 2, 3 for the field #
    is_composite BOOLEAN NOT NULL DEFAULT FALSE,
    friendly_name VARCHAR(100) NOT NULL,
    UNIQUE KEY (arena_id, internal_id),
    CONSTRAINT fk_field_arena_id FOREIGN KEY (arena_id) REFERENCES arena(id) ON UPDATE CASCADE ON DELETE CASCADE
);

-- drop table field_component
CREATE TABLE IF NOT EXISTS field_component
(   id INT AUTO_INCREMENT PRIMARY KEY,
    arena_id INT NOT NULL,
    child_internal_id INT NOT NULL,
    composite_internal_id INT NOT NULL,

    -- The following UK will pick up part of what I will explain in the Narrative
    UNIQUE KEY `unq_arena_comp_child` (arena_id,child_internal_id,composite_internal_id),

    CONSTRAINT fk_field_child_field_id FOREIGN KEY (arena_id,child_internal_id) 
        REFERENCES field(arena_id, internal_id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_field_composite_field_id FOREIGN KEY (arena_id,composite_internal_id) 
        REFERENCES field(arena_id, internal_id) ON UPDATE CASCADE ON DELETE CASCADE

    -- note that InnoDB check constraints are not effective
);

-- Note, look at output from the following
-- show create table field_component; -- this shows the auto-gen of 1 key due to FK
-- 

-- The following block is a Helper block during testing
-- Truncate in reverse order:
-- TRUNCATE TABLE field_component;
-- TRUNCATE TABLE field;
-- TRUNCATE TABLE arena;

-- test data load:
INSERT arena(id,aName) VALUES (1,'Boston Arena, North Shore');

INSERT field(arena_id,internal_id,is_composite,friendly_name) VALUES
(1,1,FALSE,'sa'),
(1,2,FALSE,'sb'),
(1,3,FALSE,'sc'),
(1,4,TRUE,'dab'),
(1,5,TRUE,'dac'),
(1,6,TRUE,'dbc'),
(1,7,TRUE,'abc');

INSERT field_component(arena_id,child_internal_id,composite_internal_id) VALUES
(1,1,4),
(1,2,4),

(1,1,5),
(1,3,5),

(1,2,6),
(1,3,6),

(1,1,7),
(1,2,7),
(1,3,7); -- SUCCESS

INSERT field_component(arena_id,child_internal_id,composite_internal_id) VALUES
(2,2,4); -- will fail, as expected

INSERT field_component(arena_id,child_internal_id,composite_internal_id) VALUES
(1,72,4); -- will fail, as expected

INSERT field_component(arena_id,child_internal_id,composite_internal_id) VALUES
(1,1,444); -- will fail, as expected

show create table field_component;
-- the above will exhibit the AUTO_INCREMENT gap anomoly due to the above
-- expected failed inserts, setting AI=13 or so

DROP SCHEMA TweakA;

我写了一个FK Enforces Composite Relationship答案有点复杂。您可以选择这条路线，具体取决于您正在寻找的数据库执行级别。

另请参阅 MySQL Using FOREIGN KEY Constraints关于由于 FK 关系而自动生成 KEYS，如第 3 点中所述。

因此，随着您的执行工作，这个答案可能会不断增长。或者在客户端进行。如果是我，我会做 DB Enforcement。

无论如何，正如评论中提到的，不要将 CSV 值存储在列中。