Centralized Lookup & Data Integrity

In a well-structured system, reference data is managed at two distinct levels for performance, maintainability, and flexibility.

Core Philosophy

“Lookup Codes are a dictionary for communication (display). Master Data is the brain for business logic.”

1. Definitions

🔴 1.1 Centralized Lookup (lookup_codes)

A unified table of standard codes focused primarily on User Interface (UI) and Localization. Data in this table is small in volume, structurally simple, and rarely changes.

• Primary purpose: Translate a “code” (e.g., MR, ACTIVE) into a human-readable display name in multiple languages
• Data structure: Flat — (category, code, display_name)
• Client usage: Clients typically fetch the entire table once and cache it locally for use in dropdowns and labels

🔵 1.2 Domain Master Data (app_master_db)

A database or group of tables storing domain knowledge — complex, relational, and attribute-rich data used in computation or processing.

• Primary purpose: Used in business logic, pricing calculations, permission checks, or industry standards
• Data structure: Complex, deeply hierarchical, large datasets (tens of thousands to millions of records)
• Client usage: Clients do not load this data wholesale — they query specific items on demand or via search APIs

2. Comparison Matrix

Aspect	Lookup Codes (lookup_codes)	Domain Master Data (app_master_db)
Examples	Title, Gender, Marital Status, Nationality	Classification Codes, Drug Standards, CPT Codes, Lab Items
Volume	Hundreds to thousands	Tens of thousands to millions
Structure	Simple (Code + Multilingual Name)	Complex (Code + Price + Rules + Constraints)
Change frequency	Rarely (static)	More frequent, driven by standards/regulations
Client usage	Display only (show label in UI)	Processing (calculate prices, check interactions)
Example use	r.gender_code = 'M' → display “Male”	d.drug_code = 'PARA500' → check interactions, update stock

3. Data Architecture

The diagram below shows how transactional data (e.g., a resource or an engagement record) connects to both data tiers:

graph TD
    subgraph "Application Database"
        R[Table: resources]
        E[Table: engagements]
    end

    subgraph "Lookup System (UI & Display)"
        L[Table: lookup_codes]
        noteL[Stores: title, gender,<br/>coverage type, status codes]
    end

    subgraph "Master Data DB (Business Logic)"
        M1[Table: classification_codes]
        M2[Table: catalog_items]
        M3[Table: partner_organizations]
    end

    R -- FK: title_code (Display) --> L
    R -- FK: gender_code (Display) --> L

    E -- FK: engagement_type (Display) --> L
    E -- FK: classification_code (Logic) --> M1
    E -- FK: item_code (Inventory Logic) --> M2

4. Use Case Scenarios: Why Separate?

Scenario A: Title (e.g., Mr., Mrs.) → Use Lookup

• Requirement: Display a title on a registration screen
• Logic: No impact on pricing or calculations
• Solution: Store in lookup_codes under category TITLE

Scenario B: Products/Items → Use Master Data

• Requirement: Fulfill an item in an order
• Complexity:
  • Must know unit of measure (piece, pack, box)
  • Must know cost price / sell price (Finance)
  • Must know safety classification (Compliance)
  • Must know standard 24-digit code (Industry Standard)
• Solution: Create a catalog_items table in app_master_db
  • Clients load only the items relevant to their context, not the entire global catalog

Scenario C: Partner Organizations → Use Master Data

• Requirement: Identify the home organization of a resource
• Complexity: Has address, phone, 5-digit ministry code, capability level
• Solution: Use a partner_organizations table
  • A simple lookup code (just a name) is insufficient for generating referral documents that require address and official codes

5. Technical: Centralized Lookup Implementation (SQL)

This section covers the lookup_codes table design to ensure that even with many code types in one table, data integrity is fully maintained.

5.1 Core Concept

Creating a separate table for every code type (e.g., gender_codes, title_codes, status_codes) produces dozens of tiny tables that are hard to maintain. Instead, we use a Centralized Lookup Table with a “Composite Foreign Key with Category Locking” technique.

• Centralized Table: All lookup data lives in lookup_codes with a composite primary key (category, code)
• Category Locking: In consumer tables (e.g., resources), a shadow column stores the locked category value, enforced via a CHECK constraint, so the composite foreign key is always category-specific

