Skip to content

Instantly share code, notes, and snippets.

@mrcsparker
Created November 11, 2015 01:42
Show Gist options
  • Save mrcsparker/bd185fbd46f795eb8341 to your computer and use it in GitHub Desktop.
Save mrcsparker/bd185fbd46f795eb8341 to your computer and use it in GitHub Desktop.
CREATE DATABASE IF NOT EXISTS `movies`;
USE `movies`;
CREATE TABLE IF NOT EXISTS `movies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL DEFAULT '',
`year` int(11) unsigned,
PRIMARY KEY (`id`),
KEY `movies_year` (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `genres` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `genres_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `links` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`movie_id` int(11) NOT NULL,
`imdb_id` varchar(255) DEFAULT NULL,
`tmdb_id` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `links_movie_id` (`movie_id`),
CONSTRAINT `fk_links_movie`
FOREIGN KEY (`movie_id`) REFERENCES `movies` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `movie_genres` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`movie_id` int(11) NOT NULL,
`genre_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `movie_genres_movie_id_genre_id` (`movie_id`,`genre_id`),
CONSTRAINT `fk_movie_genres_movie`
FOREIGN KEY (`movie_id`) REFERENCES `movies` (`id`),
CONSTRAINT `fk_movie_genres_genre`
FOREIGN KEY (`genre_id`) REFERENCES `genres` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `ratings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`movie_id` int(11) NOT NULL,
`rating` decimal(11,2) NOT NULL,
`timestamp` bigint(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `ratings_movie_id` (`movie_id`),
KEY `ratings_user_id` (`user_id`),
KEY `ratings_rating` (`rating`),
CONSTRAINT `fk_ratings_movie`
FOREIGN KEY (`movie_id`) REFERENCES `movies` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `tags` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`movie_id` int(11) NOT NULL,
`tag` varchar(255) NOT NULL DEFAULT '',
`timestamp` bigint(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `tags_movie_id` (`movie_id`),
KEY `tags_user_id` (`user_id`),
KEY `tags_tag` (`tag`),
CONSTRAINT `fk_tags_movie`
FOREIGN KEY (`movie_id`) REFERENCES `movies` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment