This week's book giveaway is in the Artificial Intelligence and Machine Learning forum.
We're giving away four copies of Transfer Learning for Natural Language Processing (MEAP) and have Paul Azunre on-line!
See this thread for details.
Win a copy of Transfer Learning for Natural Language Processing (MEAP) this week in the Artificial Intelligence and Machine Learning forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Paul Clapham
  • Devaka Cooray
  • Bear Bibeault
Sheriffs:
  • Junilu Lacar
  • Knute Snortum
  • Liutauras Vilda
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Tim Holloway
  • Piet Souris
Bartenders:
  • salvin francis
  • Carey Brown
  • Frits Walraven

Keeping track of movie people in a database

 
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Best ranchers!

I am having a real issue with trying to understand how to keep track of movie people in a media manager project of mine. I should mention that - as a rookie - database design is my weaker side when it comes to the whole table processing part. I have so many question concering this subject so I don't really know where to start. I know about foreign keys and how to use them in tables and link them together. But that's how far my knowledge reaches in this aspect of the matter.

I will try to outline my main problem as clearly as possible.

My GUI for this project is quite "simple". I have a main frame with a JTable and a JTabbedPane in a JSplitPane. And then there is a JDialog for inserting a movie. In the movie dialog I have a JTabbedPane with different panels labeled with titles like "Start","Edition","Video","Audio","Personal","Loan","Persons","Episodes" and "Plot". In the persons-tab I have two tables in a JSplitPane, one for cast people and the other is for crew people. The tables are registered to a JPanel. At the top of this JPanel I have three buttons labeled "Add" (person) "Delete" and "Edit". The "Add" button opens up a cast-info / crew-info JDialog with two JTextFields, one for entering the actors name and the other one is for entering the character name. When the user clicks the OK-button I insert the current data in a "tbl_castperson" / "tbl_crewperson" - table depending on which type of movie people the user is inserting. Now I have reached the part where I need help to continue.

Let's go back to the "Persons" - tab in my JDialog. In the "Persons" - tab I have a JButton labeled "Edit". It opens up a "EditPersonDialog" JDialog window. In this JDialog I have multiple textfields and other controls that takes care of information inputs referencing a movie person's full name, original name, birth date, death date, active state, birth place, country, biography and so on. When the user clicks the OK - button in this JDialog then I wan't to insert the current data in a larger table called "tbl_person". My problem is and so is my question - if I am on the right track and how I should proceed? My concern is also how to connect the actors to a title if there is no titles in the database like during a first run of the application? I am sorry if my problem sounds too complicated. But I just need good guidelines on how this is done in an "industrial" process.

Needles to say I am very curious about this matter. Hence I must point out that this post is a question of a basic nature and that I am sorry for not showing any examples of code in it.
But that might change later on.

Thank you so much for your time.

Best regards to everyone,
Robert!                      




 
Sheriff
Posts: 15519
263
Mac Android IntelliJ IDE Eclipse IDE Spring Debian Java Ubuntu Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It looks like you're trying to capture the relationship between a person and a movie. This kind of relationship has a Many-to-Many relationship, meaning one movie can be associated with many people and one person can be associated with many movies. In cases like this, you'd set up a mapping table. See this article: https://www.baeldung.com/jpa-many-to-many
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Best, Mr. Lacar!

Thank you so much for your kind reply. I am currently and struggling reading the article you posted the link to. It seems that I am getting closer to the answer. I have three person tables in my database, "tbl_castperson","tbl_crewperson" and a third, "tbl_filmperson". I am, shortly to say, linking theese together with foreign keys. But for instance, I can't figure out how to link them to a movie if there are no ones yet added in the database? I have a film_id key in my "tbl_filmperson" referencing to the id key in my main table "tbl_mediatitle". Here is what my person tables look like ( I have shorted them down a little bit from their original structure ) :







Something like that. I don't even know if I am on the right track as I mentioned earlier.

I am using the embedded java derby database for this project.

Best regards,
Robert!


 
Sheriff
Posts: 7051
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thinking about your tbl_filmperson table, how can a filmperson be both a cast and crew member?  And what if you needed another type (producers, maybe)?  So you need a fileperson_type table and a foreign key from that table to tbl_filmperson.  

As for film id, as Junilu said, you have a many to many relationship, so you need a join table.  This table is just the foreign key to the film and the foreign key to the person, with both being the primary key.  That way a filmperson could have zero or many films related to them.  Does this make sense?
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Best, Mr. Snortum!

I have been struggling with this issue for a long time and I still can't get a grip on it. I understand about many-to-many relationships in a database and joins. It's the design process that is the problem. In my case I have a lot of problem with just trying to figure out how to add filmpeople to their respective tables and link them together if there are no entries in the maintable. What you describe in your reply make perfect sense to me. Could you show me some example tables of the process you are describing?

