35 lines
1.2 KiB
SQL
35 lines
1.2 KiB
SQL
-- Controlled vocabularies (term sources) and their terms.
|
|
CREATE TABLE vocabulary (
|
|
id UUID PRIMARY KEY,
|
|
key TEXT NOT NULL UNIQUE -- e.g. 'material', 'object_name'
|
|
);
|
|
|
|
CREATE TABLE term (
|
|
id UUID PRIMARY KEY,
|
|
vocabulary_id UUID NOT NULL REFERENCES vocabulary (id) ON DELETE RESTRICT,
|
|
external_uri TEXT -- e.g. Getty AAT / KulturNav / Wikidata URI
|
|
);
|
|
CREATE INDEX term_vocabulary_idx ON term (vocabulary_id);
|
|
|
|
CREATE TABLE term_label (
|
|
term_id UUID NOT NULL REFERENCES term (id) ON DELETE CASCADE,
|
|
lang TEXT NOT NULL CHECK (lang <> ''),
|
|
label TEXT NOT NULL CHECK (label <> ''),
|
|
PRIMARY KEY (term_id, lang)
|
|
);
|
|
|
|
-- Authority records: person / organisation / place. Store once, link many.
|
|
CREATE TABLE authority (
|
|
id UUID PRIMARY KEY,
|
|
kind TEXT NOT NULL CHECK (kind IN ('person', 'organisation', 'place')),
|
|
external_uri TEXT
|
|
);
|
|
CREATE INDEX authority_kind_idx ON authority (kind);
|
|
|
|
CREATE TABLE authority_label (
|
|
authority_id UUID NOT NULL REFERENCES authority (id) ON DELETE CASCADE,
|
|
lang TEXT NOT NULL CHECK (lang <> ''),
|
|
label TEXT NOT NULL CHECK (label <> ''),
|
|
PRIMARY KEY (authority_id, lang)
|
|
);
|