-1

I am drawing an ER diagram. Movies (title, year, and rating) are shown in theaters (name and city). A movie can be shown in many theaters and a theater can show many movies.

"Show" should hold theater_name and title of the movie. Should "show" be the relation between movie and theater or should I draw a "showing" relation and link "show" entity to it?

Peter Mortensen
  • 28,342
  • 21
  • 95
  • 123

2 Answers2

1

Data Normalisation

Where you state:

"Show" should hold theater_name and title of the movie.

Consider normalising your data such that information pertaining to each distinct entity is held in a single table containing only a single instance of such entity.

For example, information pertaining to a movie (including its title) would be stored in the Movies table, and only in this table. Information pertaining to a theatre would be stored in the Theatres table and only in this table.

Each record in the Movies and Theatres tables would then be uniquely referenced using a separate primary key field (e.g. Movie_ID and Theatre_ID), for which I would recommend using a Long Integer Autonumber field.

From there on, whenever referencing a movie or a theatre in other tables, you would use only the relevant ID, and not carry across any other information.

Not only does this optimise the size & performance of your database (since integers occupy significantly less memory and can be indexed far more efficiently than strings), but you also need only maintain the data in a single location: if the name of a movie changes, you only need to change the data in the Movies table, and this will be automatically reflected by any query which references this table.

Junction Tables

To answer your question regarding how to represent the showing of movies in multiple theatres, with multiple theatres also showing the same movie: this many-to-many relationship requires the use of a junction table, also known as a transaction table, bridge table, link table, and many other names besides.

Such table provides a junction at which the Movies & Theatres table meet.

Bearing in mind the above advice concerning data normalisation, such a junction table should only include the unique ID for each Movie & Theatre, not the theatre name or movie title.

For example, such a table might look this like:

+------------+------------+----------+
| Showing_ID | Theatre_ID | Movie_ID |
+------------+------------+----------+
|          1 |          1 |        2 |
|          2 |          1 |        3 |
|          3 |          2 |        1 |
|        ... |        ... |      ... |
+------------+------------+----------+
Community
  • 1
  • 1
Lee Mac
  • 14,469
  • 6
  • 23
  • 70
  • thank you! I only link movie and theatre with "show" relation. So, title and theatre name become the foreign keys of the relation –  Mar 19 '19 at 13:37
  • @gzdddc I think you need to go back and re-read what Lee said. You want separate key IDs for your movies. How do you deal with two different versions of "Psycho" otherwise if the name is the key? Try to normalize as much as possible before building anything. – SmrtGrunt quit because Monica Mar 19 '19 at 13:50
  • I should use "title" as a primary key. ID would be better as you said but I need to draw it according to the informations in the question –  Mar 19 '19 at 14:15
  • @gzdddc If this is a homework assignment which is forcing you to use a description as a foreign key, I think you need to teach your teacher about database normalisation (or find a better teacher). – Lee Mac Mar 19 '19 at 20:00
0

For creating an n-to-n relationship in a relational database you need a bridge table with two relationships, one per each table.
First of all, both Movie and Theaters need a column as primary key (e.g. Id) (varchar columns are not appropriate PKs and FKs in terms of performance). You also need a bridge table as you could have guessed (e.g. Show) with two foreign keys one MoveiId and one TheaterId.

rad
  • 3,387
  • 1
  • 14
  • 19