9

I have a table representing movies. The fields are:
id (PK), title, genre, runtime, released_in, tags, origin, downloads.

My database cannot be polluted by duplicated rows, so I want to enforce uniqueness. The problem is that different movies could have the same title, or even the same fields except tags and downloads. How to enforce uniqueness?

I thought of two ways:

  • make all the fields except downloads primary key. I'm keeping downloads out since it's JSON and it will probably impact the performance.
  • keep only id as primary key, but add a unique constraint with all the other columns (except, again, downloads).

I read this question which is very similar, but I didn't quite understand what should I do. Currently this table is not related to any other tables, but in the future could be.

At the moment I have slightly less than 20,000 records, but I expect the number to grow. I don't know if this is somewhat relevant to the issue.

EDIT: I modified the schema and here is how I would create the table:

CREATE TABLE movies (
    id          serial PRIMARY KEY,
    title       text NOT NULL,
    runtime     smallint NOT NULL CHECK (runtime >= 0),
    released_in smallint NOT NULL CHECK (released_in > 0),
    genres      text[] NOT NULL default ARRAY[]::text[],
    tags        text[] NOT NULL default ARRAY[]::text[],
    origin      text[] NOT NULL default ARRAY[]::text[],
    downloads   json NOT NULL,
    inserted_at timestamp NOT NULL default current_timestamp,
    CONSTRAINT must_be_unique UNIQUE(title,runtime,released_in,genres,tags,origin)
);

I also added the timestamp column, but that is not a problem as I won't touch it. So it will always be automatic and unique.

4

Your table definition looks reasonable all over now. With all columns NOT NULL the UNIQUE constraint will work as expected - except for typos and minor differences in spelling, which may be rather common I am afraid. Consider @a_horse's comment.

Alternative with functional unique index

The other option would be a functional unique index (similar to what @Dave commented). But I would use a uuid data type to optimize index size and performance.

The cast from array to text is not IMMUTABLE (due to its generic implementation):

Hence you need a little helper function to declare it immutable:

CREATE OR REPLACE FUNCTION f_movie_uuid(_title text
                                      , _runtime int2
                                      , _released_in int2
                                      , _genres text[]
                                      , _tags text[]
                                      , _origin text[])
  RETURNS uuid LANGUAGE sql IMMUTABLE AS  -- faking IMMUTABLE
'SELECT md5(_title || _runtime::text || _released_in::text
         || _genres::text || _tags::text || _origin::text)::uuid';

Use it for the index definition:

CREATE UNIQUE INDEX movies_uni_idx
ON movies (f_movie_uuid(title,runtime,released_in,genres,tags,origin));

SQL Fiddle.

More Details:

You might use the generated UUID as PK, but I would still use the serial column with its 4 bytes, which is simple and cheap for FK references and other purposes. A UUID would be a great option for distributed systems that need to generate PK values independently. Or for very huge tables, but there aren't nearly enough movies in our solar system for that.

Pros and Cons

A unique constraint is implemented with a unique index on the involved columns. Put relevant columns in the constraint definition first and you have a useful index for other purposes as collateral benefit.

There are other specific benefits, here is a list:

The functional unique index is (potentially much) smaller in size, which can make it substantially faster. If your columns are not too big, the difference won't be much. There is also the small overhead cost for the calculation.

Concatenating all columns can introduce false positives ('foo ' || 'bar' = 'foob ' || 'ar', but that seems very unlikely for this case. Typos are so much more likely that you can safely ignore it here.

Uniqueness and arrays

Arrays would have to be sorted consistently to make sense in any unique arrangement relying on the = operator because '{1,2}' <> '{2,1}'. I suggest look-up tables for genre, tag and origin with serial PK and unique entries, which allow fuzzy search for array elements. Then:

Either way, working with arrays directly or with a normalized schema and a materialized view, searching can be very efficient with the right index and operators:

Aside

If you are using Postgres 9.4 or later consider jsonb instead of json.

|improve this answer|||||
6

Imagine you are out with a group of friends and the conversation turns to movies. Someone asks, "What did you think of 'The Three Musketeers'?" You respond, "Which one?"

What additional information would you need to be absolutely certain you are both thinking of the same movie? The director's name? The production studio? The year it was released? One of the star's names? Some combination of two or more?

The answer to my question and yours are the same.

However, I would not think that genre would be a good candidate. One reason, genre is much too subjective a criteria. Is 'The Three Musketeers' action? drama? adventure? comedy? action-adventure? romantic-comedy? I often see the same movie listed under different genres. Even when you allow for multiple genres, your user may select an entirely different one not listed with the actual movie they are looking for.

Even runtimes can differ, especially between theater and VCR/DVD/b-ray versions.

So you need hard, objective attributes which will not change from one media release to another. Unfortunately, that can exclude the name of the movie as movies have been known to be renamed, especially after the release of a sequel.

What about release date? The theatrical release of 1993? The VCR release of 1999? The DVD release of 2004? You get the idea.

Come to think about it, what of all those movies directed by Alan Smithee? Has the real director ever finally stepped forward to put his name on the project after the fact? I don't know.

Hmm, I'd better stop while there are still some criteria left.

Some additional points:

  • Yes, keep the surrogate key and create a unique index on the natural key fields (if you can finally nail those down). The surrogate key is best for foreign key references. You don't want to duplicate all the natural key fields in every table that contains a reference to a movie.
  • Drop the array fields (genres, tags, origins). Go ahead and properly normalize those attributes. I have never seen an array field that was not a great deal more trouble than it was worth, especially if you want them to be searchable ("...where genre = 'horror'..."). Note this will not automatically eliminate any issues with case differences and spelling ( "Science Fiction" vs "SciFi" ) -- unless you properly maintain the lookup tables. But it is a whole lot easier to check for such differences in one field of a small table than every array cell of every row of a large table.
|improve this answer|||||
4

The ID column has no advantage at all when it comes to the uniqueness you want/need to enforce. Uniqueness of whatever combination of attributes is never going to be enforced by adding a meaningless ID. Its "advantage" only shows when you ever get to the point where you'd need a new table that needs a foreign key to this one. In that case, and IF you have included the Id, then you can use that one as the FK in your new table. (But don't think it will be a free lunch. The downside of such an approach is you'll likely find yourself writing more joins for the mere purpose of fetching information that could perfectly well have been part of that new table you made.)

|improve this answer|||||
  • 1
    If the business rules say that the combination of values in attributes FOO and BAR must be unique, then adding an ID is not going to achieve that. Adding the ID just facilitates avoiding having to include FOO and BAR as such in referencing tables. Which in turn necessitates more joins because the FOO and BAR attributes (which carry BUSINESS identifiers) are not where they could have been (and where they are very likely EXPECTED to be, at least from a business point of view). – Erwin Smout Jul 28 '15 at 6:19
  • 1
    It is NOT the "rows" that must be unique, it is what the business says are their identifiers that must be. If that is a combination of attributes FOO and BAR, then it is the combination of attributes FOO and BAR. – Erwin Smout Jul 28 '15 at 6:22
  • 2
    Having the Id or not does not solve any problem of enforcement of uniqueness of the "business" columns in your table. Enforcement of uniqueness must be done by declaring the appropriate keys (which you do - the fact that you used the syntactic word "CONSTRAINT" instead of "KEY" does not mean it isn't a key). – Erwin Smout Jul 28 '15 at 10:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.