Table: `ndb.aggregatesampleages`#

Description#

This table stores the links to the ages of samples in an Aggregate Dataset. The table is necessary because samples may be from Collection Units with multiple chronologies, and this table stores the links to the sample ages desired for the Aggregate Dataset.

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 the links to the ages of samples in an Aggregate Dataset. The table is necessary because samples may be from Collection Units with multiple chronologies, and this table stores the links to the sample ages desired for the Aggregate Dataset.

Statistics#

Metric	Value
Row Count	369
Total Size	56 kB
Table Size	24 kB
Indexes Size	32 kB

Relationships#

Primary Key: aggregatedatasetid, aggregatechronid, sampleageid

Foreign Keys:

aggregatechronid → aggregatechronologies.aggregatechronid
aggregatedatasetid → aggregatedatasets.aggregatedatasetid
sampleageid → sampleages.sampleageid

Referenced By:

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

Data Dictionary#

Column	Type	Nullable	Default	Constraints	Description
`aggregatedatasetid`	integer	✗	`-`	PRIMARY KEY, FOREIGN KEY	Aggregate Dataset identification number. Field links to the AggregateDatasets table.
`aggregatechronid`	integer	✗	`-`	PRIMARY KEY, FOREIGN KEY	Aggregate Chronology identification number Field links to the AggregateChronologies table.
`sampleageid`	integer	✗	`-`	PRIMARY KEY, FOREIGN KEY	Sample Age ID number. Field links to the SampleAges table.
`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 aggregatesampleages
SELECT *
FROM aggregatesampleages
ORDER BY aggregatedatasetid DESC
LIMIT 10;

Purpose: Retrieve the 10 most recent records from aggregatesampleages

Example 2: Count Records#

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

Purpose: Get the total number of records in aggregatesampleages

Example 3: Filter by Date Range#

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

Purpose: Retrieve records from aggregatesampleages within a specific date range

Example 4: Join with aggregatechronologies#

-- Join with related table
SELECT 
    t1.*,
    t2.*
FROM aggregatesampleages t1
INNER JOIN aggregatechronologies t2 
    ON t1.aggregatechronid = t2.aggregatechronid
LIMIT 100;

Purpose: Retrieve aggregatesampleages records with related data from aggregatechronologies

Example 5: Aggregate Data#

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

Purpose: Count records grouped by aggregatechronid

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