db-schema-voc.md

EDR voc Schema Documentation

Overview

The voc schema holds tables, views and functions used to provide definitions for schemas and collections of concepts used for controlled vocabularies. (Some tables have a corresponding view that presents the table in a human friendly way, for example by getting a label from a related table, rather than displaying the foreign key UUID. The views have the same name as the table they present but suffixed by '__view', e.g voc.concept__view for voc.concept.)

Figure voc schema tables.

Tables and views

voc.concept [table]

Concepts, or terms, in a defined vocabulary. The language used for label and definition values is set by the concept_scheme.system__language property. After skos:Concept.

Column	Data type	Key	FK References	Null	Default	Definition
id	text	PK		NO		Concept ID.
concept_scheme_id	text			NO		Concept scheme ID
top_concept	boolean			NO	true	Shows whether the concept is a top (root) concept in its concept scheme. Defaults to true as most schemes a simple lists. In a hierarchy narrower concepts are, by definition, not top concepts so the value will be false.
preferred_label	text			NO		The preferred, human-readable, text identifier for the concept. After skos:prefLabel.
alternative_labels	text[]			YES		An array of alternative, human-readable, text identifiers for the concept (synonyms). After skos:altLabel.
notation	text			YES		A code, token or identifier formally used by the schema maintainers to identify and access a concept. E.g. L for the NZSC Allophanic Soils order. After skos:notation.
definition	text			YES		A brief definition of the concept. Content may be formatted using Markdown syntax. More detailed descriptions should be provided via the resources at the related concept_scheme.source and concept_scheme.see_also links. After skos:definition.
editorial_note	text			YES		Notes on issues with the concept and any changes that may be required. After skos:editorialNote.
system__language	text			NO	'en'	An IETF language tag for the language used for translation. Directs the client to use a specific language for a multi-language scheme.
system__order	integer			YES		Integer value marking the member's position in a ordered concept scheme.

Index	Type	Columns
fx_concept__concept_scheme	btree	concept_scheme_id
pk_concept	unique; btree	id

voc.concept__quantity [table]

Extends voc.concept to provide the applicable numeric values for concepts that actually describe a numeric range. E.g. an abundance or size fraction vocabulary. The unit of measure used is as defined in the source vocabulary.

Column	Data type	Key	FK References	Null	Default	Definition
id	uuid	PK		NO	gen_random_uuid()	Concept quantity UUID.
concept_id	text			NO		Concept ID.
value	numrange			NO		The numeric range represented by the concept.
uom_id	text			NO		The range value's unit of measure.

Index	Type	Columns
pk_concept__quantity	unique; btree	id

voc.concept__relationship [table]

Relates a concept to its other concepts. E.g. in a broader/narrower hierarchy. After skos:mappingRelation and skos:semanticRelation.

Column	Data type	Key	FK References	Null	Default	Definition
id	uuid	PK		NO	gen_random_uuid()	Concept relationship UUID.
type_id	text			NO		The type of relationship.
concept_id	text			NO		Concept ID.
related_concept_id	text			NO		Related concept ID.
system__order	integer			YES		Integer value marking the related concept's position in a ordered collection.

Constraint	Type	Columns	Description
uq_concept__relationship__association	unique	type_id, concept_id, related_concept_id	Ensures only one relationship of a given type between two concepts is allowed.

Index	Type	Columns
pk_concept__relationship	unique; btree	id
uq_concept__relationship__association	unique; btree	type_id, concept_id, related_concept_id

voc.concept__relationship__type [table]

Controlled list of types for a concept relationship. When the relationship is bi-direction, i.e. can be navigated in the inverse direction an inverse_id and inverse_label should be provided. For simplicity's sake a bi-directional relationship should only be asserted in one direction.

