Macaw-Movies/Development/Database schema

peoples

Column Name	Type	Foreign key
id	INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE
title	VARCHAR(255) NOT NULL
original_title	VARCHAR(255)
release_date	VARCHAR(10)
country	VARCHAR(50)
duration	INTEGER
synopsis	TEXT
file_path	VARCHAR(255) UNIQUE NOT NULL
poster_path	VARCHAR(255)
colored	BOOLEAN
format	VARCHAR(10)
suffix	VARCHAR(10)
rank	INTEGER
imported	BOOLEAN

movies_people

Column Name	Type	Foreign key
id	INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE
name	VARCHAR(200) NOT NULL
birthday	VARCHAR(10)
biography	TEXT

tags

Column Name	Type	Foreign key
id	INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE
id_movie	INTEGER NOT NULL
id_people	INTEGER NOT NULL
type	INTEGER NOT NULL
UNIQUE (id_people, id_movie, type) ON CONFLICT IGNORE
FOREIGN KEY(id_movie) REFERENCES movies ON DELETE CASCADE
FOREIGN KEY(id_people) REFERENCES people ON DELETE CASCADE

movies_tags

Column Name	Type	Foreign key
id	INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE
name	VARCHAR(255) UNIQUE NOT NULL

playlists

Column Name	Type	Foreign key
id	INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE
id_movie	INTEGER NOT NULL
id_tag	INTEGER NOT NULL
UNIQUE (id_tag, id_movie) ON CONFLICT IGNORE
FOREIGN KEY(id_movie) REFERENCES movies ON DELETE CASCADE
FOREIGN KEY(id_tag) REFERENCES tags ON DELETE CASCADE

movies_playlists

Column Name	Type	Foreign key
id	INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE
name	VARCHAR(255) UNIQUE NOT NULL
rate	INTEGER
creation_date	INT

paths_list

Column Name	Type	Foreign key
id	INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE
id_movie	INTEGER NOT NULL
id_playlist	INTEGER NOT NULL
UNIQUE (id_playlist, id_movie) ON CONFLICT IGNORE
FOREIGN KEY(id_movie) REFERENCES movies ON DELETE CASCADE
FOREIGN KEY(id_playlist) REFERENCES playlists ON DELETE CASCADE"

config

Column Name	Type	Foreign key
id	INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE
movies_path	VARCHAR(255) UNIQUE
imported	BOOLEAN DEFAULT 0

Column Name	Type	Foreign key
db_version	INTERGER