Difference between revisions of "Database Schema"
(→Transliteration Tables: +Data cleanup tables section) |
(Updated the description of the webpages table) |
||
Line 24: | Line 24: | ||
* [[Schema:user_sites]] - Holds User Website preferences. | * [[Schema:user_sites]] - Holds User Website preferences. | ||
* [[Schema:websites]] - Stores bookseller and bibliographic sites that a publication may link to. | * [[Schema:websites]] - Stores bookseller and bibliographic sites that a publication may link to. | ||
+ | * [[Schema:webpages]] - URLs of other Web sites. Used by authors, publishers, publication series, titles, series, award categories and award types. | ||
==Author-Related Tables== | ==Author-Related Tables== | ||
Line 34: | Line 35: | ||
* [[Schema:pseudonyms]] - Table of pseudonyms. | * [[Schema:pseudonyms]] - Table of pseudonyms. | ||
* [[Schema:pub_authors]] - Links author records to publication records. | * [[Schema:pub_authors]] - Links author records to publication records. | ||
− | |||
==Publication-Related Tables== | ==Publication-Related Tables== |
Revision as of 17:07, 3 September 2016
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. 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 here.
Global Tables
Information stored in the global tables are shared across the title, author, and publication related tables in the ISFDB.
- Schema:directory - Holds author directory information.
- Schema:history - Tracks database change history.
- Schema:languages
- Schema:license_keys - Holds user license key information.
- Schema:metadata - Contains metadata about the database itself.
- Schema:mw_user
- Schema:mw_user_groups
- Schema:notes - General-purpose notes area.
- Schema:submissions - Table for storing data submissions.
- Schema:user_languages - Holds User Language preferences.
- Schema:user_preferences - Holds User preferences.
- Schema:user_sites - Holds User Website preferences.
- Schema:websites - Stores bookseller and bibliographic sites that a publication may link to.
- Schema:webpages - URLs of other Web sites. Used by authors, publishers, publication series, titles, series, award categories and award types.
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.
Publication-Related Tables
The following tables are used to store information about publications:
- Schema:magazine - Contains magazine information. Unused.
- 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:verification - Contains verification information for a specific publication.
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.
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_puba - Transliterated titles for publications
- Schema:trans_titles - Transliterated titles for title records
Data Cleanup 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)