If the Internet Movie Database (or IMDb) website was implemented in SQL Server, how would the database structure look like? Here's an attempt of designing a database that will store different movies together with the associated actors and characters similar to the IMDb website.
Disclaimer: The author of this article does not work and has not worked with Amazon or with the IMDb website. It is just the author's attempt to design a database that will store different movies that may work for site such as IMDb. Any similarities to the database design used by IMDb are merely coincidental (and lucky).
There are three basic building blocks or basic elements when implementing a movie database and these are as follows:
- Movie - contains information about the movie such as the movie title, short description and a movie summary.
- Character - defines the different characters portrayed in a movie.
- Actor/Actress - contains information about the different actors and actresses.
CREATE TABLE [dbo].[Movie](
[MovieID] INT IDENTITY(1,1) NOT NULL,
[MovieTitle] NVARCHAR(200) NOT NULL,
[Description] NVARCHAR(max) NULL,
[Summary] NVARCHAR(max) NULL,
CONSTRAINT [PK_Movie] PRIMARY KEY CLUSTERED ( [MovieID] ASC )
)
GO
CREATE TABLE [dbo].[Character](
[CharacterID] INT IDENTITY(1,1) NOT NULL,
[CharacterName] NVARCHAR(200) NOT NULL,
[Description] NVARCHAR(max) NULL,
CONSTRAINT [PK_Character] PRIMARY KEY CLUSTERED ( [CharacterID] ASC )
)
GO
CREATE TABLE [dbo].[Actor](
[ActorID] INT IDENTITY(1, 1) NOT NULL,
[FirstName] NVARCHAR(100) NOT NULL,
[LastName] NVARCHAR(100) NOT NULL,
CONSTRAINT [PK_Actor] PRIMARY KEY CLUSTERED ( [ActorID] ASC )
)
GO
From this basic building blocks, the relationship can be established specifying the different characters portrayed in a movie and the actor or actress who portrayed the character. This relationship is stored in a separate table appropriately called the [dbo].[MovieCharacter] table.
CREATE TABLE [dbo].[MovieCharacter](
[MovieCharacterID] INT NOT NULL,
[MovieID] INT NOT NULL REFERENCES [dbo].[Movie] ( [MovieID] ),
[CharacterID] INT NOT NULL REFERENCES [dbo].[Character] ( [CharacterID] ),
[ActorID] INT NOT NULL REFERENCES [dbo].[Actor] ( [ActorID] ),
CONSTRAINT [PK_MovieCharacter] PRIMARY KEY CLUSTERED ( [MovieCharacterID] ASC )
)
GO
To list the different characters of a particular movie together with the actor portraying the character, the following query can be used:
SELECT B.[CharacterName], C.[FirstName], C.[LastName]
FROM [dbo].[MovieCharacter] A INNER JOIN [dbo].[Character] B
ON A.[CharacterID] = B.[CharacterID]
INNER JOIN [dbo].[Actor] C
ON A.[ActorID] = C.[ActorID]
WHERE A.[MovieID] = @MovieID -- For example, The Avengers
To list the different movies an actor has been part of as well as the character the actor portrayed in that movie, the following query can be used:
SELECT B.[MovieTitle], C.[CharacterName]
FROM [dbo].[MovieCharacter] A INNER JOIN [dbo].[Movie] B
ON A.[MovieID] = B.[MovieID]
INNER JOIN [dbo].[Character] C
ON A.[CharacterID] = C.[CharacterID]
WHERE A.[ActorID] = @ActorID -- For example, Tom Cruise
Lastly, to see a list of movies a particular character has been portrayed together with the actor or actress who portrayed the character, the following query can be used:
SELECT B.[MovieTitle], C.[FirstName], C.[LastName]
FROM [dbo].[MovieCharacter] A INNER JOIN [dbo].[Movie] B
ON A.[MovieID] = B.[MovieID]
INNER JOIN [dbo].[Actor] C
ON A.[ActorID] = C.[ActorID]
WHERE A.[CharacterID] = @CharacterID -- For example Superman
To make these queries execute faster, indexes can be created on the [MovieID], [ActorID] and [CharacterID] separately:
CREATE NONCLUSTERED INDEX [IX_MovieCharacter_ActorID] ON [dbo].[MovieCharacter] ( [ActorID] ASC )
GO
CREATE NONCLUSTERED INDEX [IX_MovieCharacter_CharacterID] ON [dbo].[MovieCharacter] ( [CharacterID] ASC )
GO
CREATE NONCLUSTERED INDEX [IX_MovieCharacter_MovieID] ON [dbo].[MovieCharacter] ( [MovieID] ASC )
GO