ASP 101 - Active Server Pages 101 - Web06
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:
Be Sure to Handle Errors to Continue Script Execution
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


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

by John Peterson

Introduction

I've got to learn to stop asking for input! Last week I asked what you thought of the initial pass at the database schema and boy did I get it. Instead of rushing to get to the next step, I'm making another pass over this. After all, once we settle on this (and I start entering sample data) I'd really like to have the design somewhat static so take a look at the new design and tell me what you think. Oops... I did it again! (I'm not a big Britney fan, but I couldn't resist.)

So... let's try this again...

What You Said

I've quoted and paraphrased from some of the email I got to help explain some of the issues you brought up.

Issue #1

...it is better to have an auto-incrementing int as a foreign key. It might be better to add a column in both your rating and genre tables that are ints and are auto-incrementing. Then you store those numbers into your movie table. I noticed you did this sort of thing for all the other tables. I realize that when you go to output the information on the movie/dvd, you will have a massive sql statement with some complicated joins, but in terms of scalability, having an identifier for ratings and genres might be best.

My Reply to Issue #1

You're right... I know you're right. I was thinking about the data those tables would contain and based on the assumptions I was making, I was thinking it would be easier to manage without the ints to try and alleviate the ugly queries we'll get as a result later, but when you're right, you're right!

Issue #2

Instead of just calling almost every primary key just "id" why not give it a more verbose name like "langaugeid" or "dvdid"? Might make queries easier to read down the road.

My Reply to Issue #2

Again a good point, but this one I'm gonna stand my ground on... I've been using this method for a while and personally I like it. Besides, considering all the joins we'll have, everything will probably be using table_name.field_name notation anyway so you'll see dvd.id or movie.id in most queries anyway.

Issue #3

... Tip when arranging your tables in the Diagram window: ON A 1 TO MANY RELATIONSHIP, MAKE THE ONE-SIDE POINT TO THE LEFT, AND THE MANY-SIDE POINT TO THE RIGHT...

My Reply to Issue #3

Thanks for the tip... I've tried this in this week's diagram. I ran into a few trouble spots, but for the most part I think it does help... Thanks.

Issue #4

Just wondering what the field region in the DVD table is for?

My Reply to Issue #4

This is for the regional encoding of the DVD. Over here in the USA, everything is basically region 1, but this can be a big deal for people from other contries since most players will only play DVDs encoded for their region.

Issue #5

I hate to tell you this, but you'll probably want "another table". Actually two other tables. One for Audio formats, and one to be a junction table between AudioFormat and DVD. You have multiple Audio formats listed in the DVD table, but this locks you in to only those types, or to having to create a new field any time a new format comes out (such as THX). ...

My Reply to Issue #5

Bingo... and this made things really interesting... I didn't add the intersection table I added this to the dvdlanguage table. Here's my reasoning... a DVD can have more then one language and each be a different audio format, but would you really want a query to return a movie as having Dolby Surround because the French track is in that format if you only speak English and the English track isn't encoded with Dolby Surround?

Basically we can now indicate which langage tracks are in which audio format and not just if a disc contains something in that format. And by making subtitles a type of audio format I've also eliminated that field making everything (except the queries we'll have to write later) nice and tidy.

Here's the Second Attempt

Well enough talk... here's what I've got so far. Same as last time... some basic scripts so you can see data types etc... Don't worrry... I'll provide a better set or a complete data file once we get this beast built. Oh and I included the relationships this time.

