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...
