Latest updated schema

Announcements about the database, website, and plugins.
szsori
Site Admin
Posts: 1911
Joined: Fri Nov 03, 2006 5:23 pm

Latest updated schema

Postby szsori » Sat May 09, 2009 10:07 am

For anyone interested:

Code: Select all


-- ---------------------------------------------------------
-- Create the database
-- ---------------------------------------------------------
CREATE DATABASE tvdb WITH OWNER = tvdb ENCODING = 'UNICODE';


-- ---------------------------------------------------------
-- API keys
-- ---------------------------------------------------------
CREATE TABLE apikeys
(
  apikey_pk serial,
  apikey_user_fk integer,
  apikey_key character varying(16),
  apikey_project character varying(100),
  apikey_modifiedby_fk integer,
  apikey_modified timestamp with time zone,
  CONSTRAINT apikeys_primarykey PRIMARY KEY (apikey_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX apikey_user_idx
  ON apikeys
  USING btree
  (apikey_user_fk);

CREATE INDEX apikey_key_idx
  ON apikeys
  USING btree
  (apikey_key);


-- ---------------------------------------------------------
-- Banners
-- ---------------------------------------------------------
CREATE TABLE banners
(
  banner_pk serial,
  banner_network_fk integer,
  banner_series_fk integer,
  banner_season_fk integer,
  banner_episode_fk integer,
  banner_people_fk integer,
  banner_language_fk integer,
  banner_filename character varying(100),
  banner_width integer,
  banner_height integer,
  banner_comment character varying(100),
  banner_gen_favorited integer DEFAULT 0,
  banner_modifiedby_fk integer,
  banner_modified timestamp with time zone,
  CONSTRAINT banners_primarykey PRIMARY KEY (banner_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX banner_network_idx
  ON banners
  USING btree
  (banner_network_fk);

CREATE INDEX banner_networklanguage_idx
  ON banners
  USING btree
  (banner_network_fk, banner_language_fk);

CREATE INDEX banner_series_idx
  ON banners
  USING btree
  (banner_series_fk);

CREATE INDEX banner_serieslanguage_idx
  ON banners
  USING btree
  (banner_series_fk, banner_language_fk);

CREATE INDEX banner_season_idx
  ON banners
  USING btree
  (banner_season_fk);

CREATE INDEX banner_seasonlanguage_idx
  ON banners
  USING btree
  (banner_season_fk, banner_language_fk);

CREATE INDEX banner_episode_idx
  ON banners
  USING btree
  (banner_episode_fk);

CREATE INDEX banner_episodelanguage_idx
  ON banners
  USING btree
  (banner_episode_fk, banner_language_fk);

CREATE INDEX banner_people_idx
  ON banners
  USING btree
  (banner_people_fk);

CREATE INDEX banner_seriespeople_idx
  ON banners
  USING btree
  (banner_series_fk, banner_people_fk);


-- ---------------------------------------------------------
-- Comments
-- ---------------------------------------------------------
CREATE TABLE comments
(
  comment_pk serial,
  comment_user_fk integer,
  comment_series_fk integer,
  comment_episode_fk integer,
  comment_text character varying(500),
  comment_modifiedby_fk integer,
  comment_modified timestamp with time zone,
  CONSTRAINT comments_primarykey PRIMARY KEY (comment_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX comment_user_idx
  ON comments
  USING btree
  (comment_user_fk);

CREATE INDEX comment_series_idx
  ON comments
  USING btree
  (comment_series_fk);

CREATE INDEX comment_episode_idx
  ON comments
  USING btree
  (comment_episode_fk);


-- ---------------------------------------------------------
-- Episode People
-- ---------------------------------------------------------
CREATE TABLE episodepeople
(
  episodepeople_pk serial,
  episodepeople_episode_fk integer,
  episodepeople_peopletype_fk integer,
  episodepeople_people_fk integer,
  episodepeople_role character varying(100),
  episodepeople_modifiedby_fk integer,
  episodepeople_modified timestamp with time zone,
  CONSTRAINT episodepeople_primarykey PRIMARY KEY (episodepeople_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX episodepeople_episode_idx
  ON episodepeople
  USING btree
  (episodepeople_episode_fk);

CREATE INDEX episodepeople_people_idx
  ON episodepeople
  USING btree
  (episodepeople_people_fk);


-- ---------------------------------------------------------
-- Episodes
-- ---------------------------------------------------------
CREATE TABLE episodes
(
  episode_pk serial,
  episode_series_fk integer,
  episode_airdate date,
  episode_runtime_fk integer,
  episode_productioncode character varying(25),
  episode_gen_averagerating double precision DEFAULT 0,
  episode_gen_totalratings integer DEFAULT 0,
  episode_gen_comments integer DEFAULT 0,
  episode_modifiedby_fk integer,
  episode_modified timestamp with time zone,
  CONSTRAINT episodes_primarykey PRIMARY KEY (episode_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX episode_series_idx
  ON episodes
  USING btree
  (episode_series_fk);


-- ---------------------------------------------------------
-- Genres
-- ---------------------------------------------------------
CREATE TABLE genres
(
  genre_pk serial,
  genre_name character varying(50),
  genre_active boolean DEFAULT true,
  CONSTRAINT genres_primarykey PRIMARY KEY (genre_pk)
)
WITH (OIDS=FALSE);


-- ---------------------------------------------------------
-- Languages
-- ---------------------------------------------------------
CREATE TABLE languages
(
  language_pk serial,
  language_name character varying(50),
  language_englishname character varying(50),
  language_active boolean DEFAULT true,
  CONSTRAINT languages_primarykey PRIMARY KEY (language_pk)
)
WITH (OIDS=FALSE);


-- ---------------------------------------------------------
-- Networks
-- ---------------------------------------------------------
CREATE TABLE networks
(
  network_pk serial,
  network_name character varying(100) NOT NULL,
  network_abbreviation character varying(25) NOT NULL,
  network_country_fk integer,
  network_active boolean DEFAULT true,
  CONSTRAINT networks_primarykey PRIMARY KEY (network_pk)
)
WITH (OIDS=FALSE);


-- ---------------------------------------------------------
-- People
-- ---------------------------------------------------------
CREATE TABLE people
(
  people_pk serial,
  people_firstname character varying(50),
  people_lastname character varying(50),
  people_birthdate date,
  people_bio character varying(500),
  people_modifiedby_fk integer,
  people_modified timestamp with time zone,
  CONSTRAINT people_primarykey PRIMARY KEY (people_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX people_name_idx
  ON people
  USING btree
  (people_lastname, people_firstname);


-- ---------------------------------------------------------
-- People Types (Actor, Director, Writer, etc)
-- ---------------------------------------------------------
CREATE TABLE peopletypes
(
  peopletype_pk serial,
  peopletype_name character varying(25),
  peopletype_active boolean DEFAULT true,
  CONSTRAINT peopletypes_primarykey PRIMARY KEY (peopletype_pk)
)
WITH (OIDS=FALSE);


-- ---------------------------------------------------------
-- Ratings
-- ---------------------------------------------------------
CREATE TABLE ratings
(
  rating_pk serial,
  rating_user_fk integer NOT NULL,
  rating_series_fk integer,
  rating_episode_fk integer,
  rating_score smallint NOT NULL,
  rating_modifiedby_fk integer,
  rating_modified timestamp with time zone,
  CONSTRAINT ratings_primarykey PRIMARY KEY (rating_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX rating_userseries_idx
  ON ratings
  USING btree
  (rating_user_fk, rating_series_fk);

CREATE INDEX rating_userepisode_idx
  ON ratings
  USING btree
  (rating_user_fk, rating_episode_fk);

CREATE INDEX rating_series_idx
  ON ratings
  USING btree
  (rating_series_fk);

CREATE INDEX rating_episode_idx
  ON ratings
  USING btree
  (rating_episode_fk);


-- ---------------------------------------------------------
-- Remote ID's
-- ---------------------------------------------------------
CREATE TABLE remoteids
(
  remoteid_pk serial,
  remoteid_series_fk integer,
  remoteid_episode_fk integer,
  remoteid_sourcetype_fk integer,
  remoteid_id character varying(50),
  remoteid_modifiedby_fk integer,
  remoteid_modified timestamp with time zone,
  CONSTRAINT remoteids_primarykey PRIMARY KEY (remoteid_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX remoteid_series_idx
  ON remoteids
  USING btree
  (remoteid_series_fk);

CREATE INDEX remoteid_episode_idx
  ON remoteids
  USING btree
  (remoteid_episode_fk);


-- ---------------------------------------------------------
-- Remote Sites
-- ---------------------------------------------------------
CREATE TABLE remotesites
(
  remotesite_pk serial,
  remotesite_series_fk integer,
  remotesite_episode_fk integer,
  remotesite_sourcetype_fk integer,
  remotesite_url character varying(250),
  remotesite_modifiedby_fk integer,
  remotesite_modified timestamp with time zone,
  CONSTRAINT remotesites_primarykey PRIMARY KEY (remotesite_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX remotesite_series_idx
  ON remotesites
  USING btree
  (remotesite_series_fk);

CREATE INDEX remotesite_episode_idx
  ON remotesites
  USING btree
  (remotesite_episode_fk);


-- ---------------------------------------------------------
-- Source Types (IMDB, Official Site, TV.com, etc)
-- ---------------------------------------------------------
CREATE TABLE sourcetypes
(
  sourcetype_pk serial,
  sourcetype_name character varying(50) NOT NULL,
  sourcetype_active boolean DEFAULT true,
  CONSTRAINT sourcetypes_primarykey PRIMARY KEY (sourcetype_pk)
)
WITH (OIDS=FALSE);


-- ---------------------------------------------------------
-- Seasons
-- ---------------------------------------------------------
CREATE TABLE seasons
(
  season_pk serial,
  season_series_fk integer,
  season_seasontype_fk integer,
  season_number integer,
  season_gen_firstaired date,
  season_gen_episodecount integer DEFAULT 0,
  season_gen_averagerating double precision DEFAULT 0,
  season_gen_totalratings integer DEFAULT 0,
  season_modifiedby_fk integer,
  season_modified timestamp with time zone,
  season_lockedby_fk integer,
  season_locked timestamp with time zone,
  CONSTRAINT seasons_primarykey PRIMARY KEY (season_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX season_series_idx
  ON seasons
  USING btree
  (season_series_fk);

CREATE INDEX season_seriesseason_idx
  ON seasons
  USING btree
  (season_series_fk, season_seasontype_fk);

CREATE INDEX season_all_idx
  ON seasons
  USING btree
  (season_series_fk, season_seasontype_fk, season_number);


-- ---------------------------------------------------------
-- Season Episodes
-- ---------------------------------------------------------
CREATE TABLE seasonepisodes
(
  seasonep_pk serial,
  seasonep_series_fk integer,
  seasonep_season_fk integer,
  seasonep_episode_fk integer,
  seasonep_episodenumber integer,
  seasonep_modifiedby_fk integer,
  seasonep_modified timestamp with time zone,
  CONSTRAINT seasonepisodes_primarykey PRIMARY KEY (seasonep_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX seasonepisodes_series_idx
  ON seasonepisodes
  USING btree
  (seasonep_series_fk);

CREATE INDEX seasonepisodes_season_idx
  ON seasonepisodes
  USING btree
  (seasonep_season_fk);

CREATE INDEX seasonepisodes_episode_idx
  ON seasonepisodes
  USING btree
  (seasonep_episode_fk);

CREATE INDEX seasonepisodes_episodenumber_idx
  ON seasonepisodes
  USING btree
  (seasonep_season_fk, seasonep_episodenumber);


-- ---------------------------------------------------------
-- Season Types (Default/Air Date, Absolute, DVD, Best of DVD, etc)
-- ---------------------------------------------------------
CREATE TABLE seasontypes
(
  seasontype_pk serial,
  seasontype_name character varying(25) NOT NULL,
  seasontype_shortname character varying(10) NOT NULL,
  seasontype_active boolean DEFAULT true,
  CONSTRAINT seasontypes_primarykey PRIMARY KEY (seasontype_pk)
)
WITH (OIDS=FALSE);


-- ---------------------------------------------------------
-- Series
-- ---------------------------------------------------------
CREATE TABLE series
(
  series_pk serial,
  series_network_fk integer,
  series_genre_fk integer,
  series_runtime_fk integer,
  series_night smallint,
  series_status_fk smallint,
  series_gen_firstaired date,
  series_gen_episodecount integer DEFAULT 0,
  series_gen_seasoncount integer DEFAULT 0,
  series_gen_averagerating double precision DEFAULT 0,
  series_gen_totalratings integer DEFAULT 0,
  series_gen_favorited integer DEFAULT 0,
  series_gen_comments integer DEFAULT 0,
  series_modifiedby_fk integer,
  series_modified timestamp with time zone,
  series_lockedby_fk integer,
  series_locked timestamp with time zone,
  CONSTRAINT series_primarykey PRIMARY KEY (series_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX series_genre_idx
  ON series
  USING btree
  (series_genre_fk);

CREATE INDEX series_network_idx
  ON series
  USING btree
  (series_network_fk);


-- ---------------------------------------------------------
-- Series People
-- ---------------------------------------------------------
CREATE TABLE seriespeople
(
  seriespeople_pk serial,
  seriespeople_series_fk integer,
  seriespeople_peopletype_fk integer,
  seriespeople_people_fk integer,
  seriespeople_role character varying(100),
  seriespeople_modifiedby_fk integer,
  seriespeople_modified timestamp with time zone,
  CONSTRAINT seriespeople_primarykey PRIMARY KEY (seriespeople_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX seriespeople_series_idx
  ON seriespeople
  USING btree
  (seriespeople_series_fk);

CREATE INDEX seriespeople_people_idx
  ON seriespeople
  USING btree
  (seriespeople_people_fk);


-- ---------------------------------------------------------
-- Season People
-- ---------------------------------------------------------
CREATE TABLE seasonpeople
(
  seasonpeople_pk serial,
  seasonpeople_series_fk integer,
  seasonpeople_season_fk integer,
  seasonpeople_peopletype_fk integer,
  seasonpeople_people_fk integer,
  seasonpeople_role character varying(100),
  seasonpeople_modifiedby_fk integer,
  seasonpeople_modified timestamp with time zone,
  CONSTRAINT seasonpeople_primarykey PRIMARY KEY (seasonpeople_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX seasonpeople_series_idx
  ON seasonpeople
  USING btree
  (seasonpeople_series_fk);

CREATE INDEX seasonpeople_season_idx
  ON seasonpeople
  USING btree
  (seasonpeople_season_fk);

CREATE INDEX seasonpeople_people_idx
  ON seasonpeople
  USING btree
  (seasonpeople_people_fk);


-- ---------------------------------------------------------
-- Series Statuses
-- ---------------------------------------------------------
CREATE TABLE seriesstatus
(
  serstatus_pk serial,
  serstatus_name character varying(25) NOT NULL,
  serstatus_active boolean DEFAULT true,
  CONSTRAINT seriesstatus_primarykey PRIMARY KEY (serstatus_pk)
)
WITH (OIDS=FALSE);


-- ---------------------------------------------------------
-- Countries
-- ---------------------------------------------------------
CREATE TABLE countries
(
  country_pk serial,
  country_name character varying(50) NOT NULL,
  country_active boolean DEFAULT true,
  CONSTRAINT country_primarykey PRIMARY KEY (country_pk)
)
WITH (OIDS=FALSE);


-- ---------------------------------------------------------
-- Runtimes
-- ---------------------------------------------------------
CREATE TABLE runtimes
(
  runtime_pk serial,
  runtime_name character varying(50) NOT NULL,
  runtime_active boolean DEFAULT true,
  CONSTRAINT runtime_primarykey PRIMARY KEY (runtime_pk)
)
WITH (OIDS=FALSE);


-- ---------------------------------------------------------
-- Episode Translations
-- ---------------------------------------------------------
CREATE TABLE translatedepisodes
(
  tranepisode_pk serial,
  tranepisode_episode_fk integer,
  tranepisode_language_fk integer,
  tranepisode_name character varying(100),
  tranepisode_overview character varying(1000),
  tranepisode_modifiedby_fk integer,
  tranepisode_modified timestamp with time zone,
  CONSTRAINT translatedepisodes_primarykey PRIMARY KEY (tranepisode_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX tranepisode_lang_idx
  ON translatedepisodes
  USING btree
  (tranepisode_episode_fk, tranepisode_language_fk);


-- ---------------------------------------------------------
-- Series Translations
-- ---------------------------------------------------------
CREATE TABLE translatedseries
(
  transeries_pk serial,
  transeries_series_fk integer,
  transeries_language_fk integer,
  transeries_name character varying(100),
  transeries_tagline character varying(100),
  transeries_overview character varying(1000),
  transeries_isprimary boolean DEFAULT true,
  transeries_modifiedby_fk integer,
  transeries_modified timestamp with time zone,
  CONSTRAINT translatedseries_primarykey PRIMARY KEY (transeries_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX transeries_lang_idx
  ON translatedseries
  USING btree
  (transeries_series_fk, transeries_language_fk);


-- ---------------------------------------------------------
-- Season Translations
-- ---------------------------------------------------------
CREATE TABLE translatedseasons
(
  transeason_pk serial,
  transeason_season_fk integer,
  transeason_language_fk integer,
  transeason_name character varying(100),
  transeason_overview character varying(1000),
  transeason_modifiedby_fk integer,
  transeason_modified timestamp with time zone,
  CONSTRAINT translatedseasons_primarykey PRIMARY KEY (transeason_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX transeason_lang_idx
  ON translatedseasons
  USING btree
  (transeason_season_fk, transeason_language_fk);


-- ---------------------------------------------------------
-- User Episodes
-- ---------------------------------------------------------
CREATE TABLE userepisodes
(
  userep_pk serial,
  userep_user_fk integer,
  userep_episode_fk integer,
  userep_watched timestamp with time zone,
  userep_modifiedby_fk integer,
  userep_modified timestamp with time zone,
  CONSTRAINT userepisodes_primarykey PRIMARY KEY (userep_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX userep_userep_idx
  ON userepisodes
  USING btree
  (userep_user_fk, userep_episode_fk);

CREATE INDEX userep_episode_idx
  ON userepisodes
  USING btree
  (userep_episode_fk);


-- ---------------------------------------------------------
-- User Favorites
-- ---------------------------------------------------------
CREATE TABLE userfavorites
(
  userfav_pk serial,
  userfav_user_fk integer,
  userfav_series_fk integer,
  userfav_banner_fk integer,
  userfav_modifiedby_fk integer,
  userfav_modified timestamp with time zone,
  CONSTRAINT userfavorites_primarykey PRIMARY KEY (userfav_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX userfav_userseries_idx
  ON userfavorites
  USING btree
  (userfav_user_fk, userfav_series_fk);

CREATE INDEX userfav_userbanner_idx
  ON userfavorites
  USING btree
  (userfav_user_fk, userfav_banner_fk);

CREATE INDEX userfav_user_idx
  ON userfavorites
  USING btree
  (userfav_user_fk);

CREATE INDEX userfav_series_idx
  ON userfavorites
  USING btree
  (userfav_series_fk);

CREATE INDEX userfav_banner_idx
  ON userfavorites
  USING btree
  (userfav_banner_fk);


-- ---------------------------------------------------------
-- Users
-- ---------------------------------------------------------
CREATE TABLE users
(
  user_pk serial,
  user_login character varying(25) NOT NULL,
  user_password character varying(25) NOT NULL,
  user_email character varying(50) NOT NULL,
  user_roles integer NOT NULL DEFAULT 1,
  user_language_fk integer NOT NULL DEFAULT 0,
  user_bannerlimit smallint NOT NULL DEFAULT 1,
  user_agreements smallint NOT NULL DEFAULT 0,
  user_lastipaddress character varying(25),
  user_lastlogin timestamp with time zone,
  user_modifiedby_fk integer,
  user_modified timestamp with time zone,
  CONSTRAINT users_primarykey PRIMARY KEY (user_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX user_login_idx
  ON users
  USING btree
  (user_login);


-- ---------------------------------------------------------
-- Deletions
-- ---------------------------------------------------------
CREATE TABLE deletions
(
  deletion_pk serial,
  deletion_tablename character varying(25),
  deletion_recordpk integer,
  deletion_modifiedby_fk integer,
  deletion_modified timestamp with time zone,
  CONSTRAINT deletions_primarykey PRIMARY KEY (deletion_pk)
)
WITH (OIDS=FALSE);

CREATE INDEX deletions_lookup_idx
  ON deletions
  USING btree
  (deletion_tablename, deletion_recordpk);

CREATE INDEX deletions_modified_idx
  ON deletions
  USING btree
  (deletion_modified);


-- ---------------------------------------------------------
-- Site news
-- ---------------------------------------------------------
CREATE TABLE sitenews
(
  sitenews_pk serial NOT NULL,
  sitenews_user_fk integer,
  sitenews_status integer,
  sitenews_description text,
  sitenews_modifiedby_fk integer,
  sitenews_modified timestamp with time zone
)
WITH (OIDS=FALSE);

CREATE INDEX sitenews_modifiedby_idx
  ON sitenews
  USING btree
  (sitenews_modifiedby_fk);


-- ---------------------------------------------------------
-- Related series
-- ---------------------------------------------------------
CREATE TABLE relatedseries
(
  relatedseries_pk serial NOT NULL,
  relatedseries_thisseries_fk integer,
  relatedseries_thatseries_fk integer,
  relatedseries_modifiedby_fk integer,
  relatedseries_modified timestamp with time zone
)
WITH (OIDS=FALSE);

CREATE INDEX relatedseries_modifiedby_idx
  ON relatedseries
  USING btree
  (relatedseries_modifiedby_fk);

CREATE INDEX relatedseries_thisseries_idx
  ON relatedseries
  USING btree
  (relatedseries_thisseries_fk);


-- ---------------------------------------------------------
-- Episode music
-- ---------------------------------------------------------
CREATE TABLE episodemusic
(
  epmusic_pk serial NOT NULL,
  epmusic_episode_fk integer,
  epmusic_performer character varying(100),
  epmusic_title character varying(100),
  epmusic_starttime interval,
  epmusic_endtime interval,
  epmusic_modifiedby_fk integer,
  epmusic_modified timestamp with time zone
)
WITH (OIDS=FALSE);

CREATE INDEX epmusic_modifiedby_idx
  ON episodemusic
  USING btree
  (epmusic_modifiedby_fk);

CREATE INDEX epmusic_episode_idx
  ON episodemusic
  USING btree
  (epmusic_episode_fk);


-- ---------------------------------------------------------
-- Episode trivia
-- ---------------------------------------------------------
CREATE TABLE episodetrivia
(
  eptrivia_pk serial NOT NULL,
  eptrivia_episode_fk integer,
  eptrivia_description text,
  eptrivia_starttime interval,
  eptrivia_modifiedby_fk integer,
  eptrivia_modified timestamp with time zone
)
WITH (OIDS=FALSE);

CREATE INDEX eptrivia_modifiedby_idx
  ON episodetrivia
  USING btree
  (eptrivia_modifiedby_fk);

CREATE INDEX eptrivia_episode_idx
  ON episodetrivia
  USING btree
  (eptrivia_episode_fk);



-- ---------------------------------------------------------
-- Moderation queue
-- ---------------------------------------------------------
CREATE TABLE moderation
(
  moderation_pk serial NOT NULL,
  moderation_series_fk integer,
  moderation_season_fk integer,
  moderation_episode_fk integer,
  moderation_banner_fk integer,
  moderation_priority smallint,
  moderation_description text,
  moderation_completedby_fk integer,
  moderation_completed timestamp with time zone,
  moderation_modifiedby_fk integer,
  moderation_modified timestamp with time zone
)
WITH (OIDS=FALSE);

CREATE INDEX moderation_series_idx
  ON moderation
  USING btree
  (moderation_series_fk);

CREATE INDEX moderation_season_idx
  ON moderation
  USING btree
  (moderation_season_fk);

CREATE INDEX moderation_episode_idx
  ON moderation
  USING btree
  (moderation_episode_fk);

CREATE INDEX moderation_banner_idx
  ON moderation
  USING btree
  (moderation_banner_fk);

CREATE INDEX moderation_modifiedby_idx
  ON moderation
  USING btree
  (moderation_modifiedby_fk);


-- ---------------------------------------------------------
-- Revisions
-- ---------------------------------------------------------
CREATE TABLE revisions
(
  revision_pk serial NOT NULL,
  revision_table character varying(50),
  revision_field character varying(50),
  revision_key integer,
  revision_newvalue character varying(1000),
  revision_note character varying(500),
  revision_modifiedby_fk integer,
  revision_modified timestamp with time zone
)
WITH (OIDS=FALSE);

CREATE INDEX revision_tablefieldkey_idx
  ON revisions
  USING btree
  (revision_table, revision_field, revision_key);

CREATE INDEX revision_modifiedby_idx
  ON revisions
  USING btree
  (revision_modifiedby_fk);


-- ---------------------------------------------------------
-- Relationships
-- ---------------------------------------------------------
ALTER TABLE apikeys
  ADD CONSTRAINT fk_apikey_user FOREIGN KEY (apikey_user_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE apikeys
  ADD CONSTRAINT fk_apikey_modifiedby FOREIGN KEY (apikey_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE banners
  ADD CONSTRAINT fk_banner_network FOREIGN KEY (banner_network_fk)
      REFERENCES networks (network_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE banners
  ADD CONSTRAINT fk_banner_series FOREIGN KEY (banner_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE banners
  ADD CONSTRAINT fk_banner_season FOREIGN KEY (banner_season_fk)
      REFERENCES seasons (season_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE banners
  ADD CONSTRAINT fk_banner_episode FOREIGN KEY (banner_episode_fk)
      REFERENCES episodes (episode_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE banners
  ADD CONSTRAINT fk_banner_people FOREIGN KEY (banner_people_fk)
      REFERENCES people (people_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE banners
  ADD CONSTRAINT fk_banner_language FOREIGN KEY (banner_language_fk)
      REFERENCES languages (language_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE banners
  ADD CONSTRAINT fk_banner_modifiedby FOREIGN KEY (banner_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE comments
  ADD CONSTRAINT fk_comment_user FOREIGN KEY (comment_user_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE comments
  ADD CONSTRAINT fk_comment_series FOREIGN KEY (comment_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE comments
  ADD CONSTRAINT fk_comment_episode FOREIGN KEY (comment_episode_fk)
      REFERENCES episodes (episode_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE comments
  ADD CONSTRAINT fk_comment_modifiedby FOREIGN KEY (comment_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE episodepeople
  ADD CONSTRAINT fk_episodepeople_episode FOREIGN KEY (episodepeople_episode_fk)
      REFERENCES episodes (episode_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE episodepeople
  ADD CONSTRAINT fk_episodepeople_peopletype FOREIGN KEY (episodepeople_peopletype_fk)
      REFERENCES peopletypes (peopletype_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE episodepeople
  ADD CONSTRAINT fk_episodepeople_people FOREIGN KEY (episodepeople_people_fk)
      REFERENCES people (people_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE episodepeople
  ADD CONSTRAINT fk_episodepeople_modifiedby FOREIGN KEY (episodepeople_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE episodes
  ADD CONSTRAINT fk_episode_series FOREIGN KEY (episode_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE episodes
  ADD CONSTRAINT fk_episode_runtime FOREIGN KEY (episode_runtime_fk)
      REFERENCES runtimes (runtime_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE episodes
  ADD CONSTRAINT fk_episode_modifiedby FOREIGN KEY (episode_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE people
  ADD CONSTRAINT fk_people_modifiedby FOREIGN KEY (people_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE ratings
  ADD CONSTRAINT fk_rating_user FOREIGN KEY (rating_user_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE ratings
  ADD CONSTRAINT fk_rating_series FOREIGN KEY (rating_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE ratings
  ADD CONSTRAINT fk_rating_episode FOREIGN KEY (rating_episode_fk)
      REFERENCES episodes (episode_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE ratings
  ADD CONSTRAINT fk_rating_modifiedby FOREIGN KEY (rating_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE remoteids
  ADD CONSTRAINT fk_remoteid_series FOREIGN KEY (remoteid_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE remoteids
  ADD CONSTRAINT fk_remoteid_episode FOREIGN KEY (remoteid_episode_fk)
      REFERENCES episodes (episode_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE remoteids
  ADD CONSTRAINT fk_remoteid_sourcetype FOREIGN KEY (remoteid_sourcetype_fk)
      REFERENCES sourcetypes (sourcetype_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE remoteids
  ADD CONSTRAINT fk_remoteid_modifiedby FOREIGN KEY (remoteid_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE remotesites
  ADD CONSTRAINT fk_remotesite_series FOREIGN KEY (remotesite_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE remotesites
  ADD CONSTRAINT fk_remotesite_episode FOREIGN KEY (remotesite_episode_fk)
      REFERENCES episodes (episode_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE remotesites
  ADD CONSTRAINT fk_remotesite_sourcetype FOREIGN KEY (remotesite_sourcetype_fk)
      REFERENCES sourcetypes (sourcetype_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE remotesites
  ADD CONSTRAINT fk_remotesite_modifiedby FOREIGN KEY (remotesite_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE seasons
  ADD CONSTRAINT fk_season_series FOREIGN KEY (season_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seasons
  ADD CONSTRAINT fk_season_seasontype FOREIGN KEY (season_seasontype_fk)
      REFERENCES seasontypes (seasontype_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seasons
  ADD CONSTRAINT fk_season_modifiedby FOREIGN KEY (season_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seasons
  ADD CONSTRAINT fk_season_lockedby FOREIGN KEY (season_lockedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE seasonepisodes
  ADD CONSTRAINT fk_seasonep_series FOREIGN KEY (seasonep_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seasonepisodes
  ADD CONSTRAINT fk_seasonep_season FOREIGN KEY (seasonep_season_fk)
      REFERENCES seasons (season_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seasonepisodes
  ADD CONSTRAINT fk_seasonep_episode FOREIGN KEY (seasonep_episode_fk)
      REFERENCES episodes (episode_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seasonepisodes
  ADD CONSTRAINT fk_seasonep_modifiedby FOREIGN KEY (seasonep_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE series
  ADD CONSTRAINT fk_series_network FOREIGN KEY (series_network_fk)
      REFERENCES networks (network_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE series
  ADD CONSTRAINT fk_series_genre FOREIGN KEY (series_genre_fk)
      REFERENCES genres (genre_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE series
  ADD CONSTRAINT fk_series_runtime FOREIGN KEY (series_runtime_fk)
      REFERENCES runtimes (runtime_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE series
  ADD CONSTRAINT fk_series_status FOREIGN KEY (series_status_fk)
      REFERENCES seriesstatus (serstatus_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE series
  ADD CONSTRAINT fk_series_modifiedby FOREIGN KEY (series_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE series
  ADD CONSTRAINT fk_series_lockedby FOREIGN KEY (series_lockedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE seriespeople
  ADD CONSTRAINT fk_seriespeople_series FOREIGN KEY (seriespeople_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seriespeople
  ADD CONSTRAINT fk_seriespeople_peopletype FOREIGN KEY (seriespeople_peopletype_fk)
      REFERENCES peopletypes (peopletype_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seriespeople
  ADD CONSTRAINT fk_seriespeople_people FOREIGN KEY (seriespeople_people_fk)
      REFERENCES people (people_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seriespeople
  ADD CONSTRAINT fk_seriespeople_modifiedby FOREIGN KEY (seriespeople_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE seasonpeople
  ADD CONSTRAINT fk_seasonpeople_series FOREIGN KEY (seasonpeople_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seasonpeople
  ADD CONSTRAINT fk_seasonpeople_season FOREIGN KEY (seasonpeople_season_fk)
      REFERENCES seasons (season_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seasonpeople
  ADD CONSTRAINT fk_seasonpeople_peopletype FOREIGN KEY (seasonpeople_peopletype_fk)
      REFERENCES peopletypes (peopletype_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seasonpeople
  ADD CONSTRAINT fk_seasonpeople_people FOREIGN KEY (seasonpeople_people_fk)
      REFERENCES people (people_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE seasonpeople
  ADD CONSTRAINT fk_seasonpeople_modifiedby FOREIGN KEY (seasonpeople_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE translatedepisodes
  ADD CONSTRAINT fk_tranepisode_episode FOREIGN KEY (tranepisode_episode_fk)
      REFERENCES episodes (episode_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE translatedepisodes
  ADD CONSTRAINT fk_tranepisode_language FOREIGN KEY (tranepisode_language_fk)
      REFERENCES languages (language_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE translatedepisodes
  ADD CONSTRAINT fk_tranepisode_modifiedby FOREIGN KEY (tranepisode_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE translatedseasons
  ADD CONSTRAINT fk_transeason_season FOREIGN KEY (transeason_season_fk)
      REFERENCES seasons (season_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE translatedseasons
  ADD CONSTRAINT fk_transeason_language FOREIGN KEY (transeason_language_fk)
      REFERENCES languages (language_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE translatedseasons
  ADD CONSTRAINT fk_transeason_modifiedby FOREIGN KEY (transeason_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE translatedseries
  ADD CONSTRAINT fk_transeries_series FOREIGN KEY (transeries_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE translatedseries
  ADD CONSTRAINT fk_transeries_language FOREIGN KEY (transeries_language_fk)
      REFERENCES languages (language_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE translatedseries
  ADD CONSTRAINT fk_transeries_modifiedby FOREIGN KEY (transeries_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE userepisodes
  ADD CONSTRAINT fk_userep_user FOREIGN KEY (userep_user_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE userepisodes
  ADD CONSTRAINT fk_userep_episode FOREIGN KEY (userep_episode_fk)
      REFERENCES episodes (episode_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE userepisodes
  ADD CONSTRAINT fk_userep_modifiedby FOREIGN KEY (userep_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE userfavorites
  ADD CONSTRAINT fk_userfav_user FOREIGN KEY (userfav_user_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE userfavorites
  ADD CONSTRAINT fk_userfav_series FOREIGN KEY (userfav_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE userfavorites
  ADD CONSTRAINT fk_userfav_banner FOREIGN KEY (userfav_banner_fk)
      REFERENCES banners (banner_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE userfavorites
  ADD CONSTRAINT fk_userfav_modifiedby FOREIGN KEY (userfav_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE users
  ADD CONSTRAINT fk_user_language FOREIGN KEY (user_language_fk)
      REFERENCES languages (language_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE users
  ADD CONSTRAINT fk_user_modifiedby FOREIGN KEY (user_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE networks
  ADD CONSTRAINT fk_network_country FOREIGN KEY (network_country_fk)
      REFERENCES countries (country_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE sitenews
  ADD CONSTRAINT fk_sitenews_modifiedby FOREIGN KEY (sitenews_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE relatedseries
  ADD CONSTRAINT fk_relatedseries_thisseries FOREIGN KEY (relatedseries_thisseries_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE relatedseries
  ADD CONSTRAINT fk_relatedseries_thatseries FOREIGN KEY (relatedseries_thatseries_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE moderation
  ADD CONSTRAINT fk_moderation_series FOREIGN KEY (moderation_series_fk)
      REFERENCES series (series_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE moderation
  ADD CONSTRAINT fk_moderation_season FOREIGN KEY (moderation_season_fk)
      REFERENCES seasons (season_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE moderation
  ADD CONSTRAINT fk_moderation_episode FOREIGN KEY (moderation_episode_fk)
      REFERENCES episodes (episode_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE moderation
  ADD CONSTRAINT fk_moderation_banner FOREIGN KEY (moderation_banner_fk)
      REFERENCES banners (banner_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE moderation
  ADD CONSTRAINT fk_moderation_modifiedby FOREIGN KEY (moderation_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE moderation
  ADD CONSTRAINT fk_moderation_completedby FOREIGN KEY (moderation_completedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE episodemusic
  ADD CONSTRAINT fk_epmusic_episode FOREIGN KEY (epmusic_episode_fk)
      REFERENCES episodes (episode_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE episodemusic
  ADD CONSTRAINT fk_epmusic_modifiedby FOREIGN KEY (epmusic_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE episodetrivia
  ADD CONSTRAINT fk_eptrivia_episode FOREIGN KEY (eptrivia_episode_fk)
      REFERENCES episodes (episode_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE episodetrivia
  ADD CONSTRAINT fk_eptrivia_modifiedby FOREIGN KEY (eptrivia_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

-- ---------------------------------------------------------

ALTER TABLE revisions
  ADD CONSTRAINT fk_revision_modifiedby FOREIGN KEY (revision_modifiedby_fk)
      REFERENCES users (user_pk) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT;

Coco
Site Admin
Posts: 2475
Joined: Tue Mar 13, 2007 10:16 pm
Location: Canada

Re: Latest updated schema

Postby Coco » Sat May 09, 2009 12:08 pm

OK I haven't read it all but there are a few comments I need to make. First I think it might be a good idea to keep a url field in the network table. Right now we store links to wikipedia (I know it's not shown on the site) and it's been very helpful to avoid duplicates. It's hard to keep track of everything since there are quite a few completely diffrent networks with exactly the same name. Plus this field could be used to link to the official network website as well which to me makes sense to store.

Also, the way you've got the locking fields setup might be an issue. We discussed allowing levels of locking which isn't supported right now, you're just storing who locked it and when. We need a third field to store information on how it's locked. Such as locking a single language, locking out the add new episode button or perhaps even somethign to make it so only blank fields can be filled in, etc. We could use just a single number field for this (much the same way linux file permissions work) or we could store that in a separate table but we need to allow better control of locking.

It might also be a good idea to include the locking fields on more tables. Such as the episodes (I know we almost never lock at this level) and the people table (this one we'll most likely use more).
Please don't PM me. Post in the forums instead unless there is a very good reason for a PM or I won't respond.

szsori
Site Admin
Posts: 1911
Joined: Fri Nov 03, 2006 5:23 pm

Re: Latest updated schema

Postby szsori » Sat May 09, 2009 3:09 pm

What about a completely separate locking table? It would include fields for series, season, episode, field, and language. If language is filled in, locking will apply specifically to that language. If only series is filled in, the entire thing will be locked (once again, language-specific if noted). If series and season are filled, that specific season is locked. And if series, season, episode are filled, then just the episode record is locked.

As far as the other tables, that could be done easily as well by including fields for those tables. So we'd have network, actor, etc fields in the locking table. If they're filled, those records are locked.

I think that would handle everything, right? I like the idea of field locking since we can completely lock down the names of each episode which would allow people to modify anything other than the order.

Coco
Site Admin
Posts: 2475
Joined: Tue Mar 13, 2007 10:16 pm
Location: Canada

Re: Latest updated schema

Postby Coco » Sat May 09, 2009 4:35 pm

The way you just suggested doesn't support field locking though does it? To do field locking the way you suggest we'd have to have a table with all the fields of the other tables as bools to indicate individually locked fields. If you want to support field locking wouldn't using a single number field be easier? It would keep everything in a single field rather then having to have tables at each level to store all the locking information?
Please don't PM me. Post in the forums instead unless there is a very good reason for a PM or I won't respond.

szsori
Site Admin
Posts: 1911
Joined: Fri Nov 03, 2006 5:23 pm

Re: Latest updated schema

Postby szsori » Sat May 09, 2009 5:20 pm

Remember that field names are now unique, so we don't need to store a table/field combination to know which one we're talking about.

Coco
Site Admin
Posts: 2475
Joined: Tue Mar 13, 2007 10:16 pm
Location: Canada

Re: Latest updated schema

Postby Coco » Sat May 09, 2009 5:42 pm

Hmm, it could work either way. Your way is nice because code wouldn't have to be changed to add an extra field (Then again we wouldn't have to change the code with storing the number either since we could just keep a table which indicates what each bit represents in the number). On the down side a separate table means storing a lot more information and could result in a rather large locking table over time. Not sure how much of an issue that would be as the fields would still be indexed so I wouldn't think it should slow things down much.

One thing that does confuse me though is how would we add extra locks with your method. I'm not sure exactly what we would support, things like disabling adding new episodes and seasons or disable adding of art work at diffrent levels? I suppose we could handle the only empty fields can be edited by locking all non null fields but it just seems like that approach is going to make for a very big table very fast since that is one option we are likely to use a fair amount.
Please don't PM me. Post in the forums instead unless there is a very good reason for a PM or I won't respond.

szsori
Site Admin
Posts: 1911
Joined: Fri Nov 03, 2006 5:23 pm

Re: Latest updated schema

Postby szsori » Sat May 09, 2009 6:44 pm

Coco wrote:Hmm, it could work either way. Your way is nice because code wouldn't have to be changed to add an extra field (Then again we wouldn't have to change the code with storing the number either since we could just keep a table which indicates what each bit represents in the number). On the down side a separate table means storing a lot more information and could result in a rather large locking table over time. Not sure how much of an issue that would be as the fields would still be indexed so I wouldn't think it should slow things down much.

One thing that does confuse me though is how would we add extra locks with your method. I'm not sure exactly what we would support, things like disabling adding new episodes and seasons or disable adding of art work at diffrent levels? I suppose we could handle the only empty fields can be edited by locking all non null fields but it just seems like that approach is going to make for a very big table very fast since that is one option we are likely to use a fair amount.


The thing about using a separate table is that it wouldn't be linked into queries. It would just be a cached result that's updated whenever the locking is updated. That means no per-page query, but it'll act as if there were. The benefit of using the actual field name instead of a number is that the code becomes extremely simple. Just something like:

Code: Select all

<input name="actorname" value="<?=$actorname?>" <?php if $locked[actorname] print "readonly;" ?>>


Obviously we'd probably do it a little different than that, but overall it'd be pretty simple to do. With a number we'd have to look it up in the database while writing code, in addition to putting each field into the table in advance.

Additional locks would just be a "custom" field in the locking table. Just a varchar that holds "no new episodes", "no new seasons", "no new art", etc along with whichever foreign keys are pertinent (series id, season id, etc). I'll have to make some test cases and make sure it'll work, but I think it'll be fine.

Coco
Site Admin
Posts: 2475
Joined: Tue Mar 13, 2007 10:16 pm
Location: Canada

Re: Latest updated schema

Postby Coco » Sat May 09, 2009 8:48 pm

I dunno which way is best. The table seems more complicated to me. Just a single number field seems cleaner to me. If we say the 8th bit means lock series, with 201 the 8th bit is on so the series is locked. The only downside is we'd have to assign each bit to a field to do per field locking which I admit does sound like a pain although it would only take about 10 mins and only has to be done once. Any extra kind of locks we want to add support for later can just be assigned another bit.

I really don't know which is the best way to go. Either way is going to be rather simple to code since it's either check to see if any given bit is true in a number (we could store it as a text data type and keep the numbers in binary so we can check any bit using substr) or check a table to see if a record exsists. I just see the number being easier since we'd already have queried the table so it would save us the need of an extra query.

Ultimately you're more likely to do most of the coding on the new site and I'm really not sure if it makes much diffrence anyways so unless we find out it makes a huge diffrence one way or the other I'm fine with however you want to do it.
Please don't PM me. Post in the forums instead unless there is a very good reason for a PM or I won't respond.

lair
Posts: 23
Joined: Tue Mar 10, 2009 11:41 am

Re: Latest updated schema

Postby lair » Sun May 10, 2009 11:58 pm

I have been looking over the new scheme and it seems quite good. I am very exciting and I cannot wait for it be implemented :D

As for the locking debate I think the numerical locking might be easier in the long also cause it allows for adding in additional fields that might need to be locked. A function could be written to allow quick decoding of the lock string to an associative array similar to that example to make the HTML nice.

I do not know how overkill this might be but would it be worth setting up permission levels and enforcing some kinda of lock down across everything. Then based on pass update history grant certain API keys more or less access to do updates. I say this because I have noticed that there has been some fighting about correct episodes numbers and placements (Kings, Battlestar Galactica, 24).

Coco
Site Admin
Posts: 2475
Joined: Tue Mar 13, 2007 10:16 pm
Location: Canada

Re: Latest updated schema

Postby Coco » Mon May 11, 2009 12:30 am

We wouldn't do it by api key as API keys are used by whole programs and could represent thousands of diffrent users (In fact I think it already does with XBMC). We could do it by user accounts but we'll more likely go for a more limited approach, where certain users get more access to series. It's something we've talked about I'm not sure where we settled on it though.
Please don't PM me. Post in the forums instead unless there is a very good reason for a PM or I won't respond.