Table: `ndb.contacts`#

Description#

This table lists persons and organizations referenced by the Chronologies, Collectors, DatasetPIs, DatasetSubmissions, Projects, PublicationAuthors, SampleAnalysts, and SiteImages tables.

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 lists persons and organizations referenced by the Chronologies, Collectors, DatasetPIs, DatasetSubmissions, Projects, PublicationAuthors, SampleAnalysts, and SiteImages tables.

Statistics#

Metric	Value
Row Count	14,650
Total Size	3944 kB
Table Size	2416 kB
Indexes Size	1496 kB

Relationships#

Primary Key: contactid

Foreign Keys:

contactstatusid → contactstatuses.contactstatusid

Referenced By:

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

Data Dictionary#

Column	Type	Nullable	Default	Constraints	Description
`contactid`	integer	✗	`nextval('ndb.seq_contacts_c...`	PRIMARY KEY	An arbitrary Contact identification number.
`aliasid`	integer	✓	`-`	-	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`	character varying(80)	✗	`-`	-	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`	integer	✓	`-`	FOREIGN KEY	Current status of the person, organization, or project. Field links to the ContactStatuses lookup table.
`familyname`	character varying(80)	✓	`-`	-	Family or surname name of a person.
`leadinginitials`	character varying(16)	✓	`-`	-	Leading initials for given or forenames without spaces (e.g. «G.G.»).
`givennames`	character varying(80)	✓	`-`	-	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`	character varying(16)	✓	`-`	-	Suffix of a person’s name (e.g. «Jr.», «III»).
`title`	character varying(16)	✓	`-`	-	A person’s title (e.g. «Dr.», «Prof.», «Prof. Dr»).
`phone`	character varying(64)	✓	`-`	-	Telephone number.
`fax`	character varying(64)	✓	`-`	-	Fax number.
`email`	character varying(64)	✓	`-`	-	Email address.
`url`	character varying(255)	✓	`-`	-	Universal Resource Locator, an Internet World Wide Web address.
`address`	text	✓	`-`	-	Full mailing address.
`notes`	text	✓	`-`	-	Free form notes or comments about the person, organization, or project.
`recdatecreated`	timestamp without time zone	✗	`timezone('UTC'::text, now())`	-
`recdatemodified`	timestamp without time zone	✗	`-`	-

TODO: Review column descriptions and add comments where missing.

Usage Examples#

Example 1: Basic Selection#

-- Get recent records from contacts
SELECT *
FROM contacts
ORDER BY contactid DESC
LIMIT 10;

Purpose: Retrieve the 10 most recent records from contacts

Example 2: Count Records#

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

Purpose: Get the total number of records in contacts

Example 3: Filter by Date Range#

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

Purpose: Retrieve records from contacts within a specific date range

Example 4: Join with contactstatuses#

-- Join with related table
SELECT 
    t1.*,
    t2.*
FROM contacts t1
INNER JOIN contactstatuses t2 
    ON t1.contactstatusid = t2.contactstatusid
LIMIT 100;

Purpose: Retrieve contacts records with related data from contactstatuses

Example 5: Aggregate Data#

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

Purpose: Count records grouped by aliasid

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.contacts#