Difference between revisions of "Schema:titles"

From ISFDB
Jump to navigation Jump to search
(Breaking up larger article)
 
m (Fixed alignment of attributes for title_ttype)
 
(14 intermediate revisions by 3 users not shown)
Line 2: Line 2:
  
 
  +-------------------+-----------------------------------------+------+-----+---------+----------------+
 
  +-------------------+-----------------------------------------+------+-----+---------+----------------+
−
  | Field            | Type                                    | Null | Key | Default | Extra          |
+
  | Field            | Type                                    | Null | Key | Default | Comment        |
 
  +-------------------+-----------------------------------------+------+-----+---------+----------------+
 
  +-------------------+-----------------------------------------+------+-----+---------+----------------+
 
  | title_id          | int(11)                                | NO  | PRI | NULL    | auto_increment |
 
  | title_id          | int(11)                                | NO  | PRI | NULL    | auto_increment |
Line 17: Line 17:
 
  |                  |  'INTERIORART', 'EDITOR', 'ESSAY',    |      |    |        |                |
 
  |                  |  'INTERIORART', 'EDITOR', 'ESSAY',    |      |    |        |                |
 
  |                  |  'INTERVIEW', 'NOVEL', 'NONFICTION',  |      |    |        |                |
 
  |                  |  'INTERVIEW', 'NOVEL', 'NONFICTION',  |      |    |        |                |
−
  |                  |  'NONGENRE', 'OMNIBUS', 'POEM',       |      |    |        |                |
+
  |                  |  'OMNIBUS', 'POEM', 'REVIEW', 'SERIAL' |      |    |        |                |
−
|                  |  'REVIEW','SERIAL', 'SHORTFICTION',    |      |    |        |                |
+
  |                  |  'SHORTFICTION', 'CHAPBOOK')           | YES  | MUL | NULL    |                |
−
  |                  |  'CHAPTERBOOK')                       | YES  | MUL | NULL    |                |
 
 
  | title_wikipedia  | mediumtext                              | YES  |    | NULL    |                |
 
  | title_wikipedia  | mediumtext                              | YES  |    | NULL    |                |
 
  | title_views      | int(11)                                | NO  |    | 0      |                |
 
  | title_views      | int(11)                                | NO  |    | 0      |                |
 
  | title_parent      | int(11)                                | NO  | MUL | 0      |                |
 
  | title_parent      | int(11)                                | NO  | MUL | 0      |                |
−
  | title_rating      | float                                  | YES  |    | NULL    |               |
+
  | title_rating      | float                                  | YES  |    | NULL    | No longer used |
 
  | title_annualviews | int(11)                                | NO  |    | 0      |                |
 
  | title_annualviews | int(11)                                | NO  |    | 0      |                |
 
  | title_ctl        | int(10) unsigned                        | NO  |    | 0      |                |
 
  | title_ctl        | int(10) unsigned                        | NO  |    | 0      |                |
 +
| title_language    | int(11)                                | YES  |    | NULL    |                |
 +
| title_seriesnum_2 | varchar(4)                              | YES  |    | NULL    |                |
 +
| title_non_genre  | enum('Yes','No')                        | YES  |    | No      |                |
 +
| title_graphic    | enum('Yes','No')                        | YES  |    | No      |                |
 +
| title_nvz        | enum('Yes','No')                        | YES  |    | No      |                |
 +
| title_jvn        | enum('Yes','No')                        | YES  |    | No      |                |
 +
| title_content    | varchar(32)                            | YES  |    | NULL    |                |
 
  +-------------------+-----------------------------------------+------+-----+---------+----------------+
 
  +-------------------+-----------------------------------------+------+-----+---------+----------------+
  
 
==Description==
 
==Description==
  
−
* '''title_id''' - This column is the unique record id for the titles table. A title may be referred to by this unique id.  
+
* '''title_id''' - Unique record id for the titles table.
  
−
* '''title_title''' - This column holds the working title.  
+
* '''title_title''' - The work's 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_translator''' - '''Not Used''' (This column was supposed to hold information concerning translations. The format was supposed to be Translation;Translation;Translation, where each translation was of the form Language,Year,Translator.)
  
