数据库结构/设计 [英] Database Structure/Design

问题描述

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

CD / DVD在项目表或库存表不确定名字。

tbl_items - > item_id，
item_format（DVD或CD，可能是蓝光或蓝光dvd），
item_entertainment_type（音乐，电影等）< ---可能在另一个不确定。
元数据表的外键，这样当如果元​​数据已经存在的话，当我们输入新的CD / DVD时，我只输入一个新的项目，所以它在元数据和项目之间是一对多的（项目> - - 元）

我认为的问题是，有空的外键字段是不好的做法，只是选择添加一个关系，所以 musicMeta_id INT NULL，FOREIGN KEY musicMetaID参考文献tbl_musicMeta（musicMeta_id）
为每种类型？或者以某种方式合并他们，或者是有一个技巧数据库。

我正在使用MySQL与php。

谢谢！

解决方案

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

至于有媒体（CD，DVD ，BluRay），可以是音乐/音频或软件，您可以拥有一个共享信息的表，然后是两个外键，一个到每个扩展表（AudioData和SoftwareData），但一个必须是 NULL 。这提出了一个除了别的以外的排他弧。 这个通常被认为是有问题的。

想象一个超类和两个派生类的OO语言，如Java或C ++。在关系模式中表示的一种方法是：

  create table Media（
 ID int not null， - 身份，auto_generated，始终作为身份生成
类型char（1）not null，
格式char（1）not null，
 ...<其他公用数据> 
约束PK_Media主键（ID），
约束FK_Media_Type外键（类型）
引用MediaTypes（ID）， -  AA / V，S-Software，G-Game 
约束FK_Media_Format外键（格式）
引用MediaFormats（ID） -  C-CD，D-DVD，B-BluRay等
）; 
在Media上创建唯一索引UQ_Media_ID_Type（ID，Type）; 
创建表AVData（ - 用于音乐和视频
 ID int not null，
 Type char（1）not null，
 ...<仅音频数据> 
约束PK_AVData主键（ID），
约束CK_AVData_Type检查（Type ='A'，
约束FK_AVData_Media外键（ID，类型）
引用媒体（ID，类型） 
）; 
创建表SWData（ - 对于软件，数据
 ID int not null，
类型char（1）not null，
 ...<仅限软件数据> 
约束PK_SWData主键（ID），
约束CK_SWData_Type检查（Type ='S'，
约束FK_SWData_Media外键（ID，类型）
引用媒体（ID，类型）
）; 
创建表GameData（ - 对于游戏
 ID int not null，
 Type char（1）not null，
。 ..<仅游戏数据> 
约束PK_GameData主键（ID），
约束CK_GameData_Type检查（Type ='G'，
约束FK_GameData_Media外键（ID，类型）
参考媒体（ID，类型）
）; 
  

现在，如果您正在寻找电影，您搜索AVData表，然后加入Media表为了其余的信息等等与软件或游戏。如果您有ID值但不知道是什么样的，请搜索Media表，Type值将告诉您要加入的三个（或多个）数据表中的哪一个。关键在于，FK将指向通用表，而不是它。

当然，电影或游戏或软件可以在多种媒体类型上发布，因此您可以在 Media 表和相应的数据表之间设置交叉表。 Otoh，那些通常用不同的SKU标记，所以你可能也想把它们当作不同的项目。

代码，正如你所期望的，可以变得相当复杂，虽然不是太差Otoh，我们的设计目标不是代码简单，而是数据完整性。这使得不可能将例如游戏数据与电影项目进行混合。而你可以摆脱一组字段，其中只有一个必须有一个值，而其他字段必须为空。

I couldn't think of a title for this and so didn't even know where to start researching for myself. I have to make a database where I have a table for CD/DVDs but the type of entertainment on them requires different attributes in terms of metadata/information for example music CDs have artist, publisher, producer, CDNo. etc. Whereas a piece of software may have similarities but has some that music wont have and likely the same with movies and games. And so I'm not sure how this would work in terms of an E-R diagram, so far I decided on:

CD/DVDs being in the items table or stock table not sure on the name yet.

tbl_items -> item_id, item_format(DVD or CD, maybe axpand to blu-ray or hd-dvd), item_entertainment_type(Music, Movie etc.) <--- Maybe in another not sure. foreign key to a metadata table, this is so that when deliveries for new CD/DVDs are made if the metadata already exists I just enter a new item and so its a one to many between metadata and items (items >-- meta).

The question I think is, is it bad practice to have null able foreign key fields and Just choose which to add a relation to, so musicMeta_id INT NULL, FOREIGN KEY musicMetaID REFERENCES tbl_musicMeta(musicMeta_id) like that for each type? or somehow merge them, or is there a trick databaes have.

I'm using MySQL with php.

Thanks!

解决方案

There is no general rule or Best Practice the foreign keys should not be nullable. Many times it makes perfect sense for an entity not to have a relationship with another entity. For example, you may have a table of artists you track but, at the moment, you have no CDs recorded by those artists.

As for having Media (CD, DVD, BluRay) that can be either music/audio or software, you can have a table with the information in common and then two foreign keys, one to each extension table (AudioData and SoftwareData), but one must be NULL. This presents a situation called, among other things, an exclusive arc. This is generally considered to be...problematic.

Think of a superclass and two derived classes in an OO language like Java or C++. One way to represent that in a relational schema is:

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 )
);

Now if you are looking for a movie, you search the AVData table, then join with the Media table for the rest of the information and so on with software or games. If you have an ID value but don't know what kind it is, search the Media table and the Type value will tell you which of the three (or more) data tables to join with. The point is that the FK is referring to the generic table, not from it.

Of course, a movie or game or software can be released on more than one media type, so you can have intersection tables between the Media table and the respective data tables. Otoh, those are generally labeled with different SKUs so you may want to also treat them as different items.

The code, as you might expect, can get fairly complicated, though not too bad. Otoh, our design goal is not code simplicity but data integrity. This makes it impossible to mix, for instance, game data with a movie item. And you get rid of having a set of fields where only one must have a value and the others must be null.

这篇关于数据库结构/设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！