Table: `ndb.collectiontypes`#

Description#

This table is a lookup table for types of Collection Units, or Collection Types. Table is referenced by the CollectionUnits table.

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 is a lookup table for types of Collection Units, or Collection Types. Table is referenced by the CollectionUnits table.

Statistics#

Metric	Value
Row Count	11
Total Size	24 kB
Table Size	8192 bytes
Indexes Size	16 kB

Relationships#

Primary Key: colltypeid

No foreign key relationships.

Referenced By:

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

Data Dictionary#

Column	Type	Nullable	Default	Constraints	Description
`colltypeid`	integer	✗	`nextval('ndb.seq_collection...`	PRIMARY KEY	An arbitrary Collection Type identification number.
`colltype`	character varying(64)	✓	`-`	-	The Collection Type. Types include cores, sections, excavations, and animal middens. Collection Units may be modern collections, surface float, or isolated specimens. Composite Collections Units include different kinds of Analysis Units, for example a modern surface sample for ostracodes and an associated water sample.
`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 collectiontypes
SELECT *
FROM collectiontypes
ORDER BY colltypeid DESC
LIMIT 10;

Purpose: Retrieve the 10 most recent records from collectiontypes

Example 2: Count Records#

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

Purpose: Get the total number of records in collectiontypes

Example 3: Filter by Date Range#

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

Purpose: Retrieve records from collectiontypes within a specific date range

Example 4: Aggregate Data#

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

Purpose: Count records grouped by colltype

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