CREATE TABLE [dbo].[actor] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[firstname] [char] (10) NULL ,
	[lastname] [char] (10) NULL 
)
GO
CREATE TABLE [dbo].[audio] (
	[id] [tinyint] IDENTITY (1, 1) NOT NULL ,
	[format] [varchar] (50) 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 ,
	[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 ,
	[audio_id] [tinyint] NOT NULL 
)
GO
CREATE TABLE [dbo].[genre] (
	[id] [tinyint] IDENTITY (1, 1) NOT NULL ,
	[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_id] [tinyint] NULL ,
	[genre_id] [tinyint] 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] (
	[id] [tinyint] IDENTITY (1, 1) NOT NULL ,
	[rating] [char] (5) NOT NULL 
)
GO
ALTER TABLE [dbo].[actor] WITH NOCHECK ADD 
	CONSTRAINT [PK_actor] PRIMARY KEY  NONCLUSTERED 
	(
		[id]
	)  
GO
ALTER TABLE [dbo].[audio] WITH NOCHECK ADD 
	CONSTRAINT [PK_audio] PRIMARY KEY  NONCLUSTERED 
	(
		[id]
	)  
GO
ALTER TABLE [dbo].[dvd] WITH NOCHECK ADD 
	CONSTRAINT [PK_dvd] PRIMARY KEY  NONCLUSTERED 
	(
		[id]
	)  
GO
ALTER TABLE [dbo].[dvdlanguage] WITH NOCHECK ADD 
	CONSTRAINT [PK_dvdlanguage] PRIMARY KEY  NONCLUSTERED 
	(
		[dvd_id],
		[language_id],
		[audio_id]
	)  
GO
ALTER TABLE [dbo].[genre] WITH NOCHECK ADD 
	CONSTRAINT [PK_genre] PRIMARY KEY  NONCLUSTERED 
	(
		[id]
	)  
GO
ALTER TABLE [dbo].[language] WITH NOCHECK ADD 
	CONSTRAINT [PK_languages] PRIMARY KEY  NONCLUSTERED 
	(
		[id]
	)  
GO
ALTER TABLE [dbo].[movie] WITH NOCHECK ADD 
	CONSTRAINT [PK_movie] PRIMARY KEY  NONCLUSTERED 
	(
		[id]
	)  
GO
ALTER TABLE [dbo].[movieactor] WITH NOCHECK ADD 
	CONSTRAINT [PK_cast] PRIMARY KEY  NONCLUSTERED 
	(
		[movie_id],
		[actor_id]
	)  
GO
ALTER TABLE [dbo].[rating] WITH NOCHECK ADD 
	CONSTRAINT [PK_rating] PRIMARY KEY  NONCLUSTERED 
	(
		[id]
	)  
GO
ALTER TABLE [dbo].[dvd] ADD 
	CONSTRAINT [FK_dvd_movie] FOREIGN KEY 
	(
		[movie_id]
	) REFERENCES [dbo].[movie] (
		[id]
	)
GO
ALTER TABLE [dbo].[dvdlanguage] ADD 
	CONSTRAINT [FK_dvdlanguage_audio] FOREIGN KEY 
	(
		[audio_id]
	) REFERENCES [dbo].[audio] (
		[id]
	),
	CONSTRAINT [FK_dvdlanguage_dvd] FOREIGN KEY 
	(
		[dvd_id]
	) REFERENCES [dbo].[dvd] (
		[id]
	),
	CONSTRAINT [FK_dvdlanguage_language] FOREIGN KEY 
	(
		[language_id]
	) REFERENCES [dbo].[language] (
		[id]
	)
GO
ALTER TABLE [dbo].[movie] ADD 
	CONSTRAINT [FK_movie_genre] FOREIGN KEY 
	(
		[genre_id]
	) REFERENCES [dbo].[genre] (
		[id]
	),
	CONSTRAINT [FK_movie_rating] FOREIGN KEY 
	(
		[rating_id]
	) REFERENCES [dbo].[rating] (
		[id]
	)
GO
ALTER TABLE [dbo].[movieactor] ADD 
	CONSTRAINT [FK_movieactor_actor] FOREIGN KEY 
	(
		[actor_id]
	) REFERENCES [dbo].[actor] (
		[id]
	),
	CONSTRAINT [FK_movieactor_movie] FOREIGN KEY 
	(
		[movie_id]
	) REFERENCES [dbo].[movie] (
		[id]
	)
GO

Relationships

I'm assuming this is close to finished so I went ahead and saved the relationships this time.

Here's the new relationship diagram. I took the advice on laying it out (as best I could) so hopefully it'll be easier to read, but I had more trouble fitting it on the page so I had to do some quick image editing to get everything in and here's the end result...

Database Diagram (Version 2)

Anyone Want To Help

Don't even think about it!!!

Oh all right... I'll still take suggestions on improving it further.

Stay Tuned

Next week I'll start on some sample Stored Procs and maybe some of the DB interface code. No really I mean it this time! ;)

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