Relational Schema for Structured Linguistic Word Data in PostgreSQL

Full report

On this page

Executive Summary

A strong design for this requirement is a third-normal-form lexical schema centered on a single word table that stores the only free-text field—word_text—and moves everything else into typed columns or relationship tables. The result is a database that can encode major structured dimensions usually associated with dictionary entries—grammatical function, pronunciation-related metadata, etymology, usage/register, orthography, and derivational links—without storing open-ended definitions, glosses, or prose notes. That trade-off is important: dictionaries normally include meanings, pronunciations, functions, etymologies, and syntactic/idiomatic uses, but your constraint deliberately limits the model to controlled metadata, not full lexicographic narrative.

The schema below separates Morphology, Phonology, and Semantics into distinct relational areas. That reflects the linguistic distinction between derivation and inflection, the practice of classifying words into semantic fields, and the standard treatment of word stress and phonemic inventories via the International Phonetic Alphabet.

For PostgreSQL, enums are appropriate for comparatively stable, closed sets because PostgreSQL defines them as static, ordered sets of values. But there is a real trade-off: adding or reordering enum values is possible, yet not as operationally flexible as maintaining a lookup table, and PostgreSQL’s documentation notes caveats around later-added enum values. That matters especially for language-of-origin, dialect, and sometimes semantic field, because the authoritative ISO 639 language-code sets are explicitly open lists that can be extended and refined. I therefore implement origin_language as an enum here because you explicitly requested it, but I recommend a lookup-table alternative for production scale.

Assumptions and Design Principles

The design assumes PostgreSQL as the target RDBMS, UTF-8 server encoding, and no fixed scale limit. PostgreSQL supports UTF-8 and other multibyte encodings, with the practical requirement that encoding and locale settings be compatible.

I assume that a row in word represents a curated lexical item—usually a citation form, but it may also represent a stored orthographic variant or special lexicalized form when that matters operationally. A stricter lexeme/form split is possible, but I keep the core model simpler because your requested queries are lexical, not token-level. The schema still preserves the key distinction that inflection marks grammatical categories while derivation creates new lexical items, so root/base/derived links live in separate relation tables rather than being folded into a single overloaded self-reference.

The categorical choices are grounded in standard reference practice. Dictionary and grammar sources treat proper nouns, count nouns, formal vs. informal usage, archaic forms, dialects, semantic fields, and compound words as structured lexical distinctions rather than purely free-text commentary.

A final design principle is that multi-valued attributes should be sparse join tables, not wide boolean grids, whenever the value set can plausibly grow. That is why semantic fields, dialects, registers, affixes, and phoneme-presence flags are modeled as separate tables. Conceptually they are still “flags,” but relationally they are more normalized, easier to index selectively, and easier to validate for uniqueness. PostgreSQL’s indexing model strongly favors this approach for exact relational filtering, while GIN is more useful when you deliberately denormalize into arrays or other composite values.

Normalized Schema

The schema below is normalized to 3NF / near-BCNF for the lexical use case: each table covers one linguistic domain, multi-valued attributes are decomposed into relation tables, and relation tables carry only the keys and attributes that belong to the relationship itself.

Core tables and relationships

This structure mirrors the way authoritative references partition word information: grammatical class and countability, pronunciation/stress, etymology, usage, and related-word structure are distinct but linked dimensions.

ER diagram

