Lena Fischer·
Healthcare schema with a GiST exclusion constraint that blocks provider double-booking in the DB
Creates comprehensive database designs with normalized entity-relationship models, indexing strategies, and migration scripts for complex domains.
Entity-Relationship & Data Model Design
You are a Data Architect who has designed database schemas for complex enterprise systems. Create a comprehensive data model.
**Domain**: {{domain}} (e-commerce, healthcare, fintech, social media, logistics, education)
**Database Technology**: {{database_technology}} (PostgreSQL, MySQL, MongoDB, DynamoDB, Cassandra, Neo4j)
**Scale Expectations**: {{scale_expectations}} (number of entities, relationships, query patterns, growth rate)
**Access Patterns**: {{access_patterns}} (most common queries, write-heavy vs read-heavy, reporting needs)
Design the complete data model:
1. **Entity-Relationship Diagram** - Mermaid ERD or textual representation showing all entities, relationships, cardinalities (1:1, 1:N, M:N)
2. **Entity Definitions** - Each entity with: business definition, attributes (name, type, nullable, default, constraints), business rules
3. **Complete DDL** - Database-specific CREATE TABLE statements with: data types, constraints, indexes, foreign keys, check constraints, comments
4. **Normalization Analysis** - Current normal form (3NF/BCNF), denormalization opportunities with justification, calculated/derived fields
5. **Indexing Strategy** - Primary keys, foreign key indexes, composite indexes, partial indexes, covering indexes for frequent queries, GIN/GIST indexes
6. **Partitioning Design** - Partition key selection, partition type (range/hash/list), partition pruning benefits, archival strategy
7. **Soft Delete Strategy** - deleted_at pattern vs status field, cascade behavior, unique constraints with deleted_at handling
8. **Audit Trail Design** - created_at/updated_at/created_by/updated_by, separate audit table with triggers, temporal tables, CDC
9. **Multi-Tenancy** - tenant_id column approach, shared vs dedicated schema, tenant isolation in queries, tenant data limits
10. **Migration Scripts** - Up migration (CREATE), down migration (DROP/ALTER), seed data, data transformation for existing data
11. **Query Patterns** - The 10 most common queries with EXPLAIN ANALYZE expectations, optimized query examples
12. **Data Integrity** - Foreign key constraints, check constraints, trigger-based validation, application-level validation rules
13. **Schema Evolution** - Versioning strategy, backward-compatible migrations, column deprecation process, breaking change detection
Include actual SQL DDL that can be executed directly, not pseudocode. Design for the specific database technology requested.
Ergebnisse
Data model for a **healthcare** appointments domain on **PostgreSQL** (read-heavy, strict referential integrity, soft-delete + audit required). Executable DDL — drop it straight into a migration.
```sql
CREATE TABLE patient (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
mrn text NOT NULL, -- medical record number
full_name text NOT NULL,
date_of_birth date NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
deleted_at timestamptz, -- soft delete
CONSTRAINT mrn_unique_when_live EXCLUDE (mrn WITH =) WHERE (deleted_at IS NULL)
);
CREATE TABLE appointment (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
patient_id uuid NOT NULL REFERENCES patient(id),
provider_id uuid NOT NULL REFERENCES provider(id),
scheduled_at timestamptz NOT NULL,
status text NOT NULL DEFAULT 'booked'
CHECK (status IN ('booked','completed','cancelled','no_show')),
created_at timestamptz NOT NULL DEFAULT now(),
-- no double-booking a provider for the same slot
CONSTRAINT no_provider_overlap EXCLUDE USING gist
(provider_id WITH =, tstzrange(scheduled_at, scheduled_at + interval '30 min') WITH &&)
);
-- Covering index for the hottest query: a provider's upcoming day
CREATE INDEX idx_appt_provider_day
ON appointment (provider_id, scheduled_at)
INCLUDE (status, patient_id)
WHERE status = 'booked';
```
**Design notes:** the partial unique constraint lets a soft-deleted MRN be reissued while keeping live MRNs unique. The GiST `EXCLUDE` constraint enforces "no overlapping appointments per provider" *in the database* — far safer than application checks under concurrency. Audit is handled by a trigger writing old/new row JSON to an append-only `appointment_audit` table. The model is in 3NF; the only denormalization is the covering index's `INCLUDE` columns to make the day-view an index-only scan.
Modell: Claude Opus 4
27 Likes12 SavesScore: 20
1 Kommentar
Emily Chen·
Bookmarked — exactly the system design approach I was missing.