Database Schema

Introduction

There are three primary document views utilized by the ISFDB: showing the bibliography of an author, showing the titles present in a publication, and showing the publications in which a title was published. These views require three primary tables: authors, titles, and publications. Various normalization tables are provided to reduce errors and storage needs.

The illustration below shows the relationship between the various tables. All notes are stored in the notes table, which other tables refer to via the primary note_id key. There are two tables which are used for administrative and display purposes. The metadata table contains data pertinent to the database itself, while the magazine table contains information that allows magazine publication to be grouped under a single magazine heading.

A high-level explanation of the database can be found in the Database Overview article. An up to date diagram of the ISFDB tables and their relationships can be found here.

Global Tables

Information stored in the global tables are shared across the title, author, and publication related tables in the ISFDB.

• Schema:metadata - Contains metadata about the database itself.
• Schema:notes - General-purpose notes area.
• Schema:submissions - Table for storing data submissions.
• Schema:history - Tracks database change history.
• Schema:directory - Holds author directory information.
• Schema:license_keys - Holds user license key information.

Author-Related Tables

The following tables are used to store information about authors and their works:

• Schema:authors - Contains information about an author.
• Schema:canonical_author - Links author records to title records.
• Schema:emails - Specialized table for storing email addresses.
• Schema:pseudonyms - Table of pseudonyms.
• Schema:pub_authors - Links author records to publication records.
• Schema:webpages - Specialized table for storing webpage URLs.

Publication-Related Tables

The following tables are used to store information about publications:

• Schema:magazine - Contains magazine information. Will be deleted in near future.
• Schema:publishers - Contains publisher information.
• Schema:pubs - Contains information on a specific publication.
• Schema:pub_content - Links title records to a specific publication.
• Schema:verification - Contains verification information for a specific publication.
• Schema:reference - Contains information on verification references.

Title-Related Tables

The following tables are used to store information about titles:

• Schema:series - Contains series information.
• Schema:titles - Contains information about a specific title.
• Schema:tags - Contains user-defined title tags.

tag_mapping

CREATE TABLE tag_mapping (
   tagmap_id     int(11) NOT NULL auto_increment,
   tag_id        int(11) default 0,
   title_id      int(11) default 0,
   user_id       int(11) default 0,
   PRIMARY KEY   tagmap_id,
   KEY title_id  (title_id),
   KEY user_id   (user_id),
   KEY tag_id    (tag_id),
) ENGINE=MyISAM;

This table maps a unique user-defined tag to a particular title. This table forms unique tuples of (tag, title, user).

• tagmap_id - This column is the unique record id for the tag_mapping table. A tag mapping may be referred to by this unique id.

• tag_id - The id of the tag record.

• title_id - The id of the title record.

• user_id - The id of the user record.

votes

CREATE TABLE `votes` (
   vote_id        int(11) NOT NULL auto_increment,
   title_id       int(11) default NULL,
   user_id        int(11) default NULL,
   rating         int(11) default NULL,
   PRIMARY KEY    (vote_id),
   KEY title_id   (title_id),
   KEY user_id    (user_id),
) ENGINE=MyISAM;

This table tracks the vote for a particular title by a particular user.

• vote_id - This column is the unique record id for the votes table. A vote may be referred to by this unique id.

• title_id - The id of the title record.

• user_id - The id of the user record.

• rating - The vote, ranging from 1 (very bad) to 10 (very good).

Awards-Related Tables

awards

CREATE TABLE awards (
   award_id           int(11) NOT NULL auto_increment,
   award_title        mediumtext,
   award_author       mediumtext,
   award_year         date default NULL,
   award_ttype        varchar(8) default NULL,
   award_atype        mediumtext,
   award_level        mediumtext,
   award_movie        mediumtext,
   PRIMARY KEY        (award_id)
) TYPE=MyISAM;

The awards table directly replicates the old ISFDB awards record format.

• award_id - This column is the unique record id for the awards table. An award may be referred to by this unique id.

• award_title - This column holds the title of the work.

• award_author - This column holds the author of the work.

• award_year - This column holds the date of the award.

• award_ttype - This column holds a two-letter award code:
  • An = Analog Award
  • Ap = Apollo Award
  • Ar = Asimov's Readers' Poll
  • As = Aurealis Award
  • Au = Aurora Award
  • Ax = Asimov's Undergraduate Award
  • Bf = British Fantasy Award
  • Bl = Balrog Award
  • Bs = British Science Fiction Award
  • Ca = John W. Campbell Memorial Award
  • Cc = Compton Crook Award
  • Cl = Arthur C. Clarke Award
  • Cy = Chesley Award
  • Dr = Deathrealm Award
  • Dt = Ditmar Award
  • En = Endeavour Award
  • Ga = Gandalf Award
  • Gd = Golden Duck Award
  • Gg = Gaughan Award
  • Hf = Hall of Fame Award
  • Hm = HOMer Award
  • Hu = Hugo Award
  • If = International Fantasy Award
  • Ih = Internation Horror Guild Award
  • Im = Imaginaire Award
  • Jc = John W. Campbell Award
  • Lc = Locus Poll Award
  • Lm = Lambda Award
  • My = Mythopoeic Award
  • Ne = Nebula Award
  • Pk = Philip K. Dick Award
  • Pr = Prometheus Award
  • Rh = Retro Hugo Award
  • Ry = Rhysling Award
  • Sc = SF Chronicle Award
  • Sf = SFBC Award
  • Sk = Skylark Award
  • Sn = Sunburst Award
  • St = Bram Stoker Award
  • Su = Sturgeon Award
  • Sw = Sidewise Award
  • Tp = James Tiptree, Jr. Award
  • Wf = World Fantasy Award
  • Wh = James White Award

• award_atype - This column holds an ASCII plain-text description of the award, for instance "Best Novella" or "Best Novel".

• award_level - This column holds an ASCII numeral (as well as other textual information, which precludes this being an int) that indicates the works position in a poll (1, 2, 3, etc) or its nomination/win status.

• award_movie - This column holds an IMDB-compatible title for a movie.

title_awards

CREATE TABLE title_awards (
   taw_id         int(11) NOT NULL auto_increment,
   award_id       int(11) default NULL,
   title_id       int(11) default NULL,
   PRIMARY KEY    (taw_id),
   KEY award_id   (award_id),
   KEY title_id   (title_id)
) ENGINE=MyISAM;

The title_awards table maps an award to a title listed in the titles table.

• taw_id - This column is the unique record id for the title_awards table.

• award_id - This column contains an integer value which refers to the ID of a record found in the awardss table.

• title_id - This column contains an integer value which refers to the ID of a record found in the titles table.