Data Modeling & Schema Design
Schema Design Antipatterns
Schema antipatterns trade explicit relational structure for strings, generic metadata, ambiguous references, or meaningless keys. They often simplify one write path while disabling constraints and making ordinary queries, migrations, and corrections fragile.
- Multi-valued attributes hide relationships inside one scalar column.
- EAV moves the schema into rows and weakens types and constraints.
- Polymorphic associations make one foreign key pretend to reference several tables.
- A surrogate ID is not a substitute for a business key.
- Numbered columns and cloned tables encode data values in metadata, forcing schema changes when the number of values grows.
- Antipatterns can have narrow legitimate uses, but the lost guarantees and exit strategy must be explicit.
Concrete failures and relational replacements
| Antipattern | Tempting design | Failure | Replacement |
|---|---|---|---|
| Multi-valued attribute / Jaywalking | products.category_ids = '4,9,12' | No element-level FK; substring searches and read-modify-write updates | product_categories(product_id, category_id) |
| EAV | product_attributes(product_id, name, value) | Types, required fields, ranges, and cross-attribute rules become dynamic code | Typed columns or subtype/detail tables; JSON only for genuinely open attributes |
| Polymorphic association | attachments(target_type, target_id) | No declarative FK to orders or products | Common attachment_targets supertype or separate order_attachments and product_attachments |
| Key misuse | Only order_item_id is unique | Same product line can be inserted twice against the business rule | Declare the surrogate plus the actual candidate key |
| Multicolumn attributes | phone1, phone2, phone3 | Fixed limit, repetitive queries, positional gaps | customer_phones(customer_id, phone, kind) |
| Metadata Tribbles | orders_2025, orders_2026 | New DDL and UNION edits every period | One orders relation; use physical partitioning transparently if needed |
-- Antipattern: category_ids contains values such as '4,9,12'
-- SELECT * FROM products WHERE category_ids LIKE '%9%';
CREATE TABLE product_categories (
product_id BIGINT NOT NULL REFERENCES products(product_id),
category_id BIGINT NOT NULL REFERENCES categories(category_id),
assigned_at TIMESTAMP NOT NULL,
PRIMARY KEY (product_id, category_id)
);