Postgres schema for discogs data

setup.sql

-- Clear out the database
DROP TABLE IF EXISTS artist_master;
DROP TABLE IF EXISTS artist_release;
DROP TABLE IF EXISTS label_release;
DROP TABLE IF EXISTS extraartist_release;
DROP TABLE IF EXISTS labels;
DROP TABLE IF EXISTS artists;
DROP TABLE IF EXISTS releases;
DROP TABLE IF EXISTS masters;

-- Tables
CREATE TABLE labels(
    id INT,
    name TEXT,
    data_quality CHAR(20),
    urls TEXT[],
    parent_label TEXT,
    PRIMARY KEY(id)
);
CREATE INDEX labels_name_idx ON labels (name);
CREATE INDEX labels_parentlabel_idx ON labels (parent_label);

CREATE TABLE artists(
    id INT,
    name TEXT,
    data_quality CHAR(20),
    realname TEXT,
    profile TEXT,
    namevariations TEXT[],
    aliases TEXT[],
    urls TEXT[],
    PRIMARY KEY(id)
);
CREATE INDEX artists_name_idx ON artists (name);
CREATE INDEX artist_namevariations_idx on "artists" USING GIN ("namevariations");
CREATE INDEX artist_aliases_idx on "artists" USING GIN ("aliases");

CREATE TABLE releases(
    id INT,
    name TEXT,
    date CHAR(20),
    country TEXT,
    genres TEXT[],
    styles TEXT[],
    master_id INT,
    has_master INT,
    PRIMARY KEY(id)
);
CREATE INDEX release_genre_idx on "releases" USING GIN ("genres");
CREATE INDEX release_styles_idx on "releases" USING GIN ("styles");
CREATE INDEX release_master_id_idx on "releases" ("master_id");

CREATE TABLE masters(
    id INT,
    name TEXT,
    year INT,
    main_release INT,
    genres TEXT[],
    styles TEXT[],
    PRIMARY KEY(id)
);

CREATE INDEX masters_genre_idx on "masters" USING GIN ("genres");
CREATE INDEX masters_styles_idx on "masters" USING GIN ("styles");
CREATE INDEX masters_main_release_idx on "masters" ("main_release");

CREATE TABLE artist_master(
    artist_id INT,
    master_id INT
);
CREATE INDEX am_artist_id_idx ON artist_master (artist_id);
CREATE INDEX am_master_id_idx ON artist_master (master_id);

CREATE TABLE artist_release(
    artist_id INT,
    release_id INT
);
CREATE INDEX ar_artist_id_idx ON artist_release (artist_id);
CREATE INDEX ar_release_id_idx ON artist_release (release_id);

CREATE TABLE extraartist_release(
    artist_id INT,
    release_id INT,
    role TEXT
);
CREATE INDEX ear_artist_id_idx ON extraartist_release (artist_id);
CREATE INDEX ear_release_id_idx ON extraartist_release (release_id);
CREATE INDEX ear_role_idx ON extraartist_release (role);


----

CREATE TABLE wordsets (
    key            text,
    items        text[]
);

CREATE TABLE artist_primary_category (
    artist_id integer NOT NULL,
    category TEXT
);