0

I am using TMDB api to get a list of popular, upcoming, top-rated and now-playing movies separately. I want to store them locally in a database which is updated daily. Each returned movie have a unique id. So, I want to store popular movies in a table named popular, upcoming movies in a table named upcoming and vice versa. If I follow this approach I will be creating four tables with same attributes but I can keep the movieId distinct to avoid redundancy like when the database is updated.

On the other hand if I create a single table then I cannot have movieId as distinct because a movie can be both popular and upcoming which will lead to redundancy when I update my database.Is there any way that a movie is only inserted in database if the movie with same id and same tag(popular, top-rated, upcoming, now-playing) is not present in the database?

How can I design my database so that I can easily get different list of movies from database as recieved from api?

Harry
  • 1,151
  • 11
  • 27

2 Answers2

3

Use one table: movies. Create a unique index on two fields. This will only allow one occurrence of this unique ID per type of movie: external_id and type:

CREATE TABLE `movies` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `external_id` INT NOT NULL,
  `type` ENUM('popular', 'upcoming', 'top-rated', 'now-playing') NOT NULL,
  `name` VARCHAR(45) NULL,
  `genre` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `external_id_type_idx` (`external_id` ASC, `type` ASC));

With this table structure you could use INSERT IGNORE statements when attempting to add movies:

INSERT IGNORE INTO `movies` (`external_id`, `type`, `name`, `genre`) VALUES (?, ?, ?, ?);

INSERT IGNORE will attempt to insert a record but if a constraint causes it to fail it will just be ignored(nothing will be inserted, updated, etc). See https://stackoverflow.com/a/548570/416412

To get a different list of movies(upcoming in this example), filter by type:

SELECT * FROM `movies` WHERE `type` = 'upcoming';
Community
  • 1
  • 1
james
  • 26,141
  • 19
  • 95
  • 113
  • really great answer. But how can i do the same thing in sqlite as enum are not available in sqlite? – Harry Jul 15 '15 at 23:15
  • the enum was just an example. You don't need to use an enum. if you look at @Xenon 's answer you can see that he suggests not using an `enum` but rather a separate table, `movie_types`. – james Jul 16 '15 at 12:42
1
  1. At minimum for a best practice, you should use two tables. 'Movies' and 'MovieType'. They can contain any number of columns but I will focus on the required ones here.
  2. 'MovieType' stores the details of the type of the movie. Columns will be MovieTypeId (primary key) and MovieTypeDescription (not null) - varchar - size depends. Store values such as (1, 'Popular'), (2, 'Upcoming') etc. in this table.
  3. 'Movies' stores the details of movies. Columns will be MovieId (not null) - int - auto increment, MovieName (not null) - varchar - size depends, MovieTypeId (not null) - tinyInt.
  4. You need to add 2 more CONSTRAINTS for Movies table. First is, MovieTypeId will be a foreign key referring to the MovieTypeId column in the MovieType table. 2nd will be a Composite Unique Key that combines (MovieId and MovieTypeId).

You are done.

Now you can store values such as

(Movie1, 1)

(Movie2, 2)

in the Movies table. A duplicate entry would throw an error. Of course, based on the API, you would like to handle it beforehand.

NOTE: I would personally prefer NOT to auto-increment MovieTypeId in the MovieType table because the category of the movies may change anytime including scrapping off from 10 categories to just 1 during which you will face problems.

Xenon
  • 36
  • 5