Table: `ndb.variableelements`#

Description#

Lookup table of Variable Elements. Table is referenced by the Variables 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: Lookup table of Variable Elements. Table is referenced by the Variables table.

Statistics#

Metric	Value
Row Count	1,018
Total Size	112 kB
Table Size	72 kB
Indexes Size	40 kB

Relationships#

Primary Key: variableelementid

Foreign Keys:

maturityid → elementmaturities.maturityid
portionid → elementportions.portionid
symmetryid → elementsymmetries.symmetryid
elementtypeid → elementtypes.elementtypeid

Referenced By:

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

Data Dictionary#

Column	Type	Nullable	Default	Constraints	Description
`variableelementid`	integer	✗	`nextval('ndb.seq_variableel...`	PRIMARY KEY	An arbitrary Variable Element identification number.
`variableelement`	character varying(255)	✓	`-`	-	The element, part, or organ of the taxon identified. For plants, these include pollen, spores, and various macrofossil organs, such as «seed», «twig», «cone», and «cone bract». Thus, Betula pollen and Betula seeds are two different Variables. For mammals, Elements include the bone or tooth identified, e.g. «tibia». «tibia, distal, left», «M2, lower, left». Some more unusual elements are Neotoma fecal pellets and Erethizon dorsata quills. If no element is indicated for mammalian fauna, then the genric element «bone/tooth» is assigned. Elements were not assigned in FAUNMAP, so all Variables ingested from FAUNMAP were assigned the «bone/tooth» element. Physical Variables may also have elements. For example, the Loss-on-ignition Variables have «Loss-on-ignition» as a Taxon, and temperature of analysis as an element, e.g. «500°C», «900°C». Charcoal Variables have the size fragments as elements, e.g. «75-100 µm», «100-125 µm».
`elementtypeid`	integer	✓	`-`	FOREIGN KEY
`symmetryid`	integer	✓	`-`	FOREIGN KEY
`portionid`	integer	✓	`-`	FOREIGN KEY
`maturityid`	integer	✓	`-`	FOREIGN KEY
`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 variableelements
SELECT *
FROM variableelements
ORDER BY variableelementid DESC
LIMIT 10;

Purpose: Retrieve the 10 most recent records from variableelements

Example 2: Count Records#

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

Purpose: Get the total number of records in variableelements

Example 3: Filter by Date Range#

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

Purpose: Retrieve records from variableelements within a specific date range

Example 4: Join with elementmaturities#

-- Join with related table
SELECT 
    t1.*,
    t2.*
FROM variableelements t1
INNER JOIN elementmaturities t2 
    ON t1.maturityid = t2.maturityid
LIMIT 100;

Purpose: Retrieve variableelements records with related data from elementmaturities

Example 5: Aggregate Data#

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

Purpose: Count records grouped by variableelement

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

Data Quality Notes#

Automated Data Quality Tests#

This table is subject to the following automated quality checks:

❌ bix_003: variable_elements_in_use

Severity: WARNING
Status: FAILED
Description: Over time a number of variable contexts, units and elements have been created but not neccessarily used. In some cases this may have resulted from improperly entered data in the Tilia spreadsheet.
Suggested Remediation: - Where possible, remove unused units/elements/contexts - Ensure any near-duplicates of existing units/elements/contexts are using best-practice or accepted notations.

See the Data Quality Report for details.

Maintenance#

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

Table: ndb.variableelements#