Difference between revisions of "Database Schema"

Revision as of 11:52, 5 July 2023

This page lists all MySQL tables used by the ISFDB software and links to more detailed pages which provide a field-level view of each table.

Introduction

The ISFDB is built around the following types of records:

authors, which include editors and artists
publications, i.e. any separate appearances of SF-related works, mostly books and magazines
titles, i.e. separate works of fiction, non-fiction and art which can appear in one or more publications
series, i.e. groups of related titles
publishers
publication series, that is otherwise unrelated publications grouped together by the publisher, e.g. Ace Double
awards

Note that all notes are stored in the notes table, which other tables refer to via the primary note_id key. (The only exception is the Authors table which contains author notes.) The metadata table contains data pertinent to the database itself.

A high-level explanation of the database can be found in the Database Overview article (out of date.) An out-of-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:directory - Author directory information.
Schema:history - Tracks database change history (obsolete.)
Schema:languages - List of all languages supported by the ISFDB. Must be kept in sync with LANGUAGES in common/isfdb.py.
Schema:license_keys - User license key information.
Schema:metadata - Metadata about the database itself.
Schema:notes - General-purpose notes area.
Schema:recognized_domains - Internet domain names recognized by the ISFDB.
Schema:submissions - Data submissions.
Schema:webpages - URLs of other Web sites. Used by authors, publishers, publication series, titles, series, award types, award categories and publications.

User Tables

Schema:user_languages‎ - User Language preferences.
Schema:user_preferences - User preferences.
Schema:user_sites - User Website preferences.
Schema:changed_verified_pubs - Changes to primary-verified publications
Schema:user_status - User status information

MediaWiki Tables

The ISFDB uses MediaWiki software to run its Wiki. See this MediaWiki chart for lists of table fields. The following MediaWiki pages are also used by the ISFDB software:

mw_page - Information about all Wiki pages. Used by the nightly cleanup reports to find Wiki-ISFDB mismatches.
mw_user‎ - User ID, name and other user-specific information. The version in publicly available backups has been cleansed of all private data.
mw_user‎_group - Includes the Wiki group (editors, moderators/sysops or bureaucrats) to which each user belongs.

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:author_views - Contains statistical data about the number of times author records have been viewed.

Publication-Related Tables

The following tables are used to store information about publications:

Schema:identifier_types - Contains information on external identifier types.
Schema:identifier_sites - Contains information on Web sites for external identifier types.
Schema:identifiers - Contains information on external identifiers.
Schema:magazine - Contains magazine information. Unused.
Schema:primary_verifications - Contains primary verification information for a specific publication.
Schema:pub_content - Links title records to a specific publication.
Schema:pub_series - Contains information on a publication series.
Schema:publishers - Contains publisher information.
Schema:pubs - Contains information on a specific publication.
Schema:reference - Contains information on secondary verification references.
Schema:verification - Contains secondary verification information for a specific publication.
Schema:websites - Third party (bookseller and bibliographic) sites that a publication may link to based on its ISBN.

Title-Related Tables

The following tables are used to store information about titles:

Schema:series - Contains series information.
Schema:tag_mapping - Links a tags entry to a specific title per user.
Schema:tags - Contains user-defined title tags.
Schema:title_relationships - Maps relationship between a review, serial, or translations to a title.
Schema:titles - Contains information about a specific title.
Schema:votes - Tracks votes for a specific title per user.
Schema:title_views - Contains statistical information about the number of times each title's record has been viewed by ISFDB users.

Awards-Related Tables

The following tables are used to store information about awards:

Schema:award_cats - Contains information about award categories.
Schema:award_types - Contains information about award types.
Schema:awards - Contains award information.
Schema:title_awards - Links award records to their related title records.

Transliteration Tables

Schema:trans_authors - Transliterated canonical names for authors
Schema:trans_legal_names - Transliterated legal names for authors
Schema:trans_pub_series - Transliterated names for publication series
Schema:trans_publisher - Transliterated names for publishers
Schema:trans_pubs - Transliterated titles for publications
Schema:trans_series - Transliterated names for regular (title) series
Schema:trans_titles - Transliterated titles for title records

Data Cleanup and Statistics Tables

Schema:bad_images - Publication IDs with suspect images
Schema:cleanup - Record IDs in need of review and potential cleanup
Schema:sfe3_authors - URLs of SFE3 articles about people that are not linked from ISFDB author records
Schema:most_reviewed - Most-reviewed titles by year and decade
Schema:award_titles_report - Titles with most awards and nominations by year, decade and title type

@@ Line 1: / Line 1: @@
+This page lists all MySQL tables used by the ISFDB software and links to more detailed pages which provide a field-level view of each table.
 ==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 ISFDB is built around the following types of records:
