Difference between revisions of "Database Schema"
(→Publication-Related Tables: Moving publishers) |
(→Publication-Related Tables: Moving pubs) |
||
Line 1: | Line 1: | ||
+ | ==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 [http://www.isfdb.org/isfdb_schema.png 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. | ||
+ | |||
+ | |||
+ | ===pub_content=== | ||
+ | |||
+ | CREATE TABLE pub_content ( | ||
+ | pubc_id int(11) NOT NULL auto_increment, | ||
+ | title_id int(11) default NULL, | ||
+ | pub_id int(11) default NULL, | ||
+ | pubc_page varchar(8), | ||
+ | PRIMARY KEY (pubc_id), | ||
+ | KEY pubindex (pub_id), | ||
+ | KEY titleindex (title_id) | ||
+ | ) TYPE=MyISAM; | ||
+ | |||
+ | The pub_content table contains a particular instantiation of a title as it appeared in a publication; future versions of the table will include columns for variant titles, authors, and page numbers. It also serves as a normalization table to map a publication record to a title record. The table includes indexes for both the title_id and pub_id columns. | ||
+ | |||
+ | * '''pubc_id''' - This column is the unique record id for the pub_content table. | ||
+ | |||
+ | * '''title_id''' - This column is a foreign key which points to a title record. | ||
+ | |||
+ | * '''pub_id''' - This column is a foreign key which points to a publication record. | ||
+ | |||
+ | * '''pubc_page''' - Page number in the referred publication upon which the referred title appears. | ||
+ | |||
+ | |||
+ | ===verification=== | ||
+ | |||
+ | CREATE TABLE verification ( | ||
+ | verification_id int(11) NOT NULL auto_increment, | ||
+ | pub_id int(11) default NULL, | ||
+ | reference_id int(11) default NULL, | ||
+ | user_id int(11) default NULL, | ||
+ | ver_time datetime default NULL, | ||
+ | ver_status int(11) default NULL, | ||
+ | PRIMARY KEY (verification_id), | ||
+ | KEY pub_id (pub_id), | ||
+ | KEY reference_id (reference_id), | ||
+ | KEY user_id (user_id) | ||
+ | ) ENGINE=MyISAM; | ||
+ | |||
+ | The verification table is used to verify a publication. There can be a verification record for each reference in the reference table. The verification record sets the verification state for a particular publication, against a particular reference, recording the user and time the verification was made. | ||
+ | |||
+ | * '''verification_id''' - This column is the unique record id for the verification table. | ||
+ | |||
+ | * '''pub_id''' - This column is a foreign key which points to a publication record. | ||
+ | |||
+ | * '''reference_id''' - This column is a foreign key which points to a reference record. | ||
+ | |||
+ | * '''user_id''' - This column is a foreign key which points to a user record. | ||
+ | |||
+ | * '''ver_time''' - Timestamp when the verification data was submitted. | ||
+ | |||
+ | * '''ver_status''' - The verification status for this publication for the specified reference. Can be one of: | ||
+ | ** 0 - Not verified. | ||
+ | ** 1 - Verified. | ||
+ | ** 2 - The reference has no data on this publication. | ||
+ | |||
+ | |||
+ | ===reference=== | ||
+ | |||
+ | CREATE TABLE reference ( | ||
+ | reference_id int(11) NOT NULL auto_increment, | ||
+ | reference_label mediumtext, | ||
+ | reference_fullname mediumtext, | ||
+ | pub_id int(11) default 0, | ||
+ | reference_url mediumtext, | ||
+ | PRIMARY KEY (reference_id) | ||
+ | ) ENGINE=MyISAM; | ||
+ | |||
+ | The reference table holds the officially recognized references used to verify publication records. | ||
+ | |||
+ | * '''reference_id''' - This column is the unique record id for the reference table. | ||
+ | |||
+ | * '''reference_label''' - A short label used to refer to this reference. | ||
+ | |||
+ | * '''reference_fullname''' - A full text description of the reference. | ||
+ | |||
+ | * '''pub_id''' - If the reference has been documented in the ISFDB, this field points to the publication record. | ||
+ | |||
+ | * '''reference_url''' - If the reference has an online description or is an online resource, this field points to the URL. | ||
+ | |||
+ | ==Title-Related Tables== | ||
+ | |||
+ | ===series=== | ||
+ | |||
+ | CREATE TABLE series ( | ||
+ | series_id int(11) NOT NULL auto_increment, | ||
+ | series_title varchar(64) default NULL, | ||
+ | series_parent int(11) default NULL, | ||
+ | series_type int(11) default NULL, | ||
+ | PRIMARY KEY (series_id), | ||
+ | KEY titles (series_title(5)) | ||
+ | ) ENGINE=MyISAM; | ||
+ | |||
+ | The submissions table holds data submissions | ||
+ | |||
+ | * '''series_id''' - This column is the unique record id for the series table. A series may be referred to by this unique id. | ||
+ | |||
+ | * '''series_title''' - This column holds the name of the series. | ||
+ | |||
+ | * '''series_parent''' - This column holds an integer which can point to another series record. This allows hierarchical series structures of indefinite depth. | ||
+ | |||
+ | * '''series_type''' - This column contains a value that indicates whether or not the series record is a terminating leaf. | ||
+ | ** 1 = Terminated Series | ||
+ | ** 2 = Parent Series | ||
+ | |||
+ | |||
+ | ===titles=== | ||
+ | |||
+ | CREATE TABLE titles ( | ||
+ | title_id int(11) NOT NULL auto_increment, | ||
+ | title_title mediumtext, | ||
+ | title_translator mediumtext, | ||
+ | title_synopsis int(11) default NULL, | ||
+ | note_id int(11) default NULL, | ||
+ | series_id int(11) default NULL, | ||
+ | title_seriesnum int(11) default NULL, | ||
+ | title_copyright date default NULL, | ||
+ | title_storylen mediumtext, | ||
+ | title_ttype enum('ANTHOLOGY','BACKCOVERART', | ||
+ | 'COLLECTION','COVERART', | ||
+ | 'INTERIORART','EDITOR', | ||
+ | 'ESSAY','INTERVIEW', | ||
+ | 'NOVEL','NONFICTION', | ||
+ | 'NONGENRE','OMNIBUS', | ||
+ | 'POEM','REVIEW', | ||
+ | 'SERIAL','SHORTFICTION') default NULL, | ||
+ | title_wikipedia mediumtext, | ||
+ | title_views int(11) default 0, | ||
+ | title_parent int(11) default 0, | ||
+ | title_rating float default NULL, | ||
+ | title_annualviews int(11) NOT NULL default 0, | ||
+ | title_ctl int(10) unsigned NOT NULL default 0, | ||
+ | PRIMARY KEY (title_id), | ||
+ | KEY series_id (series_id), | ||
+ | KEY note_id (note_id) | ||
+ | ) ENGINE=MyISAM; | ||
+ | |||
+ | |||
+ | * '''title_id''' - This column is the unique record id for the titles table. A title may be referred to by this unique id. | ||
+ | |||
+ | * '''title_title''' - This column holds the working title. | ||
+ | |||
+ | * '''title_translator''' - This column holds information concerning translations. It is currently of the form Translation;Translation;Translation, where each translation is of the form Language,Year,Translator. In general, this format is awkward and error-prone, and a new mechanism is needed to handle translation information. It really should be attached to the publication, but that leaves a problem when talking about a single work of short fiction in a magazine. | ||
+ | |||
+ | * '''title_synopsis''' - This column holds a pointer to a free-form note that describes the basic synopsis of the work. | ||
+ | |||
+ | * '''note_id''' - This column contains an integer value which refers to the ID of a record found in the notes table. | ||
+ | |||
+ | * '''series_id''' = This column contains an integer value which refers to the ID of a record found in the series table. | ||
+ | |||
+ | * '''title_seriesnum''' - This column contains the series number of the title. We need to think about what the right thing to do is if series information is pulled out into another table. | ||
+ | |||
+ | * '''title_copyright''' - This column contains the copyright year of the title (date of first publication). | ||
+ | |||
+ | * '''title_storylen''' - This column has been overloaded to mean numerous things. For shortfiction, nv=novella, nt=novelette, ss=shortstory, sf=shortfiction (unknown length). For novels jvn=juvenile, nvz=novelization. This is totally messed up and needs a new mechanism. | ||
+ | |||
+ | * '''title_ttype''' - This column identifies the title type | ||
+ | |||
+ | * '''title_wikipedia''' - This column holds the URL to a Wikipedia article. | ||
+ | |||
+ | * '''title_views''' - This column contains the number of times a particular title has been viewed. | ||
+ | |||
+ | * '''title_parent''' - This column is a pointer to a parent title. If zero, the record is a canonical title; if non-zero the record is a variant title, and the column refers to the parent canonical title. | ||
+ | |||
+ | * '''title_rating''' - This column contains this title's average vote rating. This column is recalculated whenever a users posts a vote against this particular title. This column is set only when 5 or more votes exist for the title. | ||
+ | |||
+ | * '''title_annualviews''' - This column contains the number of times a particular title has been viewed in the current calendar year. | ||
+ | |||
+ | * '''title_ctl''' - This column contains exception bits which can be used to control the title display under distinct unique conditions. | ||
+ | |||
+ | |||
+ | ===tags=== | ||
+ | |||
+ | CREATE TABLE tags ( | ||
+ | tag_id int(11) NOT NULL auto_increment, | ||
+ | tag_name tinytext, | ||
+ | PRIMARY KEY tag_id | ||
+ | ) ENGINE=MyISAM; | ||
+ | |||
+ | The tags table tracks all unique user-defined tags that are attached to titles. | ||
+ | |||
+ | * '''tag_id''' - This column is the unique record id for the tags table. A tag may be referred to by this unique id. | ||
+ | |||
+ | * '''title_name''' - The actual tag. | ||
+ | |||
+ | |||
+ | ===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. |
Revision as of 20:30, 22 May 2008
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.
pub_content
CREATE TABLE pub_content ( pubc_id int(11) NOT NULL auto_increment, title_id int(11) default NULL, pub_id int(11) default NULL, pubc_page varchar(8), PRIMARY KEY (pubc_id), KEY pubindex (pub_id), KEY titleindex (title_id) ) TYPE=MyISAM;
The pub_content table contains a particular instantiation of a title as it appeared in a publication; future versions of the table will include columns for variant titles, authors, and page numbers. It also serves as a normalization table to map a publication record to a title record. The table includes indexes for both the title_id and pub_id columns.
- pubc_id - This column is the unique record id for the pub_content table.
- title_id - This column is a foreign key which points to a title record.
- pub_id - This column is a foreign key which points to a publication record.
- pubc_page - Page number in the referred publication upon which the referred title appears.
verification
CREATE TABLE verification ( verification_id int(11) NOT NULL auto_increment, pub_id int(11) default NULL, reference_id int(11) default NULL, user_id int(11) default NULL, ver_time datetime default NULL, ver_status int(11) default NULL, PRIMARY KEY (verification_id), KEY pub_id (pub_id), KEY reference_id (reference_id), KEY user_id (user_id) ) ENGINE=MyISAM;
The verification table is used to verify a publication. There can be a verification record for each reference in the reference table. The verification record sets the verification state for a particular publication, against a particular reference, recording the user and time the verification was made.
- verification_id - This column is the unique record id for the verification table.
- pub_id - This column is a foreign key which points to a publication record.
- reference_id - This column is a foreign key which points to a reference record.
- user_id - This column is a foreign key which points to a user record.
- ver_time - Timestamp when the verification data was submitted.
- ver_status - The verification status for this publication for the specified reference. Can be one of:
- 0 - Not verified.
- 1 - Verified.
- 2 - The reference has no data on this publication.
reference
CREATE TABLE reference ( reference_id int(11) NOT NULL auto_increment, reference_label mediumtext, reference_fullname mediumtext, pub_id int(11) default 0, reference_url mediumtext, PRIMARY KEY (reference_id) ) ENGINE=MyISAM;
The reference table holds the officially recognized references used to verify publication records.
- reference_id - This column is the unique record id for the reference table.
- reference_label - A short label used to refer to this reference.
- reference_fullname - A full text description of the reference.
- pub_id - If the reference has been documented in the ISFDB, this field points to the publication record.
- reference_url - If the reference has an online description or is an online resource, this field points to the URL.
Title-Related Tables
series
CREATE TABLE series ( series_id int(11) NOT NULL auto_increment, series_title varchar(64) default NULL, series_parent int(11) default NULL, series_type int(11) default NULL, PRIMARY KEY (series_id), KEY titles (series_title(5)) ) ENGINE=MyISAM;
The submissions table holds data submissions
- series_id - This column is the unique record id for the series table. A series may be referred to by this unique id.
- series_title - This column holds the name of the series.
- series_parent - This column holds an integer which can point to another series record. This allows hierarchical series structures of indefinite depth.
- series_type - This column contains a value that indicates whether or not the series record is a terminating leaf.
- 1 = Terminated Series
- 2 = Parent Series
titles
CREATE TABLE titles ( title_id int(11) NOT NULL auto_increment, title_title mediumtext, title_translator mediumtext, title_synopsis int(11) default NULL, note_id int(11) default NULL, series_id int(11) default NULL, title_seriesnum int(11) default NULL, title_copyright date default NULL, title_storylen mediumtext, title_ttype enum('ANTHOLOGY','BACKCOVERART', 'COLLECTION','COVERART', 'INTERIORART','EDITOR', 'ESSAY','INTERVIEW', 'NOVEL','NONFICTION', 'NONGENRE','OMNIBUS', 'POEM','REVIEW', 'SERIAL','SHORTFICTION') default NULL, title_wikipedia mediumtext, title_views int(11) default 0, title_parent int(11) default 0, title_rating float default NULL, title_annualviews int(11) NOT NULL default 0, title_ctl int(10) unsigned NOT NULL default 0, PRIMARY KEY (title_id), KEY series_id (series_id), KEY note_id (note_id) ) ENGINE=MyISAM;
- title_id - This column is the unique record id for the titles table. A title may be referred to by this unique id.
- title_title - This column holds the working title.
- title_translator - This column holds information concerning translations. It is currently of the form Translation;Translation;Translation, where each translation is of the form Language,Year,Translator. In general, this format is awkward and error-prone, and a new mechanism is needed to handle translation information. It really should be attached to the publication, but that leaves a problem when talking about a single work of short fiction in a magazine.
- title_synopsis - This column holds a pointer to a free-form note that describes the basic synopsis of the work.
- note_id - This column contains an integer value which refers to the ID of a record found in the notes table.
- series_id = This column contains an integer value which refers to the ID of a record found in the series table.
- title_seriesnum - This column contains the series number of the title. We need to think about what the right thing to do is if series information is pulled out into another table.
- title_copyright - This column contains the copyright year of the title (date of first publication).
- title_storylen - This column has been overloaded to mean numerous things. For shortfiction, nv=novella, nt=novelette, ss=shortstory, sf=shortfiction (unknown length). For novels jvn=juvenile, nvz=novelization. This is totally messed up and needs a new mechanism.
- title_ttype - This column identifies the title type
- title_wikipedia - This column holds the URL to a Wikipedia article.
- title_views - This column contains the number of times a particular title has been viewed.
- title_parent - This column is a pointer to a parent title. If zero, the record is a canonical title; if non-zero the record is a variant title, and the column refers to the parent canonical title.
- title_rating - This column contains this title's average vote rating. This column is recalculated whenever a users posts a vote against this particular title. This column is set only when 5 or more votes exist for the title.
- title_annualviews - This column contains the number of times a particular title has been viewed in the current calendar year.
- title_ctl - This column contains exception bits which can be used to control the title display under distinct unique conditions.
tags
CREATE TABLE tags ( tag_id int(11) NOT NULL auto_increment, tag_name tinytext, PRIMARY KEY tag_id ) ENGINE=MyISAM;
The tags table tracks all unique user-defined tags that are attached to titles.
- tag_id - This column is the unique record id for the tags table. A tag may be referred to by this unique id.
- title_name - The actual tag.
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.