0

Postgresql v. 14

I want to be able to query all genres related to a movie in a efficient and scalable way, I thought of these approaches and need help deciding, if you know a better approach feel free to post it.

Problem:
I have 2 tables. 1 is movie that contains information related to a movie and the 2nd table is genre in which it contains a list of genres that can be added to movies. Each movie can have 0/null or many genres.

Solution 1:
Create a new table called movie_genre which references movie_id and genre_id. This will need to be maintained by a trigger to keep it updated once a new movie gets inserted.

OR

Solution 2:
Create a new column in movie called genres which contain array of integers that are linked with genre ids, then I'll be able to query the genres like so:

select genre.* from genre join movie on genre.id = any(movie.genres) where movie.id = 1;

Which do you think is a more sensible way of doing it? Would like to hear from you.

Alon Eitan
  • 11,997
  • 8
  • 49
  • 58
Renewablezip
  • 44
  • 1
  • 5

1 Answers1

1

Solution 1. Definitely. RDBMSs work best when the tables are normalized.

Please, don't consider Solution 2 any further. If you do, your co-workers and yourself a year from now will curse your name.

Seriously.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • @Renewablezip: why would you need a trigger? If you create a new movie, you should know to which genres it belongs to, so just insert into both tables in a single transaction. Besides: how would the trigger know to which genre the movie belongs to? –  Dec 31 '21 at 12:42
  • Annoying to maintain that extra table? Compared to how annoying it will be to search for, let us say, horror movies with an IT theme? Seriously, almost all SQL-based applications with many-to-many relationships use the extra table, sometimes called a JOIN table. – O. Jones Dec 31 '21 at 13:05