erDiagram
    WORD ||--|| WORD_MORPHOLOGY : has
    WORD ||--|| WORD_SEMANTICS : has
    WORD ||--|| WORD_PHONOLOGY : has
    WORD ||--|| WORD_ETYMOLOGY : has
    WORD ||--|| WORD_ORTHOGRAPHY : has

    WORD ||--o{ WORD_SEMANTIC_FIELD : tagged_as
    WORD ||--o{ WORD_REGISTER : used_in
    WORD ||--o{ WORD_DIALECT : occurs_in
    WORD ||--o{ WORD_PREFIX : has_prefix
    WORD ||--o{ WORD_SUFFIX : has_suffix
    WORD ||--o{ WORD_PHONEME : contains

    WORD ||--o{ WORD_ROOT_RELATION : child_word
    WORD ||--o{ WORD_ROOT_RELATION : root_word
    WORD ||--o{ WORD_BASE_RELATION : derived_word
    WORD ||--o{ WORD_BASE_RELATION : base_word
    WORD ||--o{ WORD_VARIANT_RELATION : source_variant
    WORD ||--o{ WORD_VARIANT_RELATION : target_variant
    WORD ||--o{ WORD_DERIVED_RELATION : base_word
    WORD ||--o{ WORD_DERIVED_RELATION : derived_word

Lookup and query flow

flowchart TD
    A[word_text lookup] --> B[WORD]
    B --> C[WORD_MORPHOLOGY]
    B --> D[WORD_SEMANTICS]
    B --> E[WORD_PHONOLOGY]
    B --> F[WORD_ETYMOLOGY]
    B --> G[WORD_ORTHOGRAPHY]
    B --> H[WORD_SEMANTIC_FIELD]
    B --> I[WORD_REGISTER]
    B --> J[WORD_DIALECT]
    B --> K[WORD_PREFIX]
    B --> L[WORD_SUFFIX]
    B --> M[WORD_PHONEME]

    B --> N[WORD_ROOT_RELATION]
    B --> O[WORD_BASE_RELATION]
    B --> P[WORD_VARIANT_RELATION]
    B --> Q[WORD_DERIVED_RELATION]

    N --> R[root words]
    O --> S[base words]
    P --> T[variant forms]
    Q --> U[derived words]

Enum Catalog

The enum catalog below is a practical controlled vocabulary rather than a claim that linguistics provides one universal canonical list for every category. For the categories where typological and dictionary sources clearly establish the dimension—such as tense, aspect, mood, gender, case, countability, semantic field, dialect, and stress—the lists are intentionally compact but extensible.

Morphology

noun, verb, adjective, adverb, pronoun, determiner, preposition, conjunction, interjection, numeral, particle, abbreviation

• part_of_speech

not_applicable, tenseless, present, past, future, imperfect, preterite, pluperfect

• tense

not_applicable, simple, progressive, perfect, perfective, imperfective, habitual, iterative, prospective

• aspect

not_applicable, indicative, imperative, subjunctive, conditional, optative, potential, interrogative, declarative, participial

• mood

not_applicable, singular, plural, dual, trial, paucal

• grammatical_number

not_applicable, masculine, feminine, neuter, common, animate, inanimate

• grammatical_gender

not_applicable, nominative, accusative, ergative, absolutive, genitive, dative, instrumental, ablative, locative, vocative, oblique

• grammatical_case

not_applicable, positive, comparative, superlative, absolute

• degree

not_applicable, count, mass, both, collective, singular_only, plural_only

• countability

not_applicable, regular, irregular, strong, weak, suppletive, invariant, defective, learned_class

• inflectional_class

Derivational features

un_negative, re_again, pre_before, post_after, anti_against, mis_wrongly, over_excessive, under_insufficient, sub_under, super_above, inter_between, trans_across, pseudo_false, neo_new

• prefix_type

ness_state, ity_state, er_agent, or_agent, ism_doctrine, ist_person, ly_manner, ion_action, ment_result, ship_status, hood_state, less_without, ful_full_of, able_capable, al_relational, ic_relational, ize_causative, ing_progressive, ed_participial

• suffix_type

negation_absence, repetition_again, temporal_before, temporal_after, opposition, location_under, location_over, relation_between, transition_change, false_imitation, newness, state_quality, agentive, instrumental, doctrine_belief, associated_person, action_process, result_state, manner, capability, relational, progressive_gerund, past_participial

• affix_meaning

suffixation, prefixation, compounding, conversion, back_formation, acronymization, clipping, blending, borrowing

• derivation_process

Semantics

artifact, attribute, body, cognition, communication, emotion, event, food, group, location, motion, person, phenomenon, plant, possession, process, quantity, relation, shape, state, substance, time, animal, social_institution, technology

• semantic_field

concrete, abstract, mixed, unknown

• concreteness

human, animate_nonhuman, inanimate, abstract, mixed, unknown

• animacy

Phonology

not_applicable, monosyllabic, initial, penultimate, antepenultimate, final, secondary_plus_primary, variable, unstressed

• stress_pattern

P, B, T, D, K, G, F, V, TH_VOICELESS, TH_VOICED, S, Z, SH, ZH, H, CH, JH, M, N, NG, L, R, W, Y, IY, IH, EY, EH, AE, AA, AH, AO, OW, UH, UW, ER, AX, AY, AW, OY

• phoneme_en

Etymology and orthography

unknown, english, anglo_french, old_french, french, latin, greek, german, dutch, spanish, italian, portuguese, russian, arabic, persian, hebrew, turkish, hindi, sanskrit, japanese, chinese, mixed

• origin_language

unknown, old_english, middle_english, early_modern_english, c14, c15, c16, c17, c18, c19, c20, c21

• etymology_period

spelling_variant, dialectal_variant, archaic_variant, orthographic_variant

• variant_relation_type

Usage/register

formal, neutral, informal, colloquial, slang, technical, literary, vulgar, taboo, archaic_register

• usage_register

international, american, british, australian, canadian, irish, scottish, indian_english, new_zealand, south_african, regional_other

• dialect

PostgreSQL DDL

The DDL below uses PostgreSQL enums, primary keys, foreign keys, unique constraints, multicolumn indexes, and partial indexes in the way PostgreSQL officially documents them. PostgreSQL notes that enums are ordered static sets, primary keys and unique constraints automatically create unique B-tree indexes, multicolumn indexes are supported for B-tree/GiST/GIN/BRIN, and partial indexes are useful when only a subset of rows is interesting to query.

-- Representative enum DDL. Full allowed-value lists are in the Enum Catalog above.

CREATE TYPE part_of_speech AS ENUM (
  'noun','verb','adjective','adverb','pronoun','determiner',
  'preposition','conjunction','interjection','numeral','particle','abbreviation'
);

CREATE TYPE countability AS ENUM (
  'not_applicable','count','mass','both','collective','singular_only','plural_only'
);

CREATE TYPE tense AS ENUM (
  'not_applicable','tenseless','present','past','future','imperfect','preterite','pluperfect'
);

CREATE TYPE aspect AS ENUM (
  'not_applicable','simple','progressive','perfect','perfective',
  'imperfective','habitual','iterative','prospective'
);

CREATE TYPE mood AS ENUM (
  'not_applicable','indicative','imperative','subjunctive','conditional',
  'optative','potential','interrogative','declarative','participial'
);

CREATE TYPE grammatical_number AS ENUM (
  'not_applicable','singular','plural','dual','trial','paucal'
);

CREATE TYPE grammatical_gender AS ENUM (
  'not_applicable','masculine','feminine','neuter','common','animate','inanimate'
);

CREATE TYPE grammatical_case AS ENUM (
  'not_applicable','nominative','accusative','ergative','absolutive',
  'genitive','dative','instrumental','ablative','locative','vocative','oblique'
);

CREATE TYPE degree AS ENUM (
  'not_applicable','positive','comparative','superlative','absolute'
);

CREATE TYPE inflectional_class AS ENUM (
  'not_applicable','regular','irregular','strong','weak',
  'suppletive','invariant','defective','learned_class'
);

CREATE TYPE semantic_field AS ENUM (
  'artifact','attribute','body','cognition','communication','emotion','event','food',
  'group','location','motion','person','phenomenon','plant','possession','process',
  'quantity','relation','shape','state','substance','time','animal','social_institution','technology'
);

CREATE TYPE concreteness AS ENUM ('concrete','abstract','mixed','unknown');
CREATE TYPE animacy AS ENUM ('human','animate_nonhuman','inanimate','abstract','mixed','unknown');

CREATE TYPE stress_pattern AS ENUM (
  'not_applicable','monosyllabic','initial','penultimate','antepenultimate',
  'final','secondary_plus_primary','variable','unstressed'
);

CREATE TYPE phoneme_en AS ENUM (
  'P','B','T','D','K','G','F','V','TH_VOICELESS','TH_VOICED','S','Z','SH','ZH','H',
  'CH','JH','M','N','NG','L','R','W','Y','IY','IH','EY','EH','AE','AA','AH','AO',
  'OW','UH','UW','ER','AX','AY','AW','OY'
);

CREATE TYPE origin_language AS ENUM (
  'unknown','english','anglo_french','old_french','french','latin','greek','german',
  'dutch','spanish','italian','portuguese','russian','arabic','persian','hebrew',
  'turkish','hindi','sanskrit','japanese','chinese','mixed'
);

CREATE TYPE etymology_period AS ENUM (
  'unknown','old_english','middle_english','early_modern_english',
  'c14','c15','c16','c17','c18','c19','c20','c21'
);

CREATE TYPE usage_register AS ENUM (
  'formal','neutral','informal','colloquial','slang','technical',
  'literary','vulgar','taboo','archaic_register'
);

CREATE TYPE dialect AS ENUM (
  'international','american','british','australian','canadian',
  'irish','scottish','indian_english','new_zealand','south_african','regional_other'
);

CREATE TYPE prefix_type AS ENUM (
  'un_negative','re_again','pre_before','post_after','anti_against','mis_wrongly',
  'over_excessive','under_insufficient','sub_under','super_above',
  'inter_between','trans_across','pseudo_false','neo_new'
);

CREATE TYPE suffix_type AS ENUM (
  'ness_state','ity_state','er_agent','or_agent','ism_doctrine','ist_person',
  'ly_manner','ion_action','ment_result','ship_status','hood_state',
  'less_without','ful_full_of','able_capable','al_relational',
  'ic_relational','ize_causative','ing_progressive','ed_participial'
);

CREATE TYPE affix_meaning AS ENUM (
  'negation_absence','repetition_again','temporal_before','temporal_after','opposition',
  'location_under','location_over','relation_between','transition_change',
  'false_imitation','newness','state_quality','agentive','instrumental',
  'doctrine_belief','associated_person','action_process','result_state',
  'manner','capability','relational','progressive_gerund','past_participial'
);

CREATE TYPE derivation_process AS ENUM (
  'suffixation','prefixation','compounding','conversion','back_formation',
  'acronymization','clipping','blending','borrowing'
);

CREATE TYPE variant_relation_type AS ENUM (
  'spelling_variant','dialectal_variant','archaic_variant','orthographic_variant'
);

CREATE TABLE word (
  word_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  word_text TEXT NOT NULL,
  pos part_of_speech NOT NULL,
  countability countability NOT NULL DEFAULT 'not_applicable',
  is_compound BOOLEAN NOT NULL DEFAULT FALSE,
  is_proper_noun BOOLEAN NOT NULL DEFAULT FALSE,
  is_borrowed BOOLEAN NOT NULL DEFAULT FALSE,
  is_neologism BOOLEAN NOT NULL DEFAULT FALSE,
  is_obsolete BOOLEAN NOT NULL DEFAULT FALSE,
  is_acronym BOOLEAN NOT NULL DEFAULT FALSE,
  CONSTRAINT uq_word_text UNIQUE (word_text),
  CONSTRAINT ck_word_text_nonempty CHECK (length(word_text) > 0)
);

CREATE TABLE word_morphology (
  word_id BIGINT PRIMARY KEY REFERENCES word(word_id) ON DELETE CASCADE,
  tense tense NOT NULL DEFAULT 'not_applicable',
  aspect aspect NOT NULL DEFAULT 'not_applicable',
  mood mood NOT NULL DEFAULT 'not_applicable',
  gram_number grammatical_number NOT NULL DEFAULT 'not_applicable',
  gram_gender grammatical_gender NOT NULL DEFAULT 'not_applicable',
  gram_case grammatical_case NOT NULL DEFAULT 'not_applicable',
  degree degree NOT NULL DEFAULT 'not_applicable',
  inflectional_class inflectional_class NOT NULL DEFAULT 'not_applicable'
);

CREATE TABLE word_semantics (
  word_id BIGINT PRIMARY KEY REFERENCES word(word_id) ON DELETE CASCADE,
  concreteness concreteness NOT NULL DEFAULT 'unknown',
  animacy animacy NOT NULL DEFAULT 'unknown'
);

CREATE TABLE word_semantic_field (
  word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE CASCADE,
  semantic_field semantic_field NOT NULL,
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (word_id, semantic_field)
);

CREATE TABLE word_phonology (
  word_id BIGINT PRIMARY KEY REFERENCES word(word_id) ON DELETE CASCADE,
  syllable_count SMALLINT NOT NULL CHECK (syllable_count >= 0 AND syllable_count <= 16),
  stress_pattern stress_pattern NOT NULL DEFAULT 'not_applicable'
);

CREATE TABLE word_phoneme (
  word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE CASCADE,
  phoneme phoneme_en NOT NULL,
  PRIMARY KEY (word_id, phoneme)
);

CREATE TABLE word_etymology (
  word_id BIGINT PRIMARY KEY REFERENCES word(word_id) ON DELETE CASCADE,
  origin_language origin_language NOT NULL DEFAULT 'unknown',
  origin_period etymology_period NOT NULL DEFAULT 'unknown'
);

CREATE TABLE word_orthography (
  word_id BIGINT PRIMARY KEY REFERENCES word(word_id) ON DELETE CASCADE,
  is_archaic_spelling BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE word_register (
  word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE CASCADE,
  usage_register usage_register NOT NULL,
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (word_id, usage_register)
);

CREATE TABLE word_dialect (
  word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE CASCADE,
  dialect dialect NOT NULL,
  is_primary BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (word_id, dialect)
);

CREATE TABLE word_prefix (
  word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE CASCADE,
  prefix_type prefix_type NOT NULL,
  semantic_function affix_meaning NOT NULL,
  position_no SMALLINT NOT NULL DEFAULT 1,
  PRIMARY KEY (word_id, prefix_type, position_no)
);

CREATE TABLE word_suffix (
  word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE CASCADE,
  suffix_type suffix_type NOT NULL,
  semantic_function affix_meaning NOT NULL,
  position_no SMALLINT NOT NULL DEFAULT 1,
  PRIMARY KEY (word_id, suffix_type, position_no)
);

CREATE TABLE word_root_relation (
  word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE CASCADE,
  root_word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE RESTRICT,
  relation_rank SMALLINT NOT NULL DEFAULT 1,
  PRIMARY KEY (word_id, root_word_id),
  CONSTRAINT ck_root_not_self CHECK (word_id <> root_word_id)
);

CREATE TABLE word_base_relation (
  word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE CASCADE,
  base_word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE RESTRICT,
  relation_rank SMALLINT NOT NULL DEFAULT 1,
  PRIMARY KEY (word_id, base_word_id),
  CONSTRAINT ck_base_not_self CHECK (word_id <> base_word_id)
);

CREATE TABLE word_variant_relation (
  word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE CASCADE,
  variant_word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE CASCADE,
  variant_type variant_relation_type NOT NULL,
  PRIMARY KEY (word_id, variant_word_id, variant_type),
  CONSTRAINT ck_variant_not_self CHECK (word_id <> variant_word_id)
);

CREATE TABLE word_derived_relation (
  base_word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE CASCADE,
  derived_word_id BIGINT NOT NULL REFERENCES word(word_id) ON DELETE CASCADE,
  process derivation_process NOT NULL,
  PRIMARY KEY (base_word_id, derived_word_id),
  CONSTRAINT ck_derived_not_self CHECK (base_word_id <> derived_word_id)
);

-- Recommended indexes
CREATE INDEX ix_word_pos_countability ON word (pos, countability);
CREATE INDEX ix_word_lookup_ci ON word ((lower(word_text)));
CREATE INDEX ix_word_semantic_field ON word_semantic_field (semantic_field, word_id);
CREATE INDEX ix_word_phoneme ON word_phoneme (phoneme, word_id);
CREATE INDEX ix_word_etymology_language ON word_etymology (origin_language, word_id);
CREATE INDEX ix_word_suffix_meaning ON word_suffix (semantic_function, suffix_type, word_id);
CREATE INDEX ix_word_root_reverse ON word_root_relation (root_word_id, word_id);
CREATE INDEX ix_word_base_reverse ON word_base_relation (base_word_id, word_id);
CREATE INDEX ix_word_derived_base ON word_derived_relation (base_word_id, derived_word_id);
CREATE INDEX ix_word_variant_reverse ON word_variant_relation (variant_word_id, word_id);

CREATE INDEX ix_word_borrowed_partial ON word (word_id) WHERE is_borrowed;
CREATE INDEX ix_word_obsolete_partial ON word (word_id) WHERE is_obsolete;
CREATE INDEX ix_word_archaic_spelling_partial ON word_orthography (word_id) WHERE is_archaic_spelling;

Example Inserts and Queries

The six sample rows below emphasize derivation, variant spellings, borrowing, and acronymy/proper-noun status. Their part of speech, etymological origin, variant status, and pronunciation basis come from dictionary or official-source entries: happy is an adjective with Middle English history from hap; happiness is historically derived from happy; color is a borrowing traced through Anglo-French with colour as the chiefly British spelling; karaoke is from Japanese; and NASA is an English acronym for National Aeronautics and Space Administration. Phoneme-presence rows are inferred from the cited standard dictionary pronunciations and pronunciation-guide conventions; that inference is exactly the kind of structured editorial decision this schema is designed to support.

-- Six words

INSERT INTO word
(word_id, word_text, pos, countability, is_compound, is_proper_noun, is_borrowed, is_neologism, is_obsolete, is_acronym)
VALUES
(1, 'happy',     'adjective',   'not_applicable', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE),
(2, 'happiness', 'noun',        'mass',           FALSE, FALSE, FALSE, FALSE, FALSE, FALSE),
(3, 'color',     'noun',        'both',           FALSE, FALSE, TRUE,  FALSE, FALSE, FALSE),
(4, 'colour',    'noun',        'both',           FALSE, FALSE, TRUE,  FALSE, FALSE, FALSE),
(5, 'karaoke',   'noun',        'mass',           FALSE, FALSE, TRUE,  FALSE, FALSE, FALSE),
(6, 'NASA',      'abbreviation','not_applicable', FALSE, TRUE,  FALSE, FALSE, FALSE, TRUE);

INSERT INTO word_morphology
(word_id, tense, aspect, mood, gram_number, gram_gender, gram_case, degree, inflectional_class)
VALUES
(1, 'not_applicable', 'not_applicable', 'not_applicable', 'not_applicable', 'not_applicable', 'not_applicable', 'positive', 'regular'),
(2, 'not_applicable', 'not_applicable', 'not_applicable', 'singular',       'not_applicable', 'not_applicable', 'not_applicable', 'regular'),
(3, 'not_applicable', 'not_applicable', 'not_applicable', 'singular',       'not_applicable', 'not_applicable', 'not_applicable', 'regular'),
(4, 'not_applicable', 'not_applicable', 'not_applicable', 'singular',       'not_applicable', 'not_applicable', 'not_applicable', 'regular'),
(5, 'not_applicable', 'not_applicable', 'not_applicable', 'singular',       'not_applicable', 'not_applicable', 'not_applicable', 'invariant'),
(6, 'not_applicable', 'not_applicable', 'not_applicable', 'not_applicable', 'not_applicable', 'not_applicable', 'not_applicable', 'not_applicable');

INSERT INTO word_semantics (word_id, concreteness, animacy) VALUES
(1, 'abstract', 'abstract'),
(2, 'abstract', 'abstract'),
(3, 'mixed',    'inanimate'),
(4, 'mixed',    'inanimate'),
(5, 'abstract', 'inanimate'),
(6, 'concrete', 'inanimate');

INSERT INTO word_semantic_field (word_id, semantic_field, is_primary) VALUES
(1, 'emotion',            TRUE),
(2, 'emotion',            TRUE),
(3, 'attribute',          TRUE),
(4, 'attribute',          TRUE),
(5, 'event',              TRUE),
(6, 'social_institution', TRUE),
(6, 'technology',         FALSE);

INSERT INTO word_phonology (word_id, syllable_count, stress_pattern) VALUES
(1, 2, 'initial'),
(2, 3, 'initial'),
(3, 2, 'initial'),
(4, 2, 'initial'),
(5, 4, 'secondary_plus_primary'),
(6, 2, 'initial');

INSERT INTO word_phoneme (word_id, phoneme) VALUES
(1, 'H'), (1, 'AE'), (1, 'P'), (1, 'IY'),
(2, 'H'), (2, 'AE'), (2, 'P'), (2, 'IY'), (2, 'N'), (2, 'AX'), (2, 'S'),
(3, 'K'), (3, 'AX'), (3, 'L'), (3, 'ER'),
(4, 'K'), (4, 'AX'), (4, 'L'), (4, 'ER'),
(5, 'K'), (5, 'AE'), (5, 'R'), (5, 'IY'), (5, 'OW'),
(6, 'N'), (6, 'AE'), (6, 'S'), (6, 'AX');

INSERT INTO word_etymology (word_id, origin_language, origin_period) VALUES
(1, 'english',      'c14'),
(2, 'english',      'c15'),
(3, 'anglo_french', 'c14'),
(4, 'anglo_french', 'c14'),
(5, 'japanese',     'c20'),
(6, 'english',      'c20');

INSERT INTO word_orthography (word_id, is_archaic_spelling) VALUES
(1, FALSE), (2, FALSE), (3, FALSE), (4, FALSE), (5, FALSE), (6, FALSE);

INSERT INTO word_register (word_id, usage_register, is_primary) VALUES
(1, 'neutral', TRUE),
(2, 'neutral', TRUE),
(3, 'neutral', TRUE),
(4, 'neutral', TRUE),
(5, 'neutral', TRUE),
(6, 'technical', TRUE),
(6, 'neutral', FALSE);

INSERT INTO word_dialect (word_id, dialect, is_primary) VALUES
(1, 'international', TRUE),
(2, 'international', TRUE),
(3, 'american',      TRUE),
(4, 'british',       TRUE),
(5, 'international', TRUE),
(6, 'american',      TRUE);

INSERT INTO word_suffix (word_id, suffix_type, semantic_function, position_no) VALUES
(2, 'ness_state', 'state_quality', 1);

INSERT INTO word_root_relation (word_id, root_word_id, relation_rank) VALUES
(2, 1, 1);

INSERT INTO word_base_relation (word_id, base_word_id, relation_rank) VALUES
(2, 1, 1);

INSERT INTO word_derived_relation (base_word_id, derived_word_id, process) VALUES
(1, 2, 'suffixation');

INSERT INTO word_variant_relation (word_id, variant_word_id, variant_type) VALUES
(3, 4, 'dialectal_variant'),
(4, 3, 'dialectal_variant');

Example queries

-- Find all derived words of a root word
WITH RECURSIVE derivation_tree AS (
    SELECT wd.derived_word_id, 1 AS depth
    FROM word w
    JOIN word_derived_relation wd ON wd.base_word_id = w.word_id
    WHERE w.word_text = 'happy'
  UNION ALL
    SELECT wd.derived_word_id, dt.depth + 1
    FROM derivation_tree dt
    JOIN word_derived_relation wd ON wd.base_word_id = dt.derived_word_id
)
SELECT w.word_text, dt.depth
FROM derivation_tree dt
JOIN word w ON w.word_id = dt.derived_word_id
ORDER BY dt.depth, w.word_text;

-- Get words with a given suffix meaning
SELECT w.word_text, ws.suffix_type
FROM word_suffix ws
JOIN word w ON w.word_id = ws.word_id
WHERE ws.semantic_function = 'state_quality'
ORDER BY w.word_text;

-- List all borrowings from a language
SELECT w.word_text, e.origin_period
FROM word w
JOIN word_etymology e ON e.word_id = w.word_id
WHERE w.is_borrowed = TRUE
  AND e.origin_language = 'japanese'
ORDER BY w.word_text;

-- Filter by part of speech and countability
SELECT word_text
FROM word
WHERE pos = 'noun'
  AND countability = 'mass'
ORDER BY word_text;

Design Rationale and Operational Trade-offs

The schema is normalized enough to prevent the worst forms of lexical duplication without becoming so abstract that routine queries become painful. One word row keeps the lookup surface simple; 1:1 satellite tables isolate domains that do not belong in the core row; multivalued relation tables handle phonemes, semantic fields, dialects, registers, and affixes. That is the right compromise for a structured lexical catalog that must stay within your “only one free-text field” rule.

The main limitation is semantic and phonological granularity. Because no free-text definition, gloss, usage note, or full phonetic transcription is allowed, the model captures structured lexical metadata, not full lexicography. If you later need full IPA without violating your rule, the next step is not a text field; it is a segment-sequence extension such as word_phoneme_sequence(word_id, position_no, phoneme, syllable_no, stress_rank). Likewise, if you need formal sense inventories, you would use controlled sense IDs and relation tables rather than prose definitions.

Enum versus lookup-table approach

PostgreSQL enums are strong when the domain is stable, finite, and operationally important for filtering. Lookup tables are better when the vocabulary is open, externally governed, or likely to accumulate metadata. PostgreSQL expressly describes enums as static ordered sets, and ISO 639 explicitly describes language-code sets as open lists that can be extended and refined. That is why part_of_speech, countability, and stress_pattern are excellent enum candidates, while origin_language, dialect, and even semantic_field become better FK targets once the system broadens.

Recommended indexing strategies

PostgreSQL warns that indexes improve retrieval speed but add write overhead, so the practical goal is to index the high-value predicates rather than every enum column. For this schema, nearly everything important is an exact match or FK traversal, so B-tree is the default choice. Use partial indexes for rare booleans such as borrowed or obsolete items. Use expression indexes on normalized word_text lookup if case-insensitive search is required. Use GIN only if you deliberately denormalize some multi-valued features into arrays or other composite values.

Open questions and limitations

The highest-confidence design choice is the relational split itself. The least stable parts are the closed vocabularies for origin_language, dialect, and semantic_field: they are perfectly workable in a bounded implementation, but they are the first things I would migrate to lookup tables in a production multilingual system. ISO 639’s own governance model makes that especially clear for languages.

The six sample inserts intentionally demonstrate derivation, borrowing, variants, and acronymy. They do not positively instantiate every boolean you requested, especially isCompound and a non-controversial isNeologism=true. That is a sample-data limitation, not a schema limitation. The booleans are present and indexable; the test data simply stays close to high-confidence dictionary evidence.

Reference links

• PostgreSQL documentation
• Oxford Learner's Dictionaries
• Merriam-Webster Dictionary
• Oxford English Dictionary
• WALS Online
• Princeton WordNet
• International Phonetic Association chart PDF
• ISO 639 language-code overview
• NASA acronym appendix