Column	Data type	Key	FK References	Null	Default	Definition
id	text	PK		NO		The relationship type id.
label	text			NO		A human-friendly label for the relationship type.
bidirectional	boolean			NO		true if the relationship is bi-directional (has an inverse_id). Generated.
inverse_id	text			YES		The inverse relationship type id.
inverse_label	text			YES		A human-friendly label for the relationship type's inverse relationship type (for bi-directional relationships where the relationship is consistently specified in one direction only).
definition	text			YES		A brief definition of the relationship type. Content may be formatted using Markdown syntax.
super_type_id	text			YES		The super (parent) type id where a type specialises another. E.g. narrow-match specialises match.
rdf_exact_match	text[]			YES		The compact URIs of the well-known RDF resource the type matches, where applicable. E.g. skos:narrowMatch.
rdf_inverse_exact_match	text[]			YES		The compact URIs of the well-known RDF resource the inverse of the type matches, where applicable. E.g. skos:broaderMatch.

Index	Type	Columns
pk_concept__relationship__type	unique; btree	id

voc.concept__relationship__with_inverse [view]

View extending voc.concept__relationship to includes inverse relationships as records.

Column	Data type	Definition
id	uuid	Concept relationship UUID.
concept_id	text	Concept UUID.
type_id	text	The type of relationship.
related_concept_id	text	Related concept UUID.
system__order	integer	Integer value marking the related concept's position in a ordered collection.

voc.concept__translation [table]

Concept scheme label and description properties as translated into the language specified in system__language.

Column	Data type	Key	FK References	Null	Default	Definition
id	uuid	PK		NO	gen_random_uuid()	Concept translation UUID.
concept_id	text			NO		Concept ID.
preferred_label	text			NO		Translation of the preferred, human-readable, text identifier for the concept. After skos:prefLabel.
alternative_labels	text[]			YES		Translation of the alternative, human-readable, text identifiers for the concept. After skos:prefLabel.
definition	text			YES		Translation of the brief description of the concept. Content may be formatted using Markdown syntax.
editorial_note	text			YES		Translation of the notes on issues with the concept and any changes that may be required. After skos:editorialNote.
system__language	text			NO		An IETF language tag for the language used for translation. Directs the client to use a specific language for a multi-language scheme.

Index	Type	Columns
fx_concept__translation__concept	btree	concept_id
pk_concept__translation	unique; btree	id

voc.concept__view [view]

Human friendly view of voc.concept. Gets labels for UUIDs and aggregates labels of related entities into arrays.

Column	Data type	Definition
id	text	Concept UUID.
in_scheme	text	Concept scheme the concept is a member of.
top_concept	boolean	Shows whether the concept is a top (root) concept in its concept scheme. Defaults to true as most schemes a simple lists. In a hierarchy narrower concepts are, by definition, not top concepts so the value will be false.
label	text	The formatted label, structured according to the label template defined for the concept scheme
preferred_label	text	The preferred, human-readable, text identifier for the concept. After skos:prefLabel.
alternative_labels	text[]	An array of alternative, human-readable, text identifiers for the concept (synonyms). After skos:altLabel.
notation	text	A code, token or identifier formally used by the schema maintainers to identify and access a concept. E.g. L for the NZSC Allophanic Soils order. After skos:notation.
definition	text	A brief definition of the concept. Content may be formatted using Markdown syntax. More detailed descriptions should be provided via the resources at the related concept_scheme.source and concept_scheme.see_also links. After skos:definition.
broader	text[]	Array of broader (parent) concepts.
narrower	text[]	Array of narrower (child) concepts.
related	text[]	Array of concepts that have a simple semantic relationship with the concept.
matching	text[]	Array of matching (exact, close, narrower or broader) concepts from another schema.

voc.concept_collection [table]

A collection of concepts describing the same property, or a subset of a vocabulary's concepts required by an application. Potentially a publication, or a chapter within a publication. After skos:Collection and skos:OrderedCollection.

