help needed on designing a database

[Jleagle]

I am creating a database on movies, with their CD colour and genre. Everything went well untill i came to the genre and realised that each movie had more than 1 genre. eg, action AND drama.

What i want in the end is a report that is grouped by the genre and under each genre heading lists all the movies which are part of that genre. So each movie can be displayed under multiple group headings.

This is what i have so far without the genre problem im having:



Thanks!

 

[Banana]

For genres, this is actually a Many-Many relationship. Do a search and read up on this to see how you want to execute this.

Also, you may want to look at normalization to be sure you have the tables structured right just in case.

 

[Jleagle]

Can you please give me an example on how to actually lay the database out. Would i need a joining table inbetween the genre and movie tables?

 

[georgedwilkinson]

Yes, is it called an associative or junction table and is the accepted method for implementing a M:M relationship. It would be named something like "MovieGenres", have an ID (PK Autonumber - just in case you need it later), MovieID (FK), GenreID (FK), Comments, etc., with a UK on MovieID, GenreID (so you don't have duplicates).

It will be your magic lookup table for either Movie based queries or genre based queries.

When creating a form to show this relationship, put either movie or genre in the main form and "MovieGenres" in the sub-form with a combo box on the "other" field within the table.

Feel free to post a follow-up question if this isn't clear enough.

 

[Jleagle]

OK thanks thats what i thought. I can do that on a form okay, but dont know how to put it into a report so a movie can be displayed under each one of its genres.

Like this:

movie 1: action, thriller
movie 2: drama, action
movie 3: drama, thriller


Drama:
movie 2
movie 3

Action:
movie 1
movie 2

Thriller:
movie 1
movie 3

 

[georgedwilkinson]

It would be similar to the form. Just use a join between the 3 tables and tell the report wizard which fields you want to display.

I just want to make sure you set up the relationships between the 3 tables first, though.

 

[gemma-the-husky]

personally, i think its a lot of time spent for very little on something as fuzzy as genre. The genre is debateable on most films - allowing multi genre is such a pain, and hardly worthwhile

don't you just hate websites that make you search for films by genre...

i would just keep it as a nominal single attribute in the movies table, and not bother allowing multiple genres.

eg - I have timeout film guide - there's no genre classification in there. The thing is if you only have a few films, the genre doesnt matter. If you have 10000 films, reducing a search to 1200 by selecting eg comedy will still leave a large dataset, but more importantly might omit some films the user thinks of as comedy, if you didnt classify the film as comedy

 

[Jleagle]

Thats what i was thinking, this is just a personal thing and i dont have more than 300 movies so i might leave the genre out. Thanks for the help, i think if i ever change my mind i would know what to do now.

 

[gemma-the-husky]

if its a personal thing, keep it - its just that assigning a single genre and searching on it, may not be that useful

on the other hand, you will have the same issue though storing eg, actors involved - again for each film record you will need to have multiple actors, in order to be able to search for all films with eg Robert De Niro, or Al Pacino - although you probably only need 1 director - and you probably do need to deal with this.

you will even get issues with film titles - eg how do you enter/and or sort films with the first word(s) A or The

eg

Godfather, The or The Godfather