CREATE TABLE IF NOT EXISTS genre
(
id INTEGER PRIMARY KEY,
genre TEXT NOT NULL, -- e.g. High fantasy
enum TEXT NOT NULL, -- e.g. HIGH_FANTASY
UNIQUE(enum)
);
CREATE TABLE IF NOT EXISTS title_genre
(
id INTEGER PRIMARY KEY,
title_id INTEGER REFERENCES title(id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
genre_id INTEGER REFERENCES genre(id) ON UPDATE CASCADE ON DELETE CASCADE NOT NULL,
UNIQUE(title_id, genre_id)
);