(yet another) Film Database

[Geoffo]

Hi,

I was wondering if anyone could point me in the right direction.

I am creating a database of my DVD collection so that I may create my own personal web site with the info (using MySQL and PHP). I have searched through these forums and have found some useful tips on how best to design the database (thank you guys).

I was wondering the best way to approach listing, say, the top three stars per film, i.e. each film will have a cast list, but obviously 1 or more of the cast are principle members for instance, take Die Hard - this has around 20 or more in the cast, but Bruce Willis, Alan Rickman and Bonnie Bedelia would be considered the 'stars' of the film and therefore I should list these three first on my page. I assume this would require some sort of seperate indication within the database? Would it be best to do it as a join table defining people's relationship to a film i.e.

Movie Table
film_id
title
etc, etc

Person Table
person_id
firstnames
surnames

Involvement Table
inv_id
category

(contains: actor, director, star)

Join Table (movie_person_involvement)
movie_id
person_id
involvement_id

(plus other tables for other info)

Also, I have noticed that everyone says to 'only store the data once', so with regard to the year of the film release, would it make sense to have a seperate table containing all the years and then associate these with the films or is that taking it too far?

I am a complete newbie to this and I am trying to get it right the first time, instead of finding I have to redesign the db at a later date.

Any help you could offer would be very much appreciated.

Many thanks,

Geoffo

[portaloo]

as to the actor listing i'd add another field to your actor table called lets say rank.

now this's kinda personal but you're gonna have to rank them yourself.

ok you get the movie id which gets you the actor listing,
next get all the actors for the film ordered by rank and alphabetically ascending
run this through a for loop with say 3 or 4 loops and it will display the top 3 or 4 stars of the film

as for the movie date, i'd put it with the film

[Geoffo]

Thanks for your input.

hmmm, ranking. I'm not sure that it would be the easiest option as I have around 500 films, therefore possibly about 3000 - 5000 actors. Ranking each would be a possible nightmare.

Would the design I used above not work at all? I was kinda thinking that when I create the form to input the film data I would have a few fields for the actors/director and have a check box next to each (for 'star' or 'principle cast member') and checking the box would set their 'involvement' as a 'star'.

Would this not work, or be difficult to implement?

Thanks again,

Geoffo

[Krait]

When designing a database, you need to consider this:

What will be the questions presented to the database?

In your case, I assume it will be things like:

• Who are the stars of film X?
• Who are the stars of films released in 1999?
• Which actor starred in which films and when?
• Which actor participated in the most films?

et cetera.

Although, looking back on your post, I believe that your design is optimal. Also, in my opinion, storing the date (or simply the year) of the movie's release alongside the movie data itself would be more optimal.

[Geoffo]

Thanks Krait.

The questions presented to the dvd database will be a little more simplified than those you detailed, as it will basically be a few web pages. Pages will obviously include details such as Title, Release Year, Film Synopsis, Cast List (listing 'stars' first), Director, Film Length, Film Genre and then dvd-specific details such as Special Features, Number of discs, Aspect Ratio, Disc Region, Certification, Subtitles and also Audio Output.

Searches will be kept to a minimum, like searches for films via keyword or containing certain actors, by a certain director or by genre. Also, a straight search for actors linking to actor/director pages listing films they have been involved in, in order of release year - with those listed linking to their 'film details page'. I don't think at this stage I would need to elaborate on the search options, although hopefully the design of the db will allow me to include most types of queries, if required in the future. I don't think I'm going to need to get into the realms of IMDB, more of the kind of thing you get when buying a DVD in an online shop.

Thanks for your input, it is much appreciated.

Geoffo

[Geoffo]

I would be happy to post the full MySQL design if anyone thinks it may help iron out any future pitfalls, if I may mix my metaphors.

[Krait]

The questions you specified will easily be answered by the design you posted.

If you'd like further verification, feel free to contact me or post the complete schema here.