Current Schema: Difference between revisions

From GCD
Jump to navigation Jump to search
(Finished documenting the issues table.)
(Checkpointing work on stories table.)
Line 293: Line 293:
|-
|-
|}
|}




Line 529: Line 530:
| Not Used
| Not Used
| Makes even less sense that the rest of these.
| Makes even less sense that the rest of these.
|-
|}
{| border="1"
|+ '''stories''' ['''Story''' class in new site]
! DB Column
! Name in New Code
! Description
! Usage Notes
|-
! colspan="4" | Core story data fields:
|-
| ID
| id
| DB-generated primary key.
| Since there is no story page, this field does not show up in any URLs in the Lasso implementation.
|-
| Seq_No
| sequence_number
| Order of the story within the issue.
| By convention, sequence 0 is the front cover, and some of its fields are considered to apply to the whole issue.
|-
| Feature
| feature
| The name of the feature, if any.
| Often but not always the name of the primary character.  Some stories do not have a feature.
|-
| Pg_Cnt
| page_count
| Number of story pages.
| Does not include ad pages that might be mixed in.
|-
| Notes
| notes
|
|
|-
| Reprints
| reprints
|
|
|-
| Type
| type
|
|
|-
! colspan="4" | Fields for creator credits:
|-
| Script
| script
|
|
|-
| Pencils
| pencils
|
|
|-
| Inks
| inks
|
|
|-
| Colors
| colors
|
|
|-
| Letters
| letters
|
|
|-
| Editor
| editor
|
| Rarely used, as the editor for the book is typically assumed to have edited the whole book.  However, this field can be used if individual stories have separate editors.
|-
| JobNo
| job_number
| Publisher-assigned job number for the story.
|
|-
! colspan="4" | Fields related to content:
|-
| Title
| title
|
|
|-
| Char_App
| characters
|
|
|-
| Synopsis
| synopsis
|
|
|-
| Genre
| genre
|
|
|-
|-
|}
|}

Revision as of 02:16, 29 March 2008

This wiki page attempts to document the current schema for the GCD. The public schema consists of four principle data tables as well as several supporting tables. The supporting tables are Countries, Languages (lists of codes for each of those things), Indexers (a list of indexers, but not of full account information) and IndexCredit (maps indexers to series for credit, but there are also credit notes int he series table). The four main tables are described below.

The "Name in New Code" column is for those who might be looking at the new Python/Django implementation. Since the existing table and column names are not even remotely consistent in capitalization, formatting or abbreviation, they were standardized in the Django model classes to make working with them easier (Django provides a convenient name mapping system for exactly this purpose).

Please note that much of the issue and story table information on this page is based on earlier documentation by Peter Croome.

publishers [Publisher class in new site]
DB Column Name in New Code Description Usage Notes
Core publisher data fields:
ID id DB-generated primary key. In the lasso-based site, this is the number you see in URLs like http://www.comics.org/publisher_details.lasso?id=4066 It is generated by the database and has no inherent meaning.
PubName name Name of the publisher, imprint, or set of imprints Because imprints are not stored per-issue, in some cases a semicolon-separated list of imprints corresponding to all imprints used for a given series has been given an entry in this table. Also, not all things that we term "Publishers" or "Imprints" strictly fall into those categories, and we do not accurately reflect the evolution of company names over time (for instance, "DC" was not until fairly recently the official name of the company, although it was long an informal name. It is still not exactly correct- "DC Comics" appears to be the current legal name. But "DC" is what everyone knows, so that is what appears in the database).
YearBegan year_began Year (by cover date) of first publication.
YearEnded year_ended Year (by cover date) of last publication, if any.
CountryID country The country from which the publisher operates. Foreign key into the Countries table.
Notes notes Space for arbitrary notes.
web url The URL for the publisher's website. While this column sometimes has data, it does not appear to be used by the Lasso-based site.
AlphaSortCode alpha_sort_code A single character. Appears to be used to sort related publishers near each other even if the more obvious alphabetical sort would not do so. It's a touch unclear.
Publisher / imprint relations and publication counts:
ImprintCount imprint_count Count of how many imprints a given publisher has. Does not appear to be uniformly accurate.
BookCount series_count Count of how many series this publisher publishes. Does not appear to be uniformly accurate.
IssueCount issue_count Total number of issues in all series published by the publisher. Does not appear to be uniformly accurate.
Master is_master Boolean field. Appears to indicate if something is a publisher or an imprint. Or more generally, a first-class publishing entity or one that is somehow part of a larger entity (because it was bought long ago, or is part of a group of companies commonly referred to by another name, etc). The new site mostly ignores this in favor of checking for rows with a matching parent id.
Connection connection No clue. It's a varchar, though. Perhaps it's supposed to explain the nature of each publisher-to-publisher relationship? Always null or in one case an empty string in the current data dump, so pretty clearly not used.
ParentID parent Self-join key for parent/child relationships. This is how the imprints for a publisher are stored.
NextID next Self-join key, purpose unknown. Does not appear to be in use.
Change tracking fields:
created created Row creation timestamp.
Modified modified One of two change timestamps. No idea which one the Lasso site actually uses.
Updated updated One of two change timestamps. No idea which one the Lasso site actually uses.