−
* '''title_synopsis''' - This column holds a pointer to a free-form note that describes the basic synopsis of the work.  
+
* '''title_synopsis''' - A pointer to a free-form note in the notes table. The optional note contains 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.  
+
* '''note_id''' - A pointer to an optional free-form note about the title 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.  
+
* '''series_id''' - ID of this title's series. Points to a record 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_seriesnum''' - This title's number within its series.
  
−
* '''title_copyright''' - This column contains the copyright year of the title (date of first publication).  
+
* '''title_copyright''' - The date of first publication. This is NOT necessarily the work's copyright year, despite the misleading name.
  
−
* '''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_storylen''' - Only used for SHORTFICTION titles. The valid values are "novella", "novelette" and "shortstory".  
  
−
* '''title_ttype''' - This column identifies the title type
+
* '''title_ttype''' - The type of this title. Allowable values are listed in the field definition. Note that 'BACKCOVERART' is allowed by the table definition but not used by the software.
  
−
* '''title_wikipedia''' - This column holds the URL to a Wikipedia article.
+
* '''title_wikipedia''' - '''Not Used'''. (This column was originally used to hold the URL of a Wikipedia article, but is empty now.)
  
−
* '''title_views''' - This column contains the number of times a particular title has been viewed.
+
* '''title_views''' - The number of times this 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_parent''' - ID of this title's parent title. If zero, this title record is a canonical title; if non-zero, this title 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_rating''' - '''Not Used'''. (Originally this field stored each title's average vote rating. The value was recalculated whenever a user entered a vote for the title. It was set only when 5 or more votes existed for the title.)
  
−
* '''title_annualviews''' - This column contains the number of times a particular title has been viewed in the current calendar year.
+
* '''title_annualviews''' - The number of times this 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.
+
* '''title_ctl''' - '''Not Used''' (Originally this column was supposed to contain exception bits which could be then used to control the title display under certain conditions.)
 +
 
 +
* '''title_language''' - ID of this title's language. Points to a record found in the "languages" table.
 +
 
 +
* '''title_seriesnum_2''' - The part of the series number to the right of the decimal point (if one is present.)
 +
 
 +
* '''title_non_genre''' - Indicates whether this title non-genre. The valid vales are 'Yes' and 'No'.
 +
 
 +
* '''title_graphic''' - Indicates whether this title is graphic in nature. The valid values are 'Yes' and 'No'. Note that COVERART and INTERIORART titles are not considered graphic.
 +
 
 +
* '''title_nvz''' - Indicates whether this title is a novelization. The valid vales are 'Yes' and 'No'.
 +
 
 +
* '''title_jvn''' - Indicates whether this title is aimed at the juvenile/young adult audience. The valid vales are 'Yes' and 'No'.
 +
 
 +
* '''title_content''' - Content indicator for omnibuses, e.g. "1,2+ss".

Latest revision as of 15:15, 29 December 2018

Schema Summary

+-------------------+-----------------------------------------+------+-----+---------+----------------+
| Field             | Type                                    | Null | Key | Default | Comment        |
+-------------------+-----------------------------------------+------+-----+---------+----------------+
| title_id          | int(11)                                 | NO   | PRI | NULL    | auto_increment |
| title_title       | mediumtext                              | YES  | MUL | NULL    |                |
| title_translator  | mediumtext                              | YES  |     | NULL    |                |
| title_synopsis    | int(11)                                 | YES  |     | NULL    |                |
| note_id           | int(11)                                 | YES  | MUL | NULL    |                |
| series_id         | int(11)                                 | YES  | MUL | NULL    |                |
| title_seriesnum   | int(11)                                 | YES  |     | NULL    |                |
| title_copyright   | date                                    | YES  |     | NULL    |                |
| title_storylen    | mediumtext                              | YES  |     | NULL    |                |
| title_ttype       | enum('ANTHOLOGY', 'BACKCOVERART',       |      |     |         |                |
|                   |   'COLLECTION', 'COVERART',             |      |     |         |                |
|                   |   'INTERIORART', 'EDITOR', 'ESSAY',     |      |     |         |                |
|                   |   'INTERVIEW', 'NOVEL', 'NONFICTION',   |      |     |         |                |
|                   |   'OMNIBUS', 'POEM', 'REVIEW', 'SERIAL' |      |     |         |                |
|                   |   'SHORTFICTION', 'CHAPBOOK')           | YES  | MUL | NULL    |                |
| title_wikipedia   | mediumtext                              | YES  |     | NULL    |                |
| title_views       | int(11)                                 | NO   |     | 0       |                |
| title_parent      | int(11)                                 | NO   | MUL | 0       |                |
| title_rating      | float                                   | YES  |     | NULL    | No longer used |
| title_annualviews | int(11)                                 | NO   |     | 0       |                |
| title_ctl         | int(10) unsigned                        | NO   |     | 0       |                |
| title_language    | int(11)                                 | YES  |     | NULL    |                |
| title_seriesnum_2 | varchar(4)                              | YES  |     | NULL    |                |
| title_non_genre   | enum('Yes','No')                        | YES  |     | No      |                |
| title_graphic     | enum('Yes','No')                        | YES  |     | No      |                |
| title_nvz         | enum('Yes','No')                        | YES  |     | No      |                |
| title_jvn         | enum('Yes','No')                        | YES  |     | No      |                |
| title_content     | varchar(32)                             | YES  |     | NULL    |                |
+-------------------+-----------------------------------------+------+-----+---------+----------------+

Description

  • title_id - Unique record id for the titles table.
  • title_title - The work's title.
  • title_translator - Not Used (This column was supposed to hold information concerning translations. The format was supposed to be Translation;Translation;Translation, where each translation was of the form Language,Year,Translator.)
  • title_synopsis - A pointer to a free-form note in the notes table. The optional note contains the basic synopsis of the work.
  • note_id - A pointer to an optional free-form note about the title found in the notes table.
  • series_id - ID of this title's series. Points to a record in the series table.
  • title_seriesnum - This title's number within its series.
  • title_copyright - The date of first publication. This is NOT necessarily the work's copyright year, despite the misleading name.
  • title_storylen - Only used for SHORTFICTION titles. The valid values are "novella", "novelette" and "shortstory".
  • title_ttype - The type of this title. Allowable values are listed in the field definition. Note that 'BACKCOVERART' is allowed by the table definition but not used by the software.
  • title_wikipedia - Not Used. (This column was originally used to hold the URL of a Wikipedia article, but is empty now.)
  • title_views - The number of times this title has been viewed.
  • title_parent - ID of this title's parent title. If zero, this title record is a canonical title; if non-zero, this title record is a variant title and the column refers to the parent canonical title.
  • title_rating - Not Used. (Originally this field stored each title's average vote rating. The value was recalculated whenever a user entered a vote for the title. It was set only when 5 or more votes existed for the title.)
  • title_annualviews - The number of times this title has been viewed in the current calendar year.
  • title_ctl - Not Used (Originally this column was supposed to contain exception bits which could be then used to control the title display under certain conditions.)
  • title_language - ID of this title's language. Points to a record found in the "languages" table.
  • title_seriesnum_2 - The part of the series number to the right of the decimal point (if one is present.)
  • title_non_genre - Indicates whether this title non-genre. The valid vales are 'Yes' and 'No'.
  • title_graphic - Indicates whether this title is graphic in nature. The valid values are 'Yes' and 'No'. Note that COVERART and INTERIORART titles are not considered graphic.
  • title_nvz - Indicates whether this title is a novelization. The valid vales are 'Yes' and 'No'.
  • title_jvn - Indicates whether this title is aimed at the juvenile/young adult audience. The valid vales are 'Yes' and 'No'.
  • title_content - Content indicator for omnibuses, e.g. "1,2+ss".
Retrieved from "https://isfdb.org/wiki/index.php?title=Schema:titles&oldid=539585"