many-to-many

[taylorantone]

I have a database that holds a list of my DVD's. In that database I have a table called tblDVDlist that holds my DVD info (title, notes, year, etc.). I need to be able to list the genre of the movie as well. Problem starts because a movie may have multiple genres (Romance, Comedy, Action, etc.). I want the genres to remain static so that a movie can only have a certain list, so I created a 2nd table with the genre as a field and all of the different genres listed.

I have a web page that pulls the info from the tblDVDlist table. I need to be able to assign multiple genres to a movie, however multiple movies can have the same genre and vice versa.

This is really beginning to hurt my brain for some reason. How do I make this happen within the database itself?

I think I can figure out the web part through FrontPage later--if not, I'll ask more questions!!

 

[lupins46]

I can't help with the web aspects of this but you need a table which contains just the movie ID and a single genre id or description. YOu then create multiple records for the same movie in this table.

 

[LittleSmudge]

It's commonly called a 'Link Table' and just contains two fields being the Foreign Keys of the Primary keys in each of the two tables to be joined.

These two fields are then made into a single joint Primary Key for the link table.

You can then have other fields in this link table if appropriate to store additional information that relates to that one link between the two key tables. ( in your case I can't see that being necessary ).


'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[taylorantone]

Okay. This is what I did. tblDVDList has a the key defined as an autonumber called dvdID. tblGenre has the primary key defined as an autonumber called genreID. The juncion table (tblDVDGenre) has no primary but conatins two fields: dvdID and genreID, each field being related to the corresponding main tables with a one-to-many (one on the main table to many on the junction table).

Now, each of the main tables is already full of information, but the junction table is empty. Not sure how to start assigning the multiple genres to a movie.

 

[taylorantone]

Nevermind. Got it. Not enough sleep. I chaged the juntion table to choose from a drop-down box pulled from the values of each table, so instead of the dvdID or genreID showing in the junction table, it shows the either the DVD title or the actual genre. That way in the tables themselves, I can just drop the box down and choose a text option that makes a lot more sense.

Thanks again for your help!

 

[LittleSmudge]

Uggg -- thats totally non Normaised - Non-Codd etc.


What happens when someone issues another CD with the same title as one you already have ? In your system there is no way of defining which one of the wo you mean.
That's the whole point of having a Primary key in the table and using that PK in all Foreign Key references.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[taylorantone]

I might not have explained it well enough. The junction table still only holds and relates two fields--the keys from each of the two tables it joins. However, so that I can SEE what's what when I look at the table because autonumbers don't provide any meaningful data to a human, I have it set to SHOW the title value even though it's storing the key.

Also, two movies with the same title will ALWAYS have a small note next to it to help specify what it is. For example, Gone In 60 Seconds AND Gone In 60 Seconds (Original).