Database Standards & Schema Discipline

In Plain Language

Database standards guarantee data integrity, predictable query performance, and strict compliance with health data privacy laws. By enforcing strict snake_case schema naming, version-controlled forward-and-rollback migrations, foreign key constraints, and field-level encryption for ePHI, our data tier remains stable, high-performing, and auditor-defensible.

Schema & Column Naming Conventions

All relational databases (PostgreSQL, MySQL) must follow strict snake_case naming conventions to ensure consistency across ORM models and ANSI SQL queries:

Table Names: Plural snake_case

user_accounts, clinical_trials, order_line_items. Avoid singular table names.

Primary Keys: Explicit UUID or ID

Use id UUID PRIMARY KEY or BIGSERIAL. Foreign keys must be named {referenced_table_singular}_id (e.g. patient_id).

Timestamps: Explicit UTC Suffix

Always include created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() and updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW().

Migration Versioning & Zero-Downtime Rollbacks

Direct manual modifications to production databases are strictly prohibited. Every schema change must be codified in a version-controlled migration script containing both forward (up) and reverse (down) execution blocks:

migrations/20260820_create_patient_vitals.sql
-- UP MIGRATION
CREATE TABLE patient_vitals (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    patient_id UUID NOT NULL REFERENCES patient_records(id) ON DELETE CASCADE,
    heart_rate_bpm INT NOT NULL CHECK (heart_rate_bpm BETWEEN 20 AND 250),
    recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_patient_vitals_patient_id ON patient_vitals(patient_id);
CREATE INDEX idx_patient_vitals_recorded_at ON patient_vitals(recorded_at DESC);

-- DOWN MIGRATION (ROLLBACK)
DROP TABLE IF EXISTS patient_vitals;

Indexing Strategies & Query Optimization

  • Foreign Keys: Every foreign key column must have an explicit B-tree index to prevent table-scan locks during JOIN operations.
  • Composite Queries: Where queries filter by status and sort by date, create composite indexes: CREATE INDEX idx_orders_status_date ON orders(status, created_at DESC).
  • Parameter Binding: Always use parameterized queries (e.g. $1, ?) or ORM query builders to completely eliminate SQL injection risks.

ePHI Protection & Field-Level Encryption

Under HIPAA Security Rule § 164.312(a)(2)(iv), sensitive patient identifiers (SSN, medical record number, diagnostic notes) must be encrypted before storage using authenticated column-level encryption (AES-256-GCM) with encryption keys managed externally via KMS/Vault.

Try This with AI: SQL Schema & Migration Optimizer

Copy this prompt to evaluate database schemas for performance and compliance.

Analyze this SQL table schema for PostgreSQL 16. Identify missing foreign key indexes, suggest composite index optimizations for high-throughput queries, and verify column-level encryption annotations for HIPAA ePHI compliance.

Community Discussion & Feedback

Attributed peer feedback and official Netspective architecture notes.

Was this documentation helpful?(100% found this helpful • 0 ratings)

Leave Feedback or Question

○ Loading user info...
0/2000 chars

Discussion (0)

Loading discussion thread...