Column	Data type	Key	FK References	Null	Default	Definition
id	uuid	PK		NO	gen_random_uuid()	Concept collection UUID.
register_id	uuid			NO		Governing register UUID.
preferred_label	text			NO		The preferred, human-readable, text identifier for the collection. After skos:prefLabel.
notation	text			YES		A code, token or identifier formally used by the schema maintainers to identify and access a collection. E.g. NZSC for the ... NZSC. After skos:notation.
description	text			YES		A brief description of the concept collection. Content may be formatted using Markdown syntax. More detailed descriptions should be provided via the resources at the related concept_scheme.source and concept_scheme.see_also links.
default_concept_id	text			YES		The UUID of the concept to be used as the default value for properties populated by the concept collection (where appropriate).
editorial_note	text			YES		Notes on issues with the concept and any changes that may be required. After skos:editorialNote.
system__ordered	boolean			NO	true	Specifies whether the collection members are ordered. Ordering may either be in natural ascending/descending alphanumeric order, or as specified by the vocabulary maintainer. true means the collection is a skos:OrderedCollection.
system__language	text			YES	'en'	An IETF language tag for the default language used for concept labels, descriptions and definitions when the collection is used. Directs the client to use a specific language for a multi-language scheme. If null, the default concept_scheme.system_language is used.
system__label_template	text			YES		A template specifying how the concept preferred_label and notation values are to be used to great a label for presentation. May be overridden by the concept_collection system__label_template template. Format is text with preferred_label and notation column values inserted as variables (${preferred_label} and ${notation}). E.g.: '${preferred_label} [${notation}]'
system__tag	text[]			YES		An array of tags that can be used to group or otherwise organise concept schemes.

Constraint	Type	Columns	Description
uq_concept_collection__notation	unique	notation	Ensures the notation value is unique as it is used for HTTP URIs.

Index	Type	Columns
fx_concept_collection__register	btree	register_id
pk_concept_collection	unique; btree	id
uq_concept_collection__notation	unique; btree	notation

voc.concept_collection__member [table]

Relates a collection to its member concepts.

Column	Data type	Key	FK References	Null	Default	Definition
id	uuid	PK		NO	gen_random_uuid()	Concept collection member UUID.
concept_collection_id	uuid			NO		Concept collection UUID.
member_concept_id	text			NO		Member concept ID.
system__order	integer			YES		Integer value marking the member's position in a ordered collection.

Index	Type	Columns
pk_concept_collection__member	unique; btree	id

voc.concept_collection__member__view [view]

Human friendly view of voc.concept_collection__member. Gets preferred labels for members. Ordered by voc.concept_collection__member.system__order.

Column	Data type	Definition
concept_collection	text	name of the concept collection.
concept_collection_id	uuid	Concept collection UUID.
member_concept_id	text	Member concept UUID.
member_label	text	Member concept labelled according to the concept collections label template.
system__order	integer	Integer value marking the member's position in a ordered collection.

voc.concept_collection__view [view]

Human friendly view of voc.concept_collection. Gets labels for UUIDs and aggregates labels of related entities into arrays.

Column	Data type	Definition
id	uuid	Concept collection UUID.
preferred_label	text	The preferred, human-readable, text identifier for the collection. After skos:prefLabel.
notation	text	A code, token or identifier formally used by the schema maintainers to identify and access a collection. E.g. NZSC for the ... NZSC. After skos:notation.
description	text	A brief description of the concept collection. Content may be formatted using Markdown syntax. More detailed descriptions should be provided via the resources at the related concept_scheme.source and concept_scheme.see_also links.
members	text[]	Array of concepts that are members of this collection.
system__language	text	An IETF language tag for the default language used for concept labels, descriptions and definitions when the collection is used. Directs the client to use a specific language for a multi-language scheme. If null, the default concept_scheme.system__language is used.
system__label_template	text	A template specifying how the concept preferred_label and notation values are to be used to great a label for presentation. May be overridden by the concept_collection system__label_template template. Format is text with preferred_label and notation column values inserted as variables (${preferred_label} and ${notation}). E.g.: '${preferred_label} [${notation}]'

voc.concept_scheme [table]

A vocabulary or similar collection of concepts with a common source and history of governance. Potentially a publication, or a chapter within a publication. After skos:concept_scheme.

