DVD Database Design Question

Toggle sidebar Toggle sidebar
G

getoffb

Member
Jun 19, 2003
36
0
0
Since I am out of school for the summer, I want to learn PHP in my spare time. The way I have learned other languages in the past is by creating programs that I feel will be valuable to myself. I will also be learning dynamic web page development and about MySQL along the way.

So with PHP, I want to create a way to keep track of my DVD's, with a MySQL backend. I want to be able to add the DVD, along with any relevant information to that DVD.

After adding DVD's, I want to be able to search for items by title, actors, directors, etc.

So, this is what I feel should be in the tables for the DVD's. If there are any glaring problems please let me know.

isbn VARCHAR(12) NOT NULL
title VARCHAR(75) NOT NULL
rating VARCHAR(5)
genre VARCHAR(15)
studio VARCHAR(50)
director VARCHAR(50) NOT NULL
actor VARCHAR(50) NOT NULL
actor2 VARCHAR(50)
actor3 VARCHAR(50)
actor4 VARCHAR(50)
actor5 VARCHAR(50)
format VARCHAR(10)
aspectratio VARCHAR(5)
numdisc INT(2) NOT NULL
purchase DATE NOT NULL
PRIMARY KEY(isbn)

Those are the fields of the table tblDVD. Is there anything that should be changed in it?

Also, I would like to have a cover of each DVD when looking through the titles on hand. The way I think I should do this would be to have a field that would have the location of the covers picture. Is that a good way of doing it? Or should it be done in another way?

Thanks for the help!
 
mugs

mugs

Lifer
Apr 29, 2003
48,920
46
91
1. There are programs that do this very well without a ton of manual entry on your part. DVD Profiler for one.
2. Your data model is lacking in some areas, I'll explain why when I get home if someone else doesn't do it before then.
 
G

getoffb

Member
Jun 19, 2003
36
0
0
I know there are a lof of ways to catalog DVD's, most all of them better than what I will have. But that is not the point to me. The purpose of doing it is to learn some PHP, and along the way, see if I can catalog all of my DVD's.

I personally think guzzlefish is better than DVD Profiler;)
 
O

oog

Golden Member
Feb 14, 2002
1,721
0
0
put the actors in a separate table and create another table that has the primary key of the actor table and the dvd table (a many-to-many mapping). the actor table can contain other information like other names the person is credited as.

put studio in a separate table and refer to its id in the dvd table. do the same thing with rating, genre, format, aspect ratio.

consider making a separate auto-increment integer the primary key of the dvd table.

rename purchase as purchase_date.

consider separating information about your ownership of the dvd from the dvd itself -- if you want to track when it was purchased, that is separate from information about the disc itself. i would consider an inventory table that has a foreign key to the dvd table and tracks info like when it was purchased, for what price, was it used, how many copies you have, etc.

as for the picture, you can store it as a blob or store the location of the picture. either way works.

edit: formatting and some grammar.
 
T

torpid

Lifer
Sep 14, 2003
11,631
11
76
Comments inline below. You seem to have little understanding of data normalization. I don't know whether you care about this aspect or not. Varchar fields abound in your design when they should be FKs to "lookup" tables. This prevents you from having to do mass updates if you decide to change how something is represented (e.g. you decide to change Anamorphic to Anamorphic Widescreen). It also conserves space and facilitates easy reporting/searching.

You can have a url for the image, or you can just store the image itself. I'm sure this will be a hotly debated topic as it almost always is.

I suggest these additional tables:

Person - list of movie related people (name, dob, etc.)

Role - list of possible roles (actor, director, cinematographer, etc.)

MoviePersonRole - Each row represents a person's role in a movie (fk to Person and Role and Movie). Remember that actors can also be writers and directors (think woody allen / orson welles)

Genre - List of genres

MovieGenre - list of genres to which a movie belongs

Format - List of formats

AspectRatio - List of potential aspect ratios

Originally posted by: getoffb
Since I am out of school for the summer, I want to learn PHP in my spare time. The way I have learned other languages in the past is by creating programs that I feel will be valuable to myself. I will also be learning dynamic web page development and about MySQL along the way.

So with PHP, I want to create a way to keep track of my DVD's, with a MySQL backend. I want to be able to add the DVD, along with any relevant information to that DVD.

After adding DVD's, I want to be able to search for items by title, actors, directors, etc.

So, this is what I feel should be in the tables for the DVD's. If there are any glaring problems please let me know.

isbn VARCHAR(12) NOT NULL
title VARCHAR(75) NOT NULL Movies can have alternate titles, not sure if this matters to you or not.
rating VARCHAR(5) Why is this a varchar? If you are thinking 1-5 or 1-10 stars, this should be an integer or decimal
genre VARCHAR(15) Movies can belong to multiple genres. This should be a separate table with one movie genre per row
studio VARCHAR(50) What does this refer to? Distributor or what? Movies often have multiple actual "studios" working on them
director VARCHAR(50) NOT NULL See additional table above
actor VARCHAR(50) NOT NULL Ditto
actor2 VARCHAR(50) Ditto
actor3 VARCHAR(50) Ditto
actor4 VARCHAR(50) Ditto
actor5 VARCHAR(50) Ditto
format VARCHAR(10) At least this should refer to the format table. Might want a MovieFormat table too to list each format you own
aspectratio VARCHAR(5) Should refer to an AspectRatio table.
numdisc INT(2) NOT NULL Having this information in the table is non-normal because it describes data relating to the format field. May be too much work to move it, though
purchase DATE NOT NULL
PRIMARY KEY(isbn)

Those are the fields of the table tblDVD. Is there anything that should be changed in it?

Also, I would like to have a cover of each DVD when looking through the titles on hand. The way I think I should do this would be to have a field that would have the location of the covers picture. Is that a good way of doing it? Or should it be done in another way?

Thanks for the help!
Click to expand...
 
G

getoffb

Member
Jun 19, 2003
36
0
0
Thanks for all the help. The first thought of this DB was written down in about 5 minutes, so I apologize for not thinking through it enough. A revised DB is below. One thing that I am still struggling with is with the Actor/Director tables.

And by rating, I mean PG, PG-13, etc, not what rating I would give to a film.

Here is what I have:

DVD Table
dvdID AUTO INCREMENT
isbn
title
genreID
ratingID
formatID
aspect_ratioID
numdiscs - how else could I do this?
length
notes
PRIMARY KEY(dvdID)

Genre Table
genreID
genre

Rating Table
ratingID
rating

Format Table
formatID
format

Aspect Ratio
aspect_ratioID
aspect_ratio

Apart from the "People" tables, what am I missing?
 
mugs

mugs

Lifer
Apr 29, 2003
48,920
46
91
You're getting closer. But a movie could belong to more than one genre and a DVD could contain more than one aspect ratio.
 
G

getoffb

Member
Jun 19, 2003
36
0
0
How can I design it so that a movie could belong to more than one genre, etc?

Do I need to have another table for that?
 
mugs

mugs

Lifer
Apr 29, 2003
48,920
46
91
Originally posted by: getoffb
How can I design it so that a movie could belong to more than one genre, etc?

Do I need to have another table for that?
Click to expand...

Yeah, same as with the actors and directors
 
You must log in or register to reply here.

TRENDING THREADS

COMPANY
RESOURCES
FOLLOW
Top Bottom