Table: `ndb.publications`#

Description#

This table stores publication or bibliographic data. The table is designed with fields for bibliographic data so that bibliographies can be formatted in different styles and potentially exported to bibliographic software such EndNote®. In the constituent databases that were originally merged into Neotoma, bibliographic entries were not parsed into separate fields, but rather were stored as free-form text. Because complete parsing of these thousands of legacy bibliographic entries into individual fields would have been prohibitively time consuming, the existing bibliographic data were ingested “as is” with a PubTypeID = Other. However, for legacy publications, the year of publication was added to the Year field, and authors were parsed into the PublicationAuthors table and added to the Contacts table. In addition, some global changes were made. For example, «Pp.» was changed to «Pages», «Ed.» to «Editor», and «Eds.» to «Editors». Also for FAUNMAP entries, abbreviated journal names were changed to fully spelled out names. The merged databases used different bibliographic styles, and data entry personnel working on the same database sometimes followed different conventions. Consequently, the current bibliographic entries are not stylistically uniform. Eventually, the legacy bibliographic data will be parsed into separate fields. The Publications table has fields to accommodate a number of different types of publications. Some fields contain different kinds of data for different kinds of publications. For example, the BookTitle field stores the titles of books, but stores the journal name for journal articles. The Publisher field stores the name of the publisher for books, but the name of the university for theses and dissertations. Authors are stored in the PublicationAuthors table. Editors are also stored in the PublicationAuthors table if the entire publication is cited. The PublicationAuthors table has a ContactID field, which links to the Contacts table, where full names and contact information is stored for authors and editors. The PubTypeID «Authored Book» or «Edited Book» indicates whether the Publication Authors records are authors or editors. If a book chapter or section is the primary bibliographic entry, then the book editors are stored in the PublicationEditors table, which does not have a ContactID field.

TODO: Expand this description with: - What data does this table store? - What is the business/research purpose? - How is this data collected or generated? - Are there any important caveats or data quality issues?

Table Structure#

Schema: ndb | Table Comment: This table stores publication or bibliographic data. The table is designed with fields for bibliographic data so that bibliographies can be formatted in different styles and potentially exported to bibliographic software such EndNote®. In the constituent databases that were originally merged into Neotoma, bibliographic entries were not parsed into separate fields, but rather were stored as free-form text. Because complete parsing of these thousands of legacy bibliographic entries into individual fields would have been prohibitively time consuming, the existing bibliographic data were ingested “as is” with a PubTypeID = Other. However, for legacy publications, the year of publication was added to the Year field, and authors were parsed into the PublicationAuthors table and added to the Contacts table. In addition, some global changes were made. For example, «Pp.» was changed to «Pages», «Ed.» to «Editor», and «Eds.» to «Editors». Also for FAUNMAP entries, abbreviated journal names were changed to fully spelled out names. The merged databases used different bibliographic styles, and data entry personnel working on the same database sometimes followed different conventions. Consequently, the current bibliographic entries are not stylistically uniform. Eventually, the legacy bibliographic data will be parsed into separate fields. The Publications table has fields to accommodate a number of different types of publications. Some fields contain different kinds of data for different kinds of publications. For example, the BookTitle field stores the titles of books, but stores the journal name for journal articles. The Publisher field stores the name of the publisher for books, but the name of the university for theses and dissertations. Authors are stored in the PublicationAuthors table. Editors are also stored in the PublicationAuthors table if the entire publication is cited. The PublicationAuthors table has a ContactID field, which links to the Contacts table, where full names and contact information is stored for authors and editors. The PubTypeID «Authored Book» or «Edited Book» indicates whether the Publication Authors records are authors or editors. If a book chapter or section is the primary bibliographic entry, then the book editors are stored in the PublicationEditors table, which does not have a ContactID field.

Statistics#

Metric	Value
Row Count	15,353
Total Size	26 MB
Table Size	8320 kB
Indexes Size	392 kB

Relationships#

Primary Key: publicationid

Foreign Keys:

pubtypeid → publicationtypes.pubtypeid

Referenced By:

TODO: Document which tables reference this table (will be auto-detected in validation).

Data Dictionary#

Column	Type	Nullable	Default	Constraints	Description
`publicationid`	integer	✗	`nextval('ndb.seq_publicatio...`	PRIMARY KEY	An arbitrary Publication identification number.
`pubtypeid`	integer	✓	`-`	FOREIGN KEY	Publication type. Field links to the PublicationTypes lookup table.
`year`	character varying(64)	✓	`-`	-	Year of publication.
`citation`	text	✓	`-`	-	The complete citation in a standard style. For Legacy citations inherited from other databases, this field holds the citation as ingested from the other databases
`articletitle`	text	✓	`-`	-	The title of a journal or book chapter article.
`journal`	text	✓	`-`	-
`volume`	character varying(16)	✓	`-`	-	The volume number of a journal or the volume number of a book in a set. A set of books is comprised of a fixed number of volumes and normally have ISBN numbers, not ISSN numbers. Book sets are often published simultaneously, but not necessarily. For instance, many floras, such as The Flora of North America north of Mexico and Flora Europaea, consist of a set number of volumes planned in advance but published over a period of years.
`issue`	character varying(8)	✓	`-`	-	Journal issue number, normally included only if issues are independently paginated.
`pages`	character varying(24)	✓	`-`	-	Page numbers for journal or book chapter articles, or the number of pages in theses, dissertations, and reports.
`citationnumber`	character varying(24)	✓	`-`	-	A citation or article number used in lieu of page numbers for digital or online publications, typically used in conjunction with the DOI. For example, journals published by the American Geophysical Union since 1999 use citation numbers rather than page numbers.
`doi`	character varying(128)	✓	`-`	-	Digital Object Identifier. A unique identifier assigned to digital publications. The DOI consists of a prefix and suffix separated by a slash. The portion before the slash stands for the publisher and is assigned by the International DOI Foundation. For example, 10.1029 is the prefix for the American Geophysical Union. The suffix is assigned by the publisher according to their protocols. For example, the DOI 10.1029/2002PA000768 is for an article submitted to Paleoceanography in 2002 and is article number 768 submitted since the system was installed. An example of CitationNumber and DOI:
Barron, J. A., L. Heusser, T. Herbert, and M. Lyle. 2003. High-resolution climatic evolution of coastal northern California during the past 16,000 years, Paleoceanography 18(1):1020. DOI:10.1029/2002PA000768.

`booktitle`	text	✓	`-`	-	The title of a book or journal
`numvolumes`	character varying(8)	✓	`-`	-	Number of volumes in a set of books. Used when the entire set is referenced. An example of NumVolumes and Edition:
Wilson, D. E., and D. M. Reeder. 2005. Mammal species of the world: a taxonomic and geographic reference. Third edition. 2 volumes. The Johns Hopkins University Press, Baltimore, Maryland, USA.

`edition`	character varying(24)	✓	`-`	-	Edition of a publication.
`volumetitle`	text	✓	`-`	-	Title of a book volume in a set. Used if the individual volume is referenced. Example of Volume and VolumeTitle:
Flora of North America Editorial Committee. 2002. Flora of North America north of Mexico. Volume 26. Magnoliophyta: Liliidae: Liliales and Orchidales. Oxford University Press, New York, New York, USA.

`seriestitle`	text	✓	`-`	-	Title of a book series. Book series consist of a series of books, typically published at irregular intervals on sometimes related but different topics. The number of volumes in a series is typically open ended. Book series are often assigned ISSN numbers as well as ISBN numbers. However, in contrast to most serials, book series have individual titles and authors or editors. Citation practices for book series vary; sometimes they are cited as books, other times as journals. The default citation for Neotoma includes all information. An example of SeriesTitle and SeriesVolume:
Curtis, J. H., and D. A. Hodell. 1993. An isotopic and trace element study of ostracods from Lake Miragoane, Haiti: A 10,500 year record of paleosalinity and paleotemperature changes in the Caribbean. Pages 135-152 in P. K. Swart, K. C. Lohmann, J. McKensie, and S. Savin, editors. Climate change in continental isotopic records. Geophysical Monograph 78. American Geophysical Union, Washington, D.C., USA.

`seriesvolume`	character varying(24)	✓	`-`	-	Volume number in a series.
`publisher`	character varying(255)	✓	`-`	-	Publisher, including commercial publishing houses, university presses, government agencies, and non-governmental organizations, generally the owner of the copyright.
`url`	text	✓	`-`	-
`city`	character varying(64)	✓	`-`	-	City in which the publication was published. The first city if a list is given.
`state`	character varying(64)	✓	`-`	-	State or province in which the publication was published. Used for the United States and Canada, not used for many countries.
`country`	character varying(64)	✓	`-`	-	Country in which the publication was published, generally the complete country name, but «USA» for the United States.
`originallanguage`	character varying(64)	✓	`-`	-	The original language if the publication or bibliographic citation is translated from another language or transliterated from a non-Latin character set. Field not needed for non-translated publications in languages using the Latin character set. In the following example, the ArticleTitle is translated from Russian to English and the BookTitle (journal name) is transliterated from Russian:
Tarasov, P.E. 1991. Late Holocene features of the Kokchetav Highland. Vestnik Moskovskogo Universiteta. Series 5. Geography 6:54-60 [in Russian].

`notes`	text	✓	`-`	-	Free form notes or comments about the publication, which may be added parenthetically to the citation.
`recdatecreated`	timestamp without time zone	✗	`timezone('UTC'::text, now())`	-
`recdatemodified`	timestamp without time zone	✗	`-`	-
`crossref`	jsonb	✓	`-`	-
`bibtex`	character varying	✓	`-`	-

TODO: Review column descriptions and add comments where missing.

Usage Examples#

Example 1: Basic Selection#

-- Get recent records from publications
SELECT *
FROM publications
ORDER BY publicationid DESC
LIMIT 10;

Purpose: Retrieve the 10 most recent records from publications

Example 2: Count Records#

-- Count total records
SELECT COUNT(*) as total_records
FROM publications;

Purpose: Get the total number of records in publications

Example 3: Filter by Date Range#

-- Get records within a date range
SELECT *
FROM publications
WHERE recdatecreated >= '2024-01-01'
  AND recdatecreated < '2025-01-01'
ORDER BY recdatecreated DESC;

Purpose: Retrieve records from publications within a specific date range

Example 4: Join with publicationtypes#

-- Join with related table
SELECT 
    t1.*,
    t2.*
FROM publications t1
INNER JOIN publicationtypes t2 
    ON t1.pubtypeid = t2.pubtypeid
LIMIT 100;

Purpose: Retrieve publications records with related data from publicationtypes

Example 5: Aggregate Data#

-- Aggregate records by pubtypeid
SELECT 
    pubtypeid,
    COUNT(*) as count
FROM publications
GROUP BY pubtypeid
ORDER BY count DESC
LIMIT 10;

Purpose: Count records grouped by pubtypeid

TODO: Add more specific examples relevant to common research questions or operational tasks.

Data Quality Notes#

TODO: Document: - Known data quality issues - Validation rules - Expected data ranges - Update frequency and mechanisms - Any ETL processes that populate this table

Maintenance#

Data Owner: TODO: Assign owner
Update Frequency: TODO: Document frequency
Last Major Schema Change: TODO: Document when schema last changed

Table: ndb.publications#