+* authors, which include editors and artists
+* publications, i.e. any separate appearances of SF-related works, mostly books and magazines
+* titles, i.e. separate works of fiction, non-fiction and art which can appear in one or more publications
+* series, i.e. groups of related titles
+* publishers
+* publication series, that is otherwise unrelated publications grouped together by the publisher, e.g. ''Ace Double''
+* awards
-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. The metadata table contains data pertinent to the database itself.
+Note that all notes are stored in the notes table, which other tables refer to via the primary note_id key. (The only exception is the Authors table which contains author notes.) The metadata table contains data pertinent to the database itself.
-A high-level explanation of the database can be found in the [[Database Overview]] article. An out-of-date diagram of the ISFDB tables and their relationships can be found [http://www.isfdb.org/isfdb_schema.png here].
+A high-level explanation of the database can be found in the [[Database Overview]] article (out of date.) An out-of-date diagram of the ISFDB tables and their relationships can be found [http://www.isfdb.org/isfdb_schema.png here].
 ==Global Tables==
@@ Line 12: / Line 22: @@
 * [[Schema:directory]] - Author directory information.
-* [[Schema:history]] - Tracks database change history.
+* [[Schema:history]] - Tracks database change history (obsolete.)
 * [[Schema:languages]] - List of all languages supported by the ISFDB. Must be kept in sync with LANGUAGES in common/isfdb.py.
 * [[Schema:license_keys]] - User license key information.
 * [[Schema:metadata]] - Metadata about the database itself.
-* [[Schema:mw_page]] - MediWaiki table with information about all Wiki pages. Used by the nightly cleanup reports to find Wiki-ISFDB mismatches.
-* [[Schema:mw_user‎]] - MediaWiki table with user ID, name and other user-specific information. The version in publicly available backups has been cleansed of all private data.
-* [[Schema:mw_user‎_groups]] - MediWiki table which determines which Wiki group (editors, moderators/sysops or bureaucrats) each user belongs to.
 * [[Schema:notes]] - General-purpose notes area.
+* [[Schema:recognized_domains]] - Internet domain names recognized by the ISFDB.
 * [[Schema:submissions]] - Data submissions.
+* [[Schema:webpages]] - URLs of other Web sites. Used by authors, publishers, publication series, titles, series, award types, award categories and publications.
+== User Tables==
 * [[Schema:user_languages‎]] - User Language preferences.
 * [[Schema:user_preferences]] - User preferences.
 * [[Schema:user_sites]] - User Website preferences.
-* [[Schema:webpages]] - URLs of other Web sites. Used by authors, publishers, publication series, titles, series, award categories and award types.
+* [[Schema:changed_verified_pubs]] - Changes to primary-verified publications
-* [[Schema:websites]] -  Third party (bookseller and bibliographic) sites that a publication may link to based on its ISBN.
+* [[Schema:user_status]] - User status information
+== MediaWiki Tables==
+The ISFDB uses MediaWiki software to run its Wiki. See [https://upload.wikimedia.org/wikipedia/commons/f/f7/MediaWiki_1.24.1_database_schema.svg this MediaWiki chart for lists of table fields]. The following MediaWiki pages are also used by the ISFDB software:
+* '''mw_page''' - Information about all Wiki pages. Used by the nightly cleanup reports to find Wiki-ISFDB mismatches.
+* '''mw_user‎''' - User ID, name and other user-specific information. The version in publicly available backups has been cleansed of all private data.
+* '''mw_user‎_group''' - Includes the Wiki group (editors, moderators/sysops or bureaucrats) to which each user belongs.
 ==Author-Related Tables==
@@ Line 36: / Line 55: @@
 * [[Schema:pseudonyms]] - Table of pseudonyms.
 * [[Schema:pub_authors]] - Links author records to publication records.
+* [[Schema:author_views]] - Contains statistical data about the number of times author records have been viewed.
 ==Publication-Related Tables==
@@ Line 41: / Line 61: @@
 The following tables are used to store information about publications:
+* [[Schema:identifier_types]] - Contains information on external identifier types.
+* [[Schema:identifier_sites]] - Contains information on Web sites for external identifier types.
+* [[Schema:identifiers]] - Contains information on external identifiers.
 * [[Schema:magazine]] - Contains magazine information. '''Unused'''.
+* [[Schema:primary_verifications]] - Contains primary verification information for a specific publication.
 * [[Schema:pub_content]] - Links title records to a specific publication.
 * [[Schema:pub_series]] - Contains information on a publication series.
 * [[Schema:publishers]] - Contains publisher information.
 * [[Schema:pubs]] - Contains information on a specific publication.
-* [[Schema:reference]] - Contains information on verification references.
+* [[Schema:reference]] - Contains information on secondary verification references.
-* [[Schema:verification]] - Contains verification information for a specific publication.
+* [[Schema:verification]] - Contains secondary verification information for a specific publication.
+* [[Schema:websites]] -  Third party (bookseller and bibliographic) sites that a publication may link to based on its ISBN.
 ==Title-Related Tables==
@@ Line 59: / Line 84: @@
 * [[Schema:titles]] - Contains information about a specific title.
 * [[Schema:votes]] - Tracks votes for a specific title per user.
+* [[Schema:title_views]] - Contains statistical information about the number of times each title's record has been viewed by ISFDB users.
 ==Awards-Related Tables==
@@ Line 75: / Line 101: @@
 * [[Schema:trans_pub_series]] - Transliterated names for publication series
 * [[Schema:trans_publisher]] - Transliterated names for publishers
-* [[Schema:trans_puba]] - Transliterated titles for publications
+* [[Schema:trans_pubs]] - Transliterated titles for publications
+* [[Schema:trans_series]] - Transliterated names for regular (title) series
 * [[Schema:trans_titles]] - Transliterated titles for title records
-==Data Cleanup Tables==
+== Data Cleanup and Statistics Tables ==
 * [[Schema:bad_images]] - Publication IDs with suspect images
 * [[Schema:cleanup]] - Record IDs in need of review and potential cleanup
-* [[Schema:missing_author_urls]] - Author mismatches between ISFDB and other sites (SFE3 only at this time)
+* [[Schema:sfe3_authors]] - URLs of SFE3 articles about people that are not linked from ISFDB author records
+* [[Schema:most_reviewed]] - Most-reviewed titles by year and decade
+* [[Schema:award_titles_report]] - Titles with most awards and nominations by year, decade and title type