Tip of the Day : SQL Server Database Design - Internet Movie Database (IMDb)

Welcome to SQL Server Helper !!!

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

Should you have any comments or questions regarding this site or if you want to ask SQL Server-related questions, e-mail us here.

We hope we are able to help and we are glad to help!!!

SQL Server Tip of the Day - August 20, 2018

SQL Server Database Design - Internet Movie Database (IMDb)

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

SQL Server 2012

SQL Server 2008

User-Defined Functions

Date Functions

A collection of useful user-defined functions that deal with dates.

String Functions

A collection of useful user-defined functions that deal with strings (varchar/char/nvarchar/nchar).

Tree Functions

A collection of useful user-defined functions that deal with tree or hierarchical design structures.

Table-Valued Functions

A collection of useful table-valued user-defined functions that can be used to join with other tables.

SQL Server Built-in Functions

A reference to all built-in functions available within SQL Server grouped into categories.

Tips and Tricks

A collection of useful SQL Server-related tips and tricks:

SQL Server Error Messages

A list of SQL Server error messages and for certain error messages, discusses ways on how to solve the error or work around them:

Frequently Asked Questions