5.2 Schema Design

Parent table (Lookup Table)

Stores all codes, separated by category. Supports multilingual display names via JSONB.

CREATE TABLE lookup_codes (
    category     VARCHAR(50) NOT NULL, -- e.g., 'TITLE', 'GENDER', 'BLOOD_TYPE'
    code         VARCHAR(20) NOT NULL, -- e.g., 'MR', 'M', 'A'
    display_name JSONB NOT NULL,       -- e.g., {'en': 'Mister', 'fr': 'Monsieur'}
    is_active    BOOLEAN DEFAULT TRUE,

    -- Composite Primary Key: category + code must be unique
    CONSTRAINT pk_lookup_codes PRIMARY KEY (category, code)
);

-- Index for fast category-based lookups
CREATE INDEX idx_lookup_category ON lookup_codes(category);

Consumer table — example: resources

Add a *_cat shadow column to lock the category and use a composite foreign key:

CREATE TABLE resources (
    id          SERIAL PRIMARY KEY,
    first_name  VARCHAR(100),
    last_name   VARCHAR(100),

    -- Field: Title
    title_code  VARCHAR(20) NOT NULL,
    -- Shadow Column: locked to 'TITLE', cannot be changed
    title_cat   VARCHAR(50) DEFAULT 'TITLE' NOT NULL CHECK (title_cat = 'TITLE'),

    -- Field: Blood Type
    blood_code  VARCHAR(20),
    -- Shadow Column: locked to 'BLOOD_TYPE'
    blood_cat   VARCHAR(50) DEFAULT 'BLOOD_TYPE' CHECK (blood_cat = 'BLOOD_TYPE'),

    -- Constraint: title must exist in lookup_codes under category TITLE
    CONSTRAINT fk_resource_title
        FOREIGN KEY (title_cat, title_code)
        REFERENCES lookup_codes(category, code),

    -- Constraint: blood type must exist in lookup_codes under category BLOOD_TYPE
    CONSTRAINT fk_resource_blood
        FOREIGN KEY (blood_cat, blood_code)
        REFERENCES lookup_codes(category, code)
);

5.3 Usage Example

Seeding lookup data

INSERT INTO lookup_codes (category, code, display_name) VALUES
('TITLE',      'MR',   '{"en": "Mr.", "fr": "M."}'),
('TITLE',      'MS',   '{"en": "Ms.", "fr": "Mme"}'),
('BLOOD_TYPE', 'A',    '{"en": "Type A"}'),
('BLOOD_TYPE', 'B',    '{"en": "Type B"}');

Inserting a resource (happy path)

No need to specify *_cat columns — they default automatically:

INSERT INTO resources (first_name, last_name, title_code, blood_code)
VALUES ('John', 'Smith', 'MR', 'A');
-- ✅ Success: title_cat defaults to 'TITLE', blood_cat defaults to 'BLOOD_TYPE'

Error prevention (wrong category)

If a developer accidentally uses the wrong code type:

INSERT INTO resources (first_name, last_name, title_code, blood_code)
VALUES ('Jane', 'Doe', 'A', 'B');
-- ❌ ERROR: Key (title_cat, title_code)=(TITLE, A) is not present in table "lookup_codes".
-- The composite FK prevents a blood type code from being stored as a title.

Querying with display names

SELECT
    r.first_name,
    r.last_name,
    t.display_name->>'en' AS title_en,
    b.display_name->>'en' AS blood_type_en
FROM resources r
LEFT JOIN lookup_codes t ON r.title_code = t.code AND r.title_cat = t.category
LEFT JOIN lookup_codes b ON r.blood_code = b.code AND r.blood_cat = b.category;

5.4 Pros & Cons

Aspect	Detail
Data Integrity	Very high. Composite FK + Check Constraint guarantees codes are always in the correct category. Eliminates human error at the database level.
Maintainability	Easy. A single master table means one index, one cache point, one admin screen.
Development	Developers always JOIN lookup_codes — no need to remember which specific table to use. Category awareness required.
Storage	Adding a *_cat shadow column appears redundant but PostgreSQL compression makes the overhead negligible compared to the data integrity guarantees it provides.

