[UPHPU] Database Speed Question

[Brandon Stout]

Webot Graphics wrote:

> Why wouldn't my first ones be normalized? I don't know what 
> information would be repeated. This is the information that I would 
> have (by no means is it meant to be exact)
>
> -------------- Movie Table --------------
> Movie_ID: 1, 2, 3
> Movie_Title: Little Rascals, The Incredibles, Pirates of the Caribbean
> Movie_Year_Released: 1994, 2005, 2005
> Rating_ID: 2, 2, 3
>
> -------------- Rating Table --------------
> Rating_ID: 1, 2, 3
> Rating_Code: G, PG, PG-13
> Rating_Description: Good clean fun, Parental Guidance needed, 13 and 
> Over with Parental Guidance

I would go with the 3 tables.  Sure, two tables will work if you only 
have one US rating.  However, what if you want to expand later?  You 
would have to rename your rating field and add columns to your movie 
table. I don't like underscores.  They are the most over-used and 
unnecessary programmer character.  There is a reason the underscore key 
is SHIFT + right-lazy-finger-up-two-rows - it's supposed to be uncommon. 
So, I'll use a slightly different naming convention:

MovieID
MovieTitle
MovieYearReleased
RatingUSA
RatingMEX

Plus, then you are not following one of the normalization rules - you 
have repeating data accross a row.  You can start with three tables, but 
with one extra field in the intermediate table:

  movies => (MovieID, MovieTitle, MovieYearReleased)
  movies2ratings => (RatingID, MovieID, CountryCode)
  ratings => (RatingID, Rating, RatingDescription)

It's even more forward compatible than this - you expand more.  If you 
need to account for different titles, different rating systems, you keep 
those three tables for *your* title, rating, etc.  Then you add tables like:

  countries (CountryCode, CountryName)
  countryRatings (CountryCode, CountryRating, MyEquivRating)
  countryTitles (CountryCode, CountryTitle, MovieID)
  countryReleaseDates (CountryCode, CountryMovYearReleased, MovieID)

In a nutshell, if you think you'll ever expand, look ahead and use the 3 
tables for easy expanding later.  If not do it anyway, because it's the 
better way ;) (kidding... or am I? someone run ~brastotack [someuser] on 
the irc chanel for me... ).

Brandon Stout
http://mscis.org