TopolearnThese patterns appear in nearly every product schema, and each has a well-known trap.
A few recurring problems and their usual solutions:
- Soft delete: mark rows
deleted_atinstead of removing them, for undo and audit. Cost: every query must filter it out, and unique constraints must ignore deleted rows (use a partial unique indexWHERE deleted_at IS NULL). Often an archive table is cleaner. - History / audit: keep previous versions in a
*_historytable withvalid_from/valid_to, written by a trigger or by the application. Lets you answer "what was the price on March 3rd?". - Status and enums: a lookup table or a
CHECK (status IN (...))keeps values valid; a lookup table is easier to extend and can carry labels. - Polymorphic association: a comment that can belong to a post or a photo. The tempting
(commentable_type, commentable_id)pair can't have a foreign key. Safer: separate nullable foreign keys with aCHECKthat exactly one is set, or a separate junction table per parent type. - Entity-attribute-value (EAV): rows of
(entity, attribute, value)for arbitrary fields. Flexible, but you lose types, constraints and readable queries. AJSONcolumn for genuinely free-form attributes is usually better, with real columns for anything you filter on. - Trees: an adjacency list (
parent_id) queried with recursive CTEs covers most hierarchies.
Choose the pattern that keeps the database able to enforce your rules.
Example
CREATE TABLE post (id int PRIMARY KEY);
CREATE TABLE photo (id int PRIMARY KEY);
-- polymorphic comment with real foreign keys: exactly one parent
CREATE TABLE comment (
id int PRIMARY KEY,
post_id int REFERENCES post(id),
photo_id int REFERENCES photo(id),
body text NOT NULL,
deleted_at timestamptz, -- soft delete
CHECK (num_nonnulls(post_id, photo_id) = 1)
);
-- usernames unique among live rows only
CREATE TABLE member (id int PRIMARY KEY, username text NOT NULL, deleted_at timestamptz);
CREATE UNIQUE INDEX member_username_live ON member (username) WHERE deleted_at IS NULL;
INSERT INTO member VALUES (1, 'ani', now()), (2, 'ani', NULL); -- ok
INSERT INTO member VALUES (3, 'ani', NULL);
-- ERROR: duplicate key value violates unique constraint "member_username_live"Remember
- Soft deletes need filtered queries and partial unique indexes
- Polymorphic links: separate FKs plus a CHECK keep referential integrity
- Prefer JSON columns to EAV for free-form attributes
Watch out for
- A (type, id) polymorphic pair with no foreign key, which silently accumulates orphans.