Iím currently creating a movie database (early stage, still) at now Iím at the point of designing the DB (using PostgreSQL). My problem now is, that I somehow want to make sure that movies.genre is in accordance with the genres table. For a single value a FK would be the way to go, but movies do not necessarily belong to one genre exclusively. I could imagine to use an array, but that would prevent any simple consistency check. Another way could be using bit-wise addition, though that would limit the number of genres.
Has anyone an idea how to efficiently make that relation (I donít mind if there is no consistency check, if thatís not feasible)?
This is what I (using the pgsql-docs) have come up with so far:
CREATE TABLE IF NOT EXISTS genres ( id SERIAL PRIMARY KEY, name varchar(50), description TEXT );
CREATE TABLE IF NOT EXISTS movies ( id SERIAL PRIMARY KEY, type INTEGER REFERENCES filetype (id), -- file type genre INTEGER, -- !!! PROBLEM IS THIS DEFINITION !!! name varchar(100), -- file name folder varchar(50), -- name of the containing directory size REAL -- size in MB created TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP(0) );
This post has been edited by Dormilich: 22 June 2011 - 01:09 AM