ASP 101 - Active Server Pages 101 - Web01
The Place ASP Developers Go!

Please visit our partners


Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates














ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash



 Top ASP 101 Stories Top ASP 101 Stories
Getting Scripts to Run on a Schedule
The Top 10 ASP Links @ Microsoft.com
What is Adovbs.inc and Why Do I Need It?

QUICK TIP:
On Error Don't Resume Next
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


Building an ASP.NET App: John's DVD Catalog - Part 3: Database Design

by John Peterson

Introduction

This week we're working on the table structure. This has never been my favorite part, but it is one of the most important. You wouldn't believe the number of times in the past week that I've said "Another table? ...but this is a simple little app!"

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.

Database Diagram

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!

Anyone Want To Help

While I'm fairly happy with the design so far, please realize it will almost certainly change in the coming weeks as I run into problems. If you've got any ideas how I can make it better now and avoid some of those problems, drop me a line and let me know.

Stay Tuned

Next week I'll start on some sample Stored Procs and maybe some of the DB interface code. After all, now that we've got the data we need to be able to get to it now don't we. And we'll finally get to see some ASP.NET code.

Project Links

Building an ASP.NET App: John's DVD Catalog
Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers