[Mini Project] Part 1. Database Schema


Let’s kick off the Mini Project by examining the database schema.  The project involves storing and manipulating data related to movies (in my case, DVDs I own).

For this project I am going to concentrate on three key relationships:

1. Movies have at least one director
2. Movies have one or more cast (actors/actresses)
3. Movies belong to one or more genres (e.g. Horror, Drama)
4. A Director may direct more than one film
5. Actors/Actresses may appear in more than one film

As such, I have modelled the database schema which you may observe from the diagram below:

image

You may download a T-SQL script to create this schema from this location here.  Requires Microsoft SQL Server 2005 or Microsoft SQL Server 2008.

I’ve also published a PDF version (originally from Microsoft Visio) of the schema here.

Some notes about the schema:

– This is a basic schema, and the lack of complexity is intentional
– This schema is meant to hold only a couple of thousand records (perhaps around 2,000 rows in the Movie table) at best, so it has not been tuned for high volume or ‘chatty’ small queries.
– Join tables contain composite primary keys for reasons explained in later posts

Lastly, the schema has some specific reasons for the column types which shall become clearer soon.

Stay tuned for Part 2, coming shortly.


About Rob Sanders

IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>