Best regards,
Robert!    
 
Junilu Lacar
Sheriff
Posts: 15519
263
Mac Android IntelliJ IDE Eclipse IDE Spring Debian Java Ubuntu Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Robert Ingmarsson wrote:I have three person tables in my database, "tbl_castperson","tbl_crewperson" and a third, "tbl_filmperson".


Not a good choice, in my opinion. Cast, Crew, Film, those are all roles they play in the movie. I would create two tables for this:

1st table: map person <-> movie -- capture the association of a person to a movie: both fields are part of the primary key of this table, or you could generate a unique id for each person-movie.
  column 1 - person (foreign key to person table)
  column 2 - movie (foreign key to movie table)

2nd table: person-movie-roles --
  column 1 - foreign key to 1st table
  column 2 - role played by the person in the movie (could be a foreign key to a role table)
  This is the primary key for this table (person-movie-role)
  additional fields may include start/end dates in that role, salary for that role, etc.

This handles the scenario where the same person can play multiple roles, such as Director and Actor.

So if you want to see what movies a person has been in as an actor, you can do this. If you want to see which movies a person has directed, you can do it. If you want to see all the people involved with a movie in any capacity, you can do it, or in a specific role, you can do it as well.
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello, again!

I have now created two new tables one called "tbl_filmcast" and the other called, "tbl_filmcrew". They look like this





In the "AddCastDialog" the user enters an actors full name and character name in two textfield. This data is later inserted into the "tbl_castperson" table after which I insert it into the "tbl_filmcast" table with its last inserted id as a foregin key. I then have a "EditCastDialog" that have forms whose inputs are stored in the "tbl_filmcast" table after the user clicks the ok-button. Does this seems like the correct approach to take?

Please forgive the errors in my understanding of this procedure.

Regards,
Robert!    

 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Mr. Lacar

Thank you so much for your reply. It answered a lot of questions for me. I will apply that design to my project. But still one of my basic issues here is really bothering me. How do you link filmpeople to a movie in a database if there are no titles entered yet? This question is so basic and maybe *dumb* that I am almost ashamed for asking it. Don't even understand my own reasoning here, sorry for that.

Puzzled,
Robert!
 
 
Junilu Lacar
Sheriff
Posts: 15519
263
Mac Android IntelliJ IDE Eclipse IDE Spring Debian Java Ubuntu Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Robert Ingmarsson wrote:How do you link filmpeople to a movie in a database if there are no titles entered yet?


Well, sometimes the obvious is what's easiest to miss: you don't.

People can exist without being linked to a movie. It happens all the time for people who haven't gotten their break in the industry yet. You can add people to the people table even though there's no movie yet.

Now, to link them to a movie, you first need the movie that they will be linked to, of course. Once you have a movie in the movie table, you can create the entry in the people-movie join table that links people to the movie.
 
Knute Snortum
Sheriff
Posts: 7051
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Take the film_id out of the filmcast and filmcrew tables.  (Better yet, have one table, person, that has a type of cast, crew, or whatever).  Create another table that has the person id and the film id and both of those ids form the primary key:  My DDL is a little rusty, but...

CREATE TABLE person_film (
 person_id int REFERENCES person (person_id),
 film_id int REFERENCES film (film_id),
 CONSTRAINT person_film_pkey PRIMARY KEY (person_id, film_id)  -- explicit pk
);
 
Robert Ingmarsson
Ranch Hand
Posts: 170
2
Netbeans IDE PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Mr. Lacar

Thank you so very, very much for your reply. I must have missed out a lot. I just found out a solution to the "insert person" versus "no titles in the movie-table" dilemma. What if I disable the "Persons" tab in my JTabbedPane of my insert dialog "EditTitleDialog" and let it be in that state until the user have inserted a title in the database?

Best regards,
Robert!
 
Marshal
Posts: 25436
65
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you have a business rule which says "Every Person in the database must be linked to a Movie in some way" then yes, that would be a good way to do it. A design which allowed the user to go through and type in a page full of information about a Person and then pop up an error message saying they couldn't do that, I find that sort of design very annoying. Don't show me a form (or a button or a link or whatever) if filling or clicking it is sure to result in an error message.
 
Rancher
Posts: 4545
47
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:Thinking about your tbl_filmperson table, how can a filmperson be both a cast and crew member?



This is more common than you might think.
Many a director has appeared in their own film (Taika Waititi in Ragnarok for a recent example, or Terry Jones/Terry Gilliam in Holy Grail).
And if you add in Executive Producers then there's simply loads (though I can understand not calling them crew).
 
That's a very big dog. I think I want to go home now and hug this tiny ad:
Two software engineers solve most of the world's problems in one K&R sized book
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic