mysql - 数据库结构/设计

Question

我想不出这个标题，所以甚至不知道从哪里开始自己研究。我必须创建一个数据库，其中有一个 CD/DVD 表，但它们的娱乐类型需要元数据/信息方面的不同属性，例如音乐 CD 具有艺术家、出版商、制作人、CDNo。等等。而一款软件可能有相似之处，但有一些音乐所没有的相似之处，并且可能与电影和游戏相同。所以我不确定这在 E-R 图方面是如何工作的，到目前为止我决定:

CD/DVD 位于商品表或库存表中，但名称尚未确定。

tbl_items -> item_id，item_format(DVD 或 CD，可能扩展为蓝光或高清 DVD)，item_entertainment_type(音乐、电影等) <--- 也许在另一个不确定。元数据表的外键，这样当交付新的 CD/DVD 时，如果元数据已经存在，我只需输入一个新项目，因此它是元数据和项目之间的一对多(项目 >-- 元)。

我认为的问题是，拥有可为空的外键字段并且只需选择要添加关系的字段是不好的做法，因此musicMeta_id INT NULL, FOREIGN KEY musicMetaID REFERENCES tbl_musicMeta(musicMeta_id)每种类型都这样？或者以某种方式合并它们，或者数据库有什么技巧。

我正在使用 MySQL 和 php。

谢谢!

Answer 1

没有外键不能为空的一般规则或最佳实践。很多时候，一个实体与另一个实体不建立关系是完全合理的。例如，您可能有一个跟踪的艺术家表，但目前您没有这些艺术家录制的 CD。

对于可以是音乐/音频或软件的媒体(CD、DVD、蓝光)，您可以有一个包含公共(public)信息的表，然后有两个外键，每个扩展表一个(音频数据和软件数据) ，但其中一个必须为NULL。这呈现出一种称为排他弧的情况。 这通常被认为是......有问题的。

想象一下 OO 语言(如 Java 或 C++)中的一个父类(super class)和两个派生类。在关系模式中表示它的一种方法是:

create table Media(
    ID      int not null, -- identity, auto_generated, generated always as identity...
    Type    char( 1 ) not null,
    Format  char( 1 ) not null,
    ... <other common data>,
    constraint PK_Media primary key( ID ),
    constraint FK_Media_Type foreign key( Type )
        references MediaTypes( ID ), -- A-A/V, S-Software, G-Game
    constraint FK_Media_Format foreign key( Format )
        references MediaFormats( ID ) -- C-CD, D-DVD, B-BluRay, etc.
);
create unique index UQ_Media_ID_Type( ID, Type ) on Media;
create table AVData( -- For music and video
    ID       int not null,
    Type     char( 1 ) not null,
    ... <audio-only data>,
    constraint PK_AVData primary key( ID ),
    constraint CK_AVData_Type check( Type = 'A',
    constraint FK_AVData_Media foreign key( ID, Type )
        references Media( ID, Type )
);
create table SWData( -- For software, data
    ID       int not null,
    Type     char( 1 ) not null,
    ... <software-only data>,
    constraint PK_SWData primary key( ID ),
    constraint CK_SWData_Type check( Type = 'S',
    constraint FK_SWData_Media foreign key( ID, Type )
        references Media( ID, Type )
);
create table GameData( -- For games
    ID       int not null,
    Type     char( 1 ) not null,
    ... <game-only data>,
    constraint PK_GameData primary key( ID ),
    constraint CK_GameData_Type check( Type = 'G',
    constraint FK_GameData_Media foreign key( ID, Type )
        references Media( ID, Type )
);

现在，如果您正在寻找电影，您可以搜索 AVData 表，然后与 Media 表连接以获取其余信息，依此类推，使用软件或游戏。如果您有 ID 值但不知道它是什么类型，请搜索 Media 表，Type 值将告诉您要连接三个(或更多)数据表中的哪一个。关键是 FK 引用的是通用表，而不是来自它。

当然，电影、游戏或软件可以在多种媒体类型上发布，因此您可以在 Media 表和相应的数据表之间建立交集表。 Otoh，这些通常标有不同的 SKU，因此您可能也想将它们视为不同的商品。

正如您所料，代码可能会变得相当复杂，但也不算太糟糕。 Otoh，我们的设计目标不是代码简单性而是数据完整性。例如，这使得无法将游戏数据与电影项目混合。并且您摆脱了一组字段，其中只有一个字段必须有值，而其他字段必须为空。