I am trying to create a database where some of the data is related to movies. I would like to return information on a given movie that displays a undefined number of cast members.
Also, if i can ask another question, can I define a field that only returns two options, like, Movie or TV Show.
Also, can a button be used to display the cast members, only when pressed.
And what is the terminology used if I want the data input from different forms. Am I to create a different database or a different table. I want the cast member records stored separate from the movie records, but linked by a common field.
Yes, you should use related tables for this.
- Movies Table – stores movie info.
- Cast Table – stores cast members, with a MovieID field linking to the Movies table.
This allows an undefined number of cast members per movie.
- To show only two options (Movie/TV Show), use a Value List or a field with a CHECK constraint.
- You can use a button with a macro or a subform to display cast members on demand.
- Input from different forms is handled with multiple forms linked to the same table; you don’t need a separate database.
This is standard one-to-many relational design.
Every person can collaborate in many movies. Every movie depends on many persons. This is called a many-to-many relation (m-n).
There is only one state-of-the-art method to represent m-n in a relational database.
Having a list of persons and a list of movies, you need a third table mapping the primary keys of persons to the primary keys of movies.
When defining input forms, you use one of the lists as main form and the mapping table as subform with a grid control. The grid control has a column of list boxes representing the items of the other list.
Have a look at https://ask.libreoffice.org/uploads/short-url/lI5PzUZiuE00EHzjr0sytsNo0l3.odb where each person has one pet (1-n) and many tools (m-n).
The “Persons” form for instance shows a person name in the top-left corner, the navigation bar navigates from person to person and the yellow grid shows the person’s tools.
Likewise “Things” form shows a tool name in the top-left corner, the navigation bar navigates from tool to tool and the orange grid shows the persons owning the tool.
On database level, the relations window looks like this:
I would like to return information on a given movie that displays a undefined number of cast members.
This would be a many-to-many (m-n) relationship between two tables. In a relational database this is implemented by a separate table linking to either of the two primary tables.
Movie(ID, Title, Year, ... )
Person(ID, Name, ...)
MovieActor(MovieID, PersonID)
The main table for a form will often be that “secondary” table, where movie and person data are in “dependent” tables and hence belonging in subforms. (More about subforms below.)
a field that only returns two options
The “traditional” approach to this is to use a separate table for the options and link the value (directly so the actual option is stored in the main table, or by way of an ID field). In a form you need to use a subform for this. A subform in this context is not a “separate canvas” but only a way to conceptually group fields (using the form navigator) so they are seen as “dependent”.
Some database tools will offer a more direct approach, but in my experience that makes the end product less portable. I never used it myself, but have come across a good number of cases with MS Access databases which use this, and are unmanageable by any other product that MS Access.
… terminology used if I want the data input from different forms.
Simply that, “different form”. You can make multiple forms connected to the same set of data.
I want the cast member records stored separate from the movie records, but linked by a common field.
This is a good idea, and indeed a step towards what I explained for the first question. Since an actor can participate in multiple productions, and any one production will have multiple participants, you cannot have a single field in either table to point to the other. This is the simple definition of a m-n relationship and the reason why you need the third table.
Thanks for your help.
Have a look at this:
movies_jobs.odb (40.6 KB) (replaced one time, bug fix)
3 lists of movies, persons and jobs. For simplicity, each item has a name only.
Each person can have zero, one or many jobs in a production. Which persons served which jobs in which productions is mapped in 3 columns of table “JMP”.
3 forms for movies, persons and jobs, each one having “JMP” as subform. The subform shows all related items of the selected main form item.
I started to fill in some data based on the filmography of Clint Eastwood who served as actor, director, producer and lyricist in countless productions.
As an extra, each form has 2 extra forms below the subform, where you can enter new items that do not yet appear in the list boxes. In order to make this extra as comfortable as possible, you have to enable macro execution for the database document. Without macro execution, you need to refresh the list boxes manually.