series [Series class in new site]
DB Column Name in New Code Description Usage Notes
Core series data fields:
ID id DB-generated primary key. In the lasso-based site, this is the number you see in URLs like http://www.comics.org/series.lasso?seriesid=72 It is generated by the database and has no inherent meaning.
Bk_Name name Series title.
Format format Physical format of the series (size, color presence or absence, paper stock, etc.). Since format is really an attribute of the issue rather than the whole series, this field may contain a fairly long description of how the format changed over the life of the series.
Notes notes Space for arbitrary notes.
Yr_Began year_began First year (by cover date) of publication.
Yr_Ended year_ended Last year (by cover date) of publication, if any.
PubDates publication_dates First and last full cover publication dates of the series, separated by a '-'. No last date if still ongoing.
Pub_Note publication_note Arbitrary publication / publisher-related notes. Some records in the current DB include addresses, imprint information and/or general reprint information here. Does not appear to be used by the Lasso implementation.
Frst_Iss first_issue Issue # (or other identifier) of the first issue in the series. Note that it's not currently reliably possible to determine this from the issue table.
Last_Iss last_issue Issue # (or other identifier) of the last issue in the series, if any. Note that it's not currently reliably possible to determine this from the issue table.
Issuecount issue_count Number of issues associated with the series. Does not appear to be reliably accurate.
PubID publisher Foreign key into the publishers table.
Pub_Name publisher_name Copied from publishers table, presumably to save a join. Reliability of this field has not been verified.
CounCode country_code Inexplicably not a foreign key into the Country table. Instead the country code is duplicated here, and does not necessarily match properly.
LangCode language_code Inexplicably not a foreign key into the Languages table. Instead the language code is duplicated here, and does not necessarily match properly.
Fields relating series to each other (continuations, etc.):
Tracking tracking_notes Notes about how a given series is continued in or continued from another series.
Crossref crossref Appears to be a boolean field flagging if there is a crossreference. Does not appear to be used by the Lasso implementation. Only one record where this is not NULL or 0.
CrossrefID crossref_id Apparently a self-join key for the cross-referenced series. Does not appear to be used by the Lasso implementation. Only one record where this is not NULL or 0.
Field related to the (cover?) gallery:
HasGallery gallery_present 3-character field, but used as boolean. If true, there is a cover gallery. I think.
Included gallery_includes varchar field, seems to be comma-separated list (with hyphens for ranges) of issues included in something? I speculate the gallery, but it's not clear how this is used, if at all, by the current implementation. It is not the string that shows up in the "Issues published:" space on the series page.
Fields related to indexing:
Indexers indexers Semicolon-separated string of indexers. This does not appear to be quite what gets displayed in the credits on the page in the Lasso site. The exact relationship between this and the IndexCredit table is unclear.
OpenReserve open_reserve Integer field. Presumably something to do with online indexing, but since reservations are per-issue, it's not clear quite what.
Fields apparently related to flat file management / distribution. I think.
File file Presumably the name of the file used for flat-file indexing. Or possibly distribution.
InitDist initial_distribution Presumably something to do with flat-file distribution of the data.
UpdateDist update_distribution Presumably something to do with flat-file distribution of the data.
Change tracking fields:
Created created Row creation timestamp.
Modified modified Date of last row change.
ModTime modification_time Time of last row change (24 hour).
LstChang last_change Date of some sort, but apparently unused. Only five non-NULL, non-zero values in the current data, all in the future (nearest is in 2010).
oldID old_id Not clear. Presumably the ID from a prior organization of the data? Does not appear to be in active use currently.
Miscellaneous unused and/or mysterious fields:
Themes themes Does not appear to be used (always NULL).
SelfCount self_count Absolutely no clue.


