[Dormilich]

Hi,

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

[Atli]

Hey.

Perhaps if you just removed the genre from the movies table altogether and created a N:M link table.

CREATE TABLE IF NOT EXISTS movie_genres 
(
	movie_id       Integer  REFERENCES movies(id),
	genre_id       Integer  REFERENCES genres(id),
	Primary Key (movie_id, genre_id)
);

[Dormilich]

hm, that looks like it should work. thanks Atli.

are you sure the columns should be primary keys?

PS. do you happen to know about table inheritance?

This post has been edited by Dormilich: 22 June 2011 - 01:30 AM

[Atli]

Yes, if you make them a join primary key the database will restrict you from inserting duplicate combinations. You'll never have to worry about you code accidentally putting in the same genre twice for one movie.

If you prefer to have a SERIAL id column as the PK, you could do that and use a Unique constraint on the movie and genre ids. Can be useful if you need to refer to the rows in the link table elsewhere. Primary and Unique keys are basically the same thing after all.


And no, sorry. Haven't had a chance to look at inheritance. My experience with PostgreSQL is limited to making clones of MySQL databases, which has nothing comparable. - You've got me kind of curious now though...

[Dormilich]

Atli, on 22 June 2011 - 10:45 AM, said:

Yes, if you make them a join primary key the database will restrict you from inserting duplicate combinations. You'll never have to worry about you code accidentally putting in the same genre twice for one movie.

simplified one could say that f(col1, col2) is a primary key …

Atli, on 22 June 2011 - 10:45 AM, said:

And no, sorry. Haven't had a chance to look at inheritance. My experience with PostgreSQL is limited to making clones of MySQL databases, which has nothing comparable. - You've got me kind of curious now though...

I also have a table for series … it would be a waste to duplicate the basic code.

[Atli]

Dormilich, on 22 June 2011 - 08:50 AM, said:

simplified one could say that f(col1, col2) is a primary key …

Yes, that's a good way to look at it. I'm not sure what PostgreSQL does exactly, but MySQL creates a unique index that looks like: col1val-col2val-colNval.

Atli, on 22 June 2011 - 10:45 AM, said:

I also have a table for series … it would be a waste to duplicate the basic code.

Anything that reduces the need to retype the same column definitions over and over sounds great.

Looking through the docs though, it seems relationships and constraints aren't inherited, only the column definitions, which kind of limits the usefulness of this feature quite a bit. It's probably simpler creating a master table with nullable fields for the would-be child table columns.

I wonder if this sort of thing could pay off.

CREATE TABLE named_abstract (
	id         SERIAL PRIMARY KEY,
	name       VARCHAR NOT NULL UNIQUE,
	created	   TIMESTAMP with time zone DEFAULT CURRENT_TIMESTAMP(0)
);

CREATE TABLE genres (
	description    TEXT
) INHERITS (named_abstract);

CREATE TABLE movies (
	type       INTEGER   REFERENCES filetype (id),
	folder     varchar(50),
	size       REAL
) INHERITS (named_abstract);

I guess the abstract tables would have to be pretty generic to be worth the trouble.

[Dormilich]

Atli, on 22 June 2011 - 10:24 AM, said:

Perhaps if you just removed the genre from the movies table altogether and created a N:M link table.

How do I get that to output the genres in a single value (e.g. comma-separated)?

if I do something like

SELECT 
    movies.name,
    genres.name
FROM
    movie_genres
INNER JOIN
    movies ON movies.id = movie_genres.movie_id
INNER JOIN
    genres ON genres.id = movie_genres.genre_id;

this would give me an entry with each movie-genre pair, which is certainly not optimal for output/display …

This post has been edited by Dormilich: 23 June 2011 - 05:24 AM

[Atli]

You could emulate MySQL's GROUP_CONCAT using arrays. Try something like:

SELECT 
	m.id AS move_id,
	m.name AS movie_title,
	array_to_string(ARRAY(
		SELECT g.name
		FROM genres AS g
		INNER JOIN movie_genres AS mg
			ON  mg.genre_id = g.id
			AND mg.movie_id = m.id
	), ', ') AS genres
FROM movies AS m

[baavgai]

I'd avoid doing a OO style abstraction thing with databases. I once heard someone say something to the effect of "never try to optimize database declarations as you would code, repetition of structural definitions is fine, it's the data that matters in a database." It's honestly the best advice I've ever gotten. Things like abstraction and object are programmer toys; they work for crap in databases. Always. Trust me, I've tried and been burnt enough.

To comma separated values in a database; you don't. Databases store data, they retrieve data, and they do this well. Trying to format in a complex fashion is not their strong suit. You'd ultimately end up writing some complex function with whatever procedural language of your database offers. Or, you could just do it in code and save yourself some hair.

For all genres for a given move, it would look like this:

SELECT b.name
	FROM movie_genres a
		INNER JOIN genres b
			ON a.genre_id = b.genre_id
	WHERE a.movie_id=?

So, you get a list of strings and do what you like with them in code.