ASP 101 - Building an ASP.NET App: John's DVD Catalog

Consolidation of Info

In my mind, one of the most important things in setting up your database is to eliminat repetitive data. You want to do this for the same reason you try and eliminate repetitive programming code: to make chages easier and to keep things uniform. For example, if I sometimes spell out "New York" and other times use "N.Y." (and the database lets me), how can you easily be sure you've found all the records in the database that relate to the Empire State? Worse yet... what if I type "New Yorm" by mistake? (Hey... you should see me type!) Well the answer is simple... you can't.

And there are other good reasons too... storage space and speed. I just had to pick state names for this example didn't I? Well in this case the storage issue isn't a big one, but lets think about our DVD project for a second. If I listed each actor's name with every movie they'd been in, think of the number of times you'd have Sean Connery or Robert De Niro listed. If you instead create a table of Actors then you can store all the actor's info in one place and simply reference it instead of storing it all with every movie they've been in.

Here's the First Attempt

I tend to start with my main table which in this case is DVD. Well I quickly discovered DVD is not the same as movie so there's table #2. After that I needed some look up tables for those tables which led to genre, actor, language, and rating... and that's where I'm at basically. Well almost...

Let's take the movie / actor relationship. I couldn't very well just set a fixed number of actors per movie and list the actors in the movie table and at the same time I couldn't just list the movies an actor is in inside the actors table. So you end up with these intersection tables. I've got two of them so far (dvdlanguage and movieactor). They basically just point at records in the two other tables and combined create a unique item. For example... think of the movieactor table as a cast table. It currently lists Roy Scheider as the main character Cheif Martin Brody in the movie Jaws. It does this by containing the id for Jaws and Roy Scheider in the same row as the character name and data.

Well enough talk... here's what I've got so far... I didn't incude the deletion scripts, but here's the basic SQL 2000 creation script for the tables. This is more for you to read then actually use... when I ship a running version of the code, I'll probably give you a complete mdf file or a complete SQL creation script.

CREATE TABLE [dbo].[actor] ( [id] [int] IDENTITY (1, 1) NOT NULL , [firstname] [char] (10) NULL , [lastname] [char] (10) NULL ) GO CREATE TABLE [dbo].[dvd] ( [id] [int] IDENTITY (1, 1) NOT NULL , [movie_id] [int] NOT NULL , [sku] [char] (12) NULL , [isbn] [char] (10) NULL , [edition] [varchar] (255) NULL , [screen_format] [char] (10) NULL , [audio_dolbysurround] [bit] NOT NULL , [audio_dolbydigital51] [bit] NOT NULL , [audio_dolbydigitalex] [bit] NOT NULL , [audio_dts] [bit] NOT NULL , [audio_dtses] [bit] NOT NULL , [region] [tinyint] NULL , [bought_date] [datetime] NULL , [bought_price] [money] NULL , [lent_to] [datetime] NULL , [lent_date] [datetime] NULL , [comments] [varchar] (255) NULL , [features] [text] NULL ) GO CREATE TABLE [dbo].[dvdlanguage] ( [dvd_id] [int] NOT NULL , [language_id] [tinyint] NOT NULL , [subtitle] [bit] NOT NULL ) GO CREATE TABLE [dbo].[genre] ( [genre] [varchar] (255) NOT NULL ) GO CREATE TABLE [dbo].[language] ( [id] [tinyint] IDENTITY (1, 1) NOT NULL , [language] [varchar] (25) NOT NULL ) GO CREATE TABLE [dbo].[movie] ( [id] [int] IDENTITY (1, 1) NOT NULL , [title] [varchar] (255) NOT NULL , [length] [smallint] NULL , [rating] [char] (5) NULL , [genre] [varchar] (255) NULL , [release_date] [datetime] NULL , [imdb_id] [char] (7) NULL ) GO CREATE TABLE [dbo].[movieactor] ( [movie_id] [int] NOT NULL , [actor_id] [int] NOT NULL , [character] [varchar] (255) NOT NULL , [sequence] [int] NOT NULL ) GO CREATE TABLE [dbo].[rating] ( [rating] [char] (5) NOT NULL ) GO

You'll also notice that there aren't any indecies or relationships defined yet. I'm waiting until I get a little more settled on the design before I go through and make them. If you've ever tried to change a data type that's involved in a relationship with another table you know why!

Here's what I'm envisioning the relationships looking like however. Pardon the "squished" appearence... I was trying to keep the image relatively compact since I knew it was being captured for the web.

So that's where we are... we'll revisit these tables later as we build the scripts to interface with them and once their design is somewhat stable, maybe I'll publish one with some data... that is if I ever get around to entering it!