issues [Issue class in new site]
DB Column Name in New Code Description Usage Notes
Core issue data fields:
ID id DB-generated primary key. In the lasso-based site, this is the number you see in URLs like http://www.comics.org/details.lasso?id=293 It is generated by the database and has no inherent meaning.
VolumeNum volume Volume number from the indicia. Not consistently used, and according to recent editor list comments, not planned for migration into the new schema. Where especially relevant, volume numbers are typically included in the Issue (number) field.
Issue number Issue number or other identifier from the indicia. Use the value nn to indicate "no number" when there is no identifier. For issues where the volume number is important, the form v1#1 is used to specify both the volume and issue numbers. When the cover number or commonly assumed number is different from the indicia number, it goes in brackets after the indicia number: v3#2 [26]. The same format is used for disambiguation of identically numbered issues: 1 [1946] or nn [1947].
Pub_Date publication_date Publication date of the issue as given in the indicia, but fully spelled out. Issues that list a publication date of December-January, Winter or Holiday should specify both years involved, i.e. Winter 1942-1943.
Price price ISO code followed by decimal price, i.e. USD 0.10 Most documentation of this field is out of date. ISO code followed by a decimal number is the correct format, contrary to what other pages might say, as per recent (early 2008) discussions on the editor list.
storycount story_count Number of stories linked to this issue. Not 100% convinced this is always accurate.
Key_Date key_date Specially formatted date string that (in theory) determines the ordering of issues within a series. In practice, many issues are missing this field, meaning that if their issue numbers are not standard, there is no reliable programmatic way to sort the issues in the series. In practice, the cover sort codes are slightly more reliable (although not always), but they are not included in the public data dumps.
Fields that would be core issue data but aren't actually used:
Pg_Cnt page_count Not Used Page count for the whole issue is taken from story sequence 0 (which otherwise represents the front cover).
Editing editor Not Used Editor(s) for the whole issue are taken from story sequence 0 (which otherwise represents the front cover).
Notes notes Not Used Notes for the whole issue are taken from story sequence 0 (which otherwise represents the front cover).
Fields related to indexing:
IndexStatus index_status Whether or not an issue is being indexed, and where it is in the process. 0=not (white); 1=reserved (red); 2=pending (orange); 3=approved (green). Colors noted are how the issues appear in various index status grids.
ReserveCheck reserve_check Not Used The intention of this field is unknown.
ReserveStatus reserve_status 1=indexed, pending or reserved; 0=not reserved The intended relationship between this and IndexStatus/index_status is not known.
Fields related to cover images:
CoverCheck has_cover Not Used
CoverCount num_covers Not Used
Fields related to the series, including unused cache fields:
SeriesID series Foreign key into the series table.
Bk_Name series_name Not Used
Yr_Began year_began Not Used
Pub_Name publisher_name Not Used
Fields apparently related to flat file management / distribution. I think.
InitDist initial_distribution Not Used
UpdateDist update_distribution Not Used
isUpdated is_updated Not Used (? uncertain)
Change tracking fields:
created created Row creation timestamp.
Modified modified Date of last row change.
ModTime modification_time Time of last row change (24 hour).
LstChang last_change Date of some sort, but apparently unused? Column has numerous values, but they do not line up with the values in the Modified column.
Miscellaneous unused and/or mysterious fields:
rel_year rel_year Not Used I couldn't even guess this one well enough to come up with a more clear name in the code. release_year? related_year?
SelfCount self_count Not Used
SeriesLink series_link Not Used
Unused fields that would apply only to the cover (as far as I can tell), as opposed to applying to the whole issue:
Feature cover_feature Not Used
Char_App cover_characters Not Used
Script cover_script Not Used
Pencils cover_pencils Not Used
Inks cover_inks Not Used
Colors cover_colors Not Used
Letters cover_letters Not Used
Synopsis cover_synopsis Not Used Yes, I realize cover_synopsis does not make a whole lot of sense. It's not used anyway.
Reprints cover_reprints Not Used Arguably this could go in the "whole issue" section and be used to indicates issues that reprint entire other issues instead of doing story-by-story reprint links.
Title cover_title Not Used Arguably this could go in the "whole issue" section.
Genre cover_genre Not Used Arguably this could go in the "whole issue" section.
Type cover_type Not Used Since the type of a cover is "cover", this also doesn't make much sense. But it doesn't make sense as applied to an entire issue either, as far as I can tell.
Seq_No cover_sequence_number Not Used Makes even less sense that the rest of these.


stories [Story class in new site]
DB Column Name in New Code Description Usage Notes
Core story data fields:
ID id DB-generated primary key. Since there is no story page, this field does not show up in any URLs in the Lasso implementation.
Seq_No sequence_number Order of the story within the issue. By convention, sequence 0 is the front cover, and some of its fields are considered to apply to the whole issue.
Feature feature The name of the feature, if any. Often but not always the name of the primary character. Some stories do not have a feature.
Pg_Cnt page_count Number of story pages. Does not include ad pages that might be mixed in.
Notes notes
Reprints reprints
Type type
Fields for creator credits:
Script script
Pencils pencils
Inks inks
Colors colors
Letters letters
Editor editor Rarely used, as the editor for the book is typically assumed to have edited the whole book. However, this field can be used if individual stories have separate editors.
JobNo job_number Publisher-assigned job number for the story.
Fields related to content:
Title title
Char_App characters
Synopsis synopsis
Genre genre