6. TypeORM Implementation

6.1 Lookup Entity (LookupCode.ts)

Define two @PrimaryColumn fields to create the composite primary key, and use jsonb for multilingual names:

import { Column, Entity, Index, PrimaryColumn } from 'typeorm';

@Entity({ name: 'lookup_codes', database: AppDatabases.APP_CORE })
@Index(['category'])
export class LookupCode {
    // Part 1 of Composite Primary Key
    @PrimaryColumn({ type: 'varchar', length: 50 })
    category: string;

    // Part 2 of Composite Primary Key
    @PrimaryColumn({ type: 'varchar', length: 20 })
    code: string;

    // Multilingual display name as JSONB
    @Column({ type: 'jsonb' })
    display_name: {
        en: string;
        [key: string]: string;
    };

    @Column({ default: true })
    is_active: boolean;
}

6.2 Consumer Entity (Resource.ts)

Implement Category Locking using default values and multi-column @JoinColumn:

import { Column, Entity, JoinColumn, ManyToOne, PrimaryGeneratedColumn } from 'typeorm';

import { LookupCode } from './LookupCode';

@Entity({ name: 'resources', database: AppDatabases.APP_CORE })
export class Resource {
    @PrimaryGeneratedColumn()
    id: number;

    @Column({ type: 'varchar', length: 100 })
    first_name: string;

    @Column({ type: 'varchar', length: 100 })
    last_name: string;

    // ========================================================
    // 1. Title Relationship
    // ========================================================

    // Shadow Column: locked to category 'TITLE'
    @Column({ name: 'title_cat', default: 'TITLE' })
    title_category: string;

    @Column()
    title_code: string;

    // Composite JOIN: uses both title_cat and title_code
    @ManyToOne(() => LookupCode)
    @JoinColumn([
        { name: 'title_cat', referencedColumnName: 'category' },
        { name: 'title_code', referencedColumnName: 'code' },
    ])
    title: LookupCode;

    // ========================================================
    // 2. Blood Type Relationship
    // ========================================================

    // Shadow Column: locked to category 'BLOOD_TYPE'
    @Column({ name: 'blood_cat', default: 'BLOOD_TYPE' })
    blood_category: string;

    @Column({ nullable: true })
    blood_code: string;

    @ManyToOne(() => LookupCode)
    @JoinColumn([
        { name: 'blood_cat', referencedColumnName: 'category' },
        { name: 'blood_code', referencedColumnName: 'code' },
    ])
    blood: LookupCode;
}

6.3 Usage in Application Code

Inserting:

Developers only need to set the code — the category is handled automatically by the entity default:

const resource = new Resource();
resource.first_name = 'John';
resource.last_name = 'Smith';

// Just specify the code — category is auto-filled by Entity default
resource.title_code = 'MR';
resource.blood_code = 'A';

await resourceRepository.save(resource);

Querying with relation:

TypeORM resolves the composite JOIN automatically when relations is specified:

const result = await resourceRepository.findOne({
    where: { first_name: 'John' },
    relations: {
        title: true, // JOINs lookup_codes using both category and code
        blood: true,
    },
});

if (result?.title) {
    console.log(result.title.display_name.en); // Output: "Mr."
}

7. Decision Framework

When a developer needs to add new reference data, use this decision flow to choose between lookup_codes and a dedicated master data table:

1. Question: Is this data used only to display a label in a dropdown?

   • If Yes → Store in lookup_codes

2. Question: Does this data have special attributes required for computation (e.g., price, weight, formula)?

   • If Yes → Create a dedicated table in the master data group

3. Question: Is the dataset larger than 1,000 rows and should the client never load it all at once?

   • If Yes → Create a dedicated table with a Search API

Developer Note

Separating Lookup from Master Data keeps client applications lean. A mobile app or frontend should not need to load 50,000 catalog items just to populate a 10-item title dropdown.

---

Related Documentation

• Database Architecture Overview — Three-database architecture and read model pattern
• Data Replication for Reporting — CQRS read model for reporting tools
• Database Relation Design (1:N vs M:N) — Relationship design decisions