Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2005
    Posts
    34

    Unanswered: How to get junction table to autopopulate?

    I'm very new to the concept of many to many relationships. I created a small test database about Movies and the Actors in them to illustrate the point of them. I totally understand the necessity for the junction table, but one thing I'm having a hard time wrapping my head around is how to get the junction table to continually auto-update with the new data as its added into the database? I use the Microsoft lookup wizard, but it doesn't seem to dynamically populate the values into the junction table. Is there a way to do this within Access automatically or will this be a query that I'll need to periodically run?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Not sure what you're asking. If you add a new movie, there's no way for Access to know what actors are in it, so how could it auto populate the junction table?
    Paul

  3. #3
    Join Date
    Oct 2005
    Posts
    34
    Quote Originally Posted by pbaldy
    Not sure what you're asking. If you add a new movie, there's no way for Access to know what actors are in it, so how could it auto populate the junction table?
    Maybe there's a flaw in my design?

    I currently have it going Movies (pk MovieID) -> Junction Table (fk MovieID, fk ActorID) -> Actors (pk ActorID). Because movies can have many actors, and many actors can have many movies, this seemed to make the most sense without creating duplication anywhere. Is this wrong? So for a site like IMDB, where I doubt their site is updated via data entry, most likely some kind of mass update process, how do they go about refreshing their junction table?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your design is fine, it is the "best practice" for a many-to-many relationship

    no matter how it's updated, whether by data entry or mass update, there is no escaping pbaldy's point -- the database doesn't know which actors are in which movies, you gotta tell it, and you tell it with INSERT statements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    As Rudy said, the design is fine and appropriate.

    I have no idea how IMDB gets its data, but there's no way it can magically populate the junction table. If I had to guess, they get data electronically from the movie's producers listing the actors (and probably all kinds of other data). They append that data into theirs. While there may not be a manual data entry process, there's no way they could "automatically" populate the table without getting the data from a source external to their database.
    Paul

  6. #6
    Join Date
    Oct 2005
    Posts
    34
    As a follow up question, this design works well for determining Actors to Movies. What if I wanted to throw in Producers, or Directors, or other film staff into the mix? How would those tables be added and related? Maybe instead of having an Actors table, I should have a People table and then another table that will tell me if they were an Actor, Director, or both an Actor or Director, etc.

    Movies (pk MovieID) -> Junction Table (fk MovieID, fk PeopleID) -> People (pk PeopleID) -> Role Table (fk PeopleID)

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you definitely are on the right track, and you show a good familiarity with data modelling

    what if the same person is both the director and lead actor in the same movie

    this is the point where some people always get mixed up, but you have taken it in stride

    nice job
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the only effective way Ive seen of doing this under the Access GUI is to have either a subform that contains a list of actors associated with the film and insert each actor as required (presumably form a gombo box, or mebbe a list box.

    you can have search capabilities built into the subforms footer.

    you could consider using an unbound combo whiich select the actorID based ont he uisers input.

    in your subform you have a query which displays the actors name based on the actorID retrieved from your intersection table.

    another approach is to use a unbound listbox containing actors in the film, adjacent to an unbound combo/listbox populated with Actors (preferably actors not already allocated to that film, and a couple of buttons to transfer actors from each list/combobox. that design approach is used quite a lot in the windows world, and seems pretty intuitive. Similar to how you decide which columns to include in a report, index or whatever. Undoubtably it requires a lot more programming, and it requires a lot more though about how you let the user interact with the form.. but it can be a lot slicker looking interface. You will need to put soem code behind various events, make sure any changes are saved before you move off the record or close the form.....

    you can extend it to allow for searches in the unallocated combobox, you could allow th euser to see the actors as 'Connery, Shaun' or Shaun Connery, heck there is no reason why ytou coldn't display 'em in ascending surname order but display as 'Shaun Connery'
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If I may - is the role not an attribute of the junction table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    what if the same person is both the director and lead actor in the same movie

    this is the point where some people always get mixed up, but you have taken it in stride
    Sorry - I have got mixed up. I'll try again.

    If I may - is the role not a part of PK of the junction table (or an additional table)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •