mysql - Database Structure/Design

Question

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!

Answer 1

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.