Topic: suggestion for movie database

[Jake]

My friend who owns a video store asked me to help him design a site for his video store. One of the features of the site would be a searchable/browsable data base of all the movies that he has.

Currently, all the titles are stored on a program he uses at the register. I looked through it, and it seems that the way that it stores title info could not be extracted for anything else, so basically I have to start from scratch.

What would be the best way of doing this? I got one of his employees to start making alphabetical text files with each title being on a seperate line. I was thinking that later  could use php to read the titles and put them into a mysql DB. one more thing, each title will have a link to the movie's description (if available) that will be a link to an external website...should this info be in the same file as the titles, or have a seperate corresponding file?

what do you guys think?

[Mike]

Depending on their exact wants I'd have a table like this:
Table name: movies
movieid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
title TINYTEXT
description (Either TINYTEXT or TEXT)

Table name: tapes
tapeid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
barcode (not sure of the type, maybe TINYTEXT)
movieid INT UNSIGNED
checkedOut TINYINT UNSIGNED
dueBack (Either INT with UNIX timestamp [my pref] or DATE)

Now if they want to extend it to say include which actors are in the movies I'd add two more tables:
Table name: actors
actorid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
name TINYTEXT
gender ENUM male/female  (optional)

Table name: cast
moveid INT UNSIGNED
actorid INT UNSIGNED
PRIMARY KEY (moveid, actorid)

I would then try to redo the checkout software so that when a particular tape (or dvd) is checked out it'll update the tapes table.

You now have the basis to search and show availablity (and if not availiable when its slated to be back)

[Rob]

Maybe include a genre too? (Horror / Sci-Fi etc). If you really wanted to go to town on it how about individualised suggestions of films based on previous rentals a la Amazon or Pandora?

[ober]

And keep in mind that almost anything can be extracted into another format... just food for thought.

[Jake]

the software that he already has can't be manipulated...he has to renew the liscence every year, so copyrights, etc etc.

Thanks for the suggestions...genre might be a good idea.

I'm sure I'll have more questions as I get started on this...especially with the sql :/

[hans]

Take a look at Netlix. That's basically what you're trying to develop right?

Maybe you could just give the customers a link to netflix?

 

[ober]

I'm not saying you should try to manipulate the software he has... but if you can read the file format, you should be able to get the data out of it programmatically.

[Mike]

You'll definatly need to develop some type of bridge between the two.  Because if you keep them totally seperate you will get bad data eventually.

[Perspective]

If your serious about building a database system that can handle a lot of data efficiently you should identify your funcional dependancies and do a normal form reduction to derive your tables. If you don't you can end up with a lot of redundancies or even inconsistencies.