What's the best way to store an enum type in postgres being used in a join table?-CodePudding

I have have the following three tables:

Movie Types

CREATE TYPE category AS ENUM ('comedy', 'drama', 'action', 'thriller');

CREATE TABLE movie_types (
    id BIGSERIAL PRIMARY KEY,
    category category NOT NULL
);

Column |  Type  |                     Modifiers
-------- -------- ----------------------------------------------------
id     | bigint | not null
category | category | not null

Movie to Movie types joins table

CREATE TABLE movie_categories (
    id BIGSERIAL PRIMARY KEY,
    movie_type_id FOREIGN KEY REFERENCES movie_type(id)
    movie_id FOREIGN KEY REFERENCES movie(id)
);

Column |  Type  |                     Modifiers
-------- -------- ----------------------------------------------------
id     | bigint | not null
movie_type_id | category | not null
movie_id | category | not null

Movies


CREATE TABLE movies (
    id BIGSERIAL PRIMARY KEY,
    name varchar
);

Column |  Type  |                     Modifiers
-------- -------- ----------------------------------------------------
id     | bigint | not null
name | string | not null

The Movie types is limited list of categories stored as an enum. A movie can have several different categories associated with it.

What's the best practice when storing something in a similar data model? I using the enum type here good practice or is it better to just use varchar for category in movie_types?

CodePudding user response：

An ENUM sets up a predetermined set of values that a column can take. A look up table provides the same restriction on a column. The problem you face is you are attempting to implement both. A solution is to choose and implement one of them. I tend to lean toward minimizing maintenance where possible, so the following implements a look table approach.

First step: Drop the ENUM.
Second alter category table category to text.
Insert the prior enum values into category table.
Adjust other tables as needed.

The result becomes a simple M:M with Movie:Movie_Types with the intersection table movie_categories.

create table movie_types (
             id       bigint generated always as identity primary key 
           , category text not null unique
           );
          
insert into movie_types(category) 
     values ('comedy'), ('drama'), ('action'), ('thriller');

    
create table movies (
             id   bigint generated always as identity primary key 
           , name varchar
           );
 
create table movie_categories (
             movie_type_id bigint references movie_types(id)
           , movie_id      bigint references movies(id)
           , constraint movie_categories_pk 
                        primary key (movie_id,movie_type_id)
           );