Column	Data type	Key	FK References	Null	Default	Definition
id	text	PK		NO		Concept scheme ID.
register_id	uuid			NO		Governing register UUID.
preferred_label	text			NO		The preferred, human-readable, text identifier for the concept scheme. After skos:prefLabel.
notation	text			YES		A code, token or identifier formally used by the schema maintainers to identify and access a concept scheme. E.g. NZSC for the ... NZSC. After skos:notation.
description	text			YES		A brief description of the concept concept scheme. Content may be formatted using Markdown syntax. More detailed descriptions should be provided via the resources at the related concept_scheme.source and concept_scheme.see_also links.
editorial_note	text			YES		Notes on issues with the concept and any changes that may be required. After skos:editorialNote.
source	text[]			YES		An array of citations (preferably DOIs or other URIs) for the source of the data in the concept scheme.
see_also	text[]			YES		An array of URLs for web pages or other web resources that describe or define this concept scheme.
system__language	text			NO	'en'	An IETF language tag for the default language used for concept labels, descriptions and definitions when the concept scheme is used. Directs the client to use a specific language for a multi-language scheme. If null, the default concept_scheme.system__language is used.
system__label_template	text			YES	'${preferred_label}'	A template specifying how the concept preferred_label and notation values are to be used to great a label for presentation. May be overridden by the concept_concept scheme system__label_template template. Format is text with preferred_label and notation column values inserted as variables (${preferred_label} and ${notation}). E.g.: '${preferred_label} [${notation}]'
system__tag	text[]			YES		An array of tags that can be used to group or otherwise organise concept schemes.

Index	Type	Columns
fx_concept_scheme__register	btree	register_id
pk_concept_scheme	unique; btree	id

voc.concept_scheme__translation [table]

Concept scheme label and description properties as translated into the language specified in system__language.

Column	Data type	Key	FK References	Null	Default	Definition
id	uuid	PK		NO	gen_random_uuid()	Concept scheme translation UUID.
concept_scheme_id	text			NO		Concept scheme ID.
preferred_label	text			NO		Translation of the preferred, human-readable, text identifier for the concept scheme. After skos:prefLabel.
description	text			YES		Translation of the brief description of the concept scheme. Content may be formatted using Markdown syntax.Content may be formatted using Markdown syntax.
editorial_note	text			YES		Translation of the notes on issues with the concept scheme and any changes that may be required. After skos:editorialNote.
system__language	text			NO		An IETF language tag for the language used for translation. Directs the client to use a specific language for a multi-language scheme.

Index	Type	Columns
fx_concept_scheme__translation__concept_scheme	btree	concept_scheme_id
pk_concept_scheme__translation	unique; btree	id

voc.concept_scheme__view [view]

Human friendly view of voc.concept_scheme. Gets labels for UUIDs and aggregates labels of related entities into arrays.

Column	Data type	Definition
id	text	Concept scheme UUID.
preferred_label	text	The preferred, human-readable, text identifier for the concept scheme. After skos:prefLabel.
notation	text	A code, token or identifier formally used by the schema maintainers to identify and access a concept scheme. E.g. NZSC for the ... NZSC. After skos:notation.
description	text	A brief description of the concept concept scheme. Content may be formatted using Markdown syntax. More detailed descriptions should be provided via the resources at the related concept_scheme.source and concept_scheme.see_also links.
top_concepts	text[]	An array of concepts that are at the root (top) of the concept scheme.
lower_concepts	text[]	An array of concepts that are at the narrower concepts of the concept scheme's top concepts.
source	text[]	An array of citations (preferably DOIs or other URIs) for the source of the data in the concept scheme.
see_also	text[]	An array of URLs for web pages or other web resources that describe or define this concept scheme.
system__language	text	An IETF language tag for the default language used for concept labels, descriptions and definitions when the concept scheme is used. Directs the client to use a specific language for a multi-language scheme. If null, the default concept_scheme.system__language is used.
system__label_template	text	A template specifying how the concept preferred_label and notation values are to be used to great a label for presentation. May be overridden by the concept_concept scheme system__label_template template. Format is text with preferred_label and notation column values inserted as variables (${preferred_label} and ${notation}). E.g.: '${preferred_label} [${notation}]'

Functions

Function	Type	Arguments	Returns	Description
concept__label	function	_concept_id; _concept_collection_id	text	Returns a concept's (_concept_id) formatted label based on the concept collection (_concept_collection_id) or concept scheme (if _concept_collection_id is null) template defined in concept_collection