CREATE TABLE artist (
  id INTEGER PRIMARY KEY, 

  --Name is a number referencing the id in the artist_name table, where the real string is. 
  --Beware: name here references id there, not name. Name there is the real string.  

  name INTEGER REFERENCES artist_name(id), 

  --For a person: Begin date represents date of birth, and end date represents date of death.
  --For a group: Begin date represents the date when the group first formed. 
  --If a group dissolved and then reunited, the date is still that of when they first formed. 
  --End date represents the date when the group last dissolved: if a group dissolved and then reunited, 
  --the date is that of when they last dissolved (if they are together, it should be blank).

  begin_date_year INTEGER, 
  begin_date_month INTEGER, 
  begin_date_day INTEGER, 

  end_date_year INTEGER, 
  end_date_month INTEGER, 
  end_date_day INTEGER, 

  --Type, country, gender here reference id's of artist_type, country, and gender tables, respectively.  
  --There are similar references in other tables that will not be explained further.

  type INTEGER REFERENCES artist_type(id), 
  country INTEGER REFERENCES country(id), 
  gender INTEGER REFERENCES gender(id));

--This index is created to speed up joins with the artist_name table.
CREATE INDEX anamecode ON artist (name);


CREATE TABLE artist_name (
    id INTEGER PRIMARY KEY,

    --name is the real string of the artist name. TEXT is like VARCHAR.
    name TEXT
);

CREATE INDEX aname ON artist_name (name);


CREATE TABLE artist_type (
    id INTEGER PRIMARY KEY,
    name TEXT
);


CREATE TABLE country (
    id INTEGER PRIMARY KEY,
    iso_code TEXT,
    name TEXT
);


CREATE TABLE gender (
    id INTEGER PRIMARY KEY,
    name TEXT
);


CREATE TABLE language
(
    id INTEGER PRIMARY KEY,
    iso_code_2t TEXT,
    iso_code_2b TEXT,
    iso_code_1 TEXT,
    name TEXT,
    frequency INTEGER,
    iso_code_3 TEXT
);


--Each release can have one or more mediums (e.g. CD 1, CD 2, etc).

CREATE TABLE medium (
  id INTEGER PRIMARY KEY, 
  tracklist INTEGER REFERENCES tracklist(id), 
  release INTEGER REFERENCES release(id), 
  position INTEGER, 
  format INTEGER REFERENCES medium_format(id), 
  name TEXT
);

--There are 34 different formats, such as CD, DVD, Vinyl, etc.

CREATE TABLE medium_format (
  id INTEGER PRIMARY KEY, 
  name TEXT, 
  year INTEGER, 
  has_discids BOOLEAN
);

--Release is one of the main tables with several attributes with straightforward meaning. 
--A release represents the unique release of a product on a specific date with specific release information such as the country, date, language, etc. 
--If you walk into a store and purchase an album or single, they're each represented as one release.
--Each release belongs to a release group and contains at least one medium (commonly referred to as a disc when talking about a CD release). 
--Each medium has a tracklist. A medium is the actual physical medium the audio content is stored upon. 
--This means that each CD in a multi-disc release will be entered as a separate medium within the release, 
--and that both sides of a vinyl record or cassette will exist on one medium. 
--Mediums have a format (e.g. CD, DVD, vinyl, cassette) and can optionally also have a title (name).
--Tracklists represent the set and ordering of tracks as listed on a liner, and the same tracklist can appear on more than one release. 
--For example, a boxset compilation that contains previously released CDs would share the same tracklists as the separate releases.

CREATE TABLE release (
  id INTEGER PRIMARY KEY, 
  name INTEGER REFERENCES release_name(id), 
  release_group INTEGER REFERENCES release_group(id), 
  status INTEGER REFERENCES release_status(id), 
  country INTEGER REFERENCES country(id), 
  language INTEGER REFERENCES language(id), 
  date_year INTEGER, 
  date_month INTEGER, 
  date_day INTEGER
);

--A release group, is used to group several different releases into a single logical entity. Every release belongs to one, and only one release group.
--Both release groups and releases are "albums" in a general sense, but with an important difference: 
--a release is something you can buy as media such as a CD or a vinyl record, while a release group embraces the overall concept of an album -- 
--it doesn't matter how many CDs or editions/versions it had.
--When an artist says "We've released our new album", they're talking about a release group. 
--When their publisher says "This new album gets released next week in Japan and next month in Europe", 
--they're referring to the different releases that belong in the above mentioned release group.
--MusicBrainz automatically considers every release in the database to be part of a release group, even if this group only contains the one release. 
--When a brand new release is added to the database, a new release group is automatically added as well.

CREATE TABLE release_group (
  id INTEGER PRIMARY KEY, 
  name INTEGER REFERENCES release_name(id), 
  type INTEGER REFERENCES release_group_primary_type(id)
);


CREATE TABLE release_group_primary_type (
    id INTEGER PRIMARY KEY,
    name TEXT
);


CREATE TABLE release_name (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE INDEX rname ON release_name (name);


CREATE TABLE release_status
(
    id INTEGER PRIMARY KEY,
    name TEXT
);


--The following tables relate pairs the main tables as their name suggests. 
--They have been populated by appropriate joins on the original Musicbranz tables. 

CREATE TABLE r_artist_release (
  entity0 INTEGER REFERENCES artist(id),   
  entity1 INTEGER REFERENCES release(id),   
  position INTEGER
);


CREATE INDEX ar_entity0 ON r_artist_release (entity0);

CREATE INDEX ar_entity1 ON r_artist_release (entity1);


CREATE TABLE r_artist_release_group (
  entity0 INTEGER REFERENCES artist(id),   
  entity1 INTEGER REFERENCES release_group(id),   
  position INTEGER
);


CREATE INDEX arg_entity0 ON r_artist_release_group (entity0);

CREATE INDEX arg_entity1 ON r_artist_release_group (entity1);


CREATE TABLE r_artist_track (
  entity0 INTEGER REFERENCES artist(id),   
  entity1 INTEGER REFERENCES track(id)
);


CREATE INDEX at_entity0 ON r_artist_track (entity0);

CREATE INDEX at_entity1 ON r_artist_track (entity1);


CREATE TABLE r_track_release (
  entity0 INTEGER REFERENCES track(id),   
  entity1 INTEGER REFERENCES release(id)
);


CREATE INDEX tr_entity0 ON r_track_release (entity0);

CREATE INDEX tr_entity1 ON r_track_release (entity1);


CREATE TABLE track (
  id INTEGER PRIMARY KEY, 
  tracklist INTEGER REFERENCES tracklist(id), 
  position INTEGER, 
  name INTEGER REFERENCES track_name(id), 
  length INTEGER
);


CREATE TABLE tracklist (
  id INTEGER PRIMARY KEY, 
  track_count INTEGER
);


CREATE TABLE track_name (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE INDEX tname ON track_name (name);