2

What's the normal way to design a relationship where every entry in table A is connected(1:n) to either an entry in table B or table C?

Or specifically: There are a tables for characters, movies, episodes and TV show season. One character is connected to either 1) a movie or 2) a show(in addition to n:n to season(season regular) and episode)

x can be the id for both a movie and a show.

Two identical tables(tv_characters and movie_characters)?

Is it better with two primary keys(one will always be either "movie" or "tv")?

5

Use a cross reference table to link characters to media, as in:

CREATE TABLE dbo.Characters
(
    CharacterID INT NOT NULL
        CONSTRAINT PK_Characters
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , CharacterName VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.MediaTypes
(
    MediaTypeID INT NOT NULL
        CONSTRAINT PK_MediaTypes
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , MediaTypeName VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.Media
(
    MediaID INT NOT NULL
        CONSTRAINT PK_Media
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , MediaName VARCHAR(100) NOT NULL
    , MediaTypeID INT NOT NULL
        CONSTRAINT FK_Media_MediaTypeID
        FOREIGN KEY 
        REFERENCES dbo.MediaTypes(MediaTypeID)
);

CREATE TABLE dbo.CharactersMedia
(
    MediaID INT NOT NULL
    , CharacterID INT NOT NULL
    , CONSTRAINT PK_CharactersMedia
        PRIMARY KEY CLUSTERED
        (MediaID, CharacterID)
);

INSERT INTO dbo.MediaTypes (MediaTypeName)
VALUES ('TV Show')
    , ('Movie');

INSERT INTO dbo.Characters (CharacterName)
VALUES ('Don Juan')
    , ('Don Johnson');

INSERT INTO dbo.Media (MediaName, MediaTypeID)
VALUES ('Miami Vice', 1)
    , ('Don Juan', 2);

INSERT INTO dbo.CharactersMedia (MediaID, CharacterID)
VALUES (1, 2)
    , (2, 1);

SELECT m.MediaName
    , c.CharacterName
    , mt.MediaTypeName
FROM dbo.Media m
    INNER JOIN dbo.CharactersMedia cm ON m.MediaID = cm.MediaID
    INNER JOIN dbo.Characters c ON cm.CharacterID = c.CharacterID
    INNER JOIN dbo.MediaTypes mt ON m.MediaTypeID = mt.MediaTypeID;

enter image description here

2

I don't like to use plural for table names
Most people would expect a table to have multiple rows

If the properties (columns) of character are the same if they are in a movie of a show then just one

character  
id pk identity  
lastName 
fistName 
DOB

movie 
id pk identity  
name 
studio 
date 

movie_character 
movieID     (fk movie.id)     pk 
characterID (fk character.id) pk 
characterName

show 
id pk identity 
name 
network 
season  

show_character 
showID      (fk show.id)      pk 
characterID (fk character.id) pk 
characterName

episode 
showID (fk show.id)
name  
date 

If you to exclude a character from possibly being in both a movie and a show then go with two character tables

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.