10 Contact and Individual Related Tables

10.1 collectors

The collectors table lists the people who were responsible for collecting a particular collection unit. This is part of the complete set of individuals who collect, analyze, publish and process the data for contribution to Neotoma. Other related tables include publicationsauthors, sampleanalysts, and datasetsubmissions.

• collectorid (primary key): An arbitrary Collector identification number.
• collectionunitid (foreign key)]: The collectionunitid for the record that was collected. Field links to the collectionunits table.
• contactid (foreign key): Person who collected the CollectionUnit. Multiple individuals are listed in separate records. Field links to the contacts table.
• collectororder: Order in which the collectors should be listed.

10.1.1 SQL Example

We want to see the top contributors for each constitutent database, so we calculate the sum of each appearance of a contributor/database, and then return the COUNT(*). The DISTINCT ON pattern allows us to select a single instance of the field db.databasename, that has been ordered by the descending COUNT(*) per individual, and database. This then ensures we have the top collectionunit collector for each constituent database.

SELECT DISTINCT ON (db.databasename)
       ct.contactname, 
       db.databasename,
       COUNT(*)
FROM ndb.contacts AS ct
INNER JOIN ndb.collectors AS co ON co.contactid = ct.contactid
INNER JOIN ndb.collectionunits AS cu ON cu.collectionunitid = co.collectionunitid
INNER JOIN ndb.datasets AS ds ON ds.collectionunitid = cu.collectionunitid
INNER JOIN ndb.datasetdatabases AS dsdb ON dsdb.datasetid = ds.datasetid
INNER JOIN ndb.constituentdatabases AS db ON db.databaseid = dsdb.databaseid
GROUP BY db.databasename, ct.contactname
ORDER BY db.databasename, COUNT(*) DESC;

Table 10.1: Displaying records 1 - 10

contactname	databasename	count
USEPA and Collaborators	Academy of Natural Sciences of Drexel University	2546
Scott, Louis	African Pollen Database	71
West, Dixie L.	Alaskan Archaeofaunas	22
Tinner, Willy	Alpine Palynological Database	153
Delorme, Denis	Canadian Museum of Nature-Delorme Ostracoda-Surface Samples	9024
Li, Jianyong	Chinese Pollen Database	3
Revelles-López, Jordi	Deep-Time Palynology Database	1
Wise, Robert	Diatom Paleolimnology Data Cooperative (DPDC)	4952
Visset, Lionel	European Pollen Database	159
Cannon, Aubrey	Faunal Isotope Database	10

10.2 contacts

This table lists persons and organizations. The table is referenced through Foreign Keys in the following tables: * Chronologies, * Collectors, * DatasetPIs, * DatasetSubmissions, * Projects, * PublicationAuthors, * SampleAnalysts, and * SiteImages tables.

• contactid (primary key): An arbitrary contactid number.
• aliasid (foreign key): The contactid of a person’s current name. If the aliasid is different from the contactid, the contactid refers to the person’s former name. For example, if J. L. Bouvier became J. B. Kennedy, the contactid for J. B. Kennedy is the aliasid for J. L. Bouvier.
• contactname: Full name of the person, last name first (e.g. Simpson, George Gaylord) or name of organization or project (e.g. Great Plains Flora Association).
• contactstatusid (foreign key): Current status of the person, organization, or project. Field links to the contactstatuses lookup table.
• familyname: Family or surname name of a person.
• leadinginitials**: leading initials for given or forenames **without spaces (e.g. G.G.).
• givennames: Given or forenames of a person (e.g. George Gaylord). Initials with spaces are used if full given names are not known (e.g. G. G).
• suffix: Suffix of a person’s name (e.g. «Jr.», «III»).
• title: A person’s title (e.g. «Dr.», «Prof.», «»).
• phone: Telephone number.
• fax: Fax number.
• email: Email address.
• url: Universal Resource Locator, an Internet World Wide Web address.
• address: Full mailing address.
• notes: Free form notes or comments about the person, organization, or project.

10.3 contactstatuses

Lookup table of Contact Statuses. Table is referenced by the contacts table.

• contactstatusid (primary key): An arbitrary Contact Status identification number.
• contactstatus: Status of person, organization, or project.
• statusdescription:
  • Description of the status. The following statuses exist (with descriptions):
    • active: Person, project, or organization is active in the field
    • deceased: Person is deceased
    • defunct: Project or organization is defunct or non-operational
    • extant: Project or organization is extant
    • inactive: Person is inactive in the field
    • retired: Person is retired
    • unknown: Status is unknown