Eight ERD anti-patterns: diagnose them with writes and queries
Review EAV, string types, nullability, cycles, growing keys, many-to-many relations, CSV values, and ambiguous tables with a tag-model exercise.
Look for these warning signs before a model is implemented: comma-separated lists in one column; repeated numbered columns; nullable values used to represent different entity types; a table with no primary key; a foreign key without an index where joins are frequent; money stored as floating point; status values with no documented transitions; and “current” reference data reused to represent past transactions.
None of these is automatically wrong, but each needs an explicit reason. A JSON column can suit genuinely flexible attributes. A nullable relationship can be correct before an assignment happens. Trouble starts when a shortcut silently becomes the only source of a business fact and cannot be queried, constrained, or migrated safely.
A tag filter reveals a modeling shortcut
Storing sql,mysql in a post column looks convenient until someone searches for sql. A substring match can also select nosql. A tag rename becomes a parsing task, and duplicate tags have no simple database constraint. Model the association as its own table when tags are independently managed.
CREATE TABLE post_tags (
post_id BIGINT NOT NULL,
tag_id BIGINT NOT NULL,
PRIMARY KEY (post_id, tag_id),
FOREIGN KEY (post_id) REFERENCES posts(post_id),
FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
);The fragment assumes posts and tags already have the corresponding primary keys. Add UNIQUE on tags.name if tag names must be unique under your chosen collation. A composite key rejects duplicate associations, while foreign keys reject missing parents.
Use eight review prompts rather than eight blanket bans
| Pattern | Question that exposes the cost |
|---|---|
| EAV for core fields | Can the database validate amount and currency together? |
| Everything stored as text | Can sorting distinguish numbers from their string spelling? |
| Every field nullable | Which missing values describe real workflow states? |
| Circular required parents | Can the first pair of rows be inserted? |
| Growing composite keys | Which downstream references must copy every ancestor? |
| An unmodeled many-to-many relation | Can one association have its own attributes? |
| Comma-separated values | Can sql be found without matching nosql? |
| Generic table names | What business fact does each row represent? |
SELECT p.post_id
FROM posts p
JOIN post_tags pt ON pt.post_id = p.post_id
JOIN tags t ON t.tag_id = pt.tag_id
WHERE t.name = 'sql';The exception must have a reason
Create sql and nosql tags on different posts. The exact-match join should return only the sql post. Try a duplicate association and an unknown tag id; both should be rejected. Decide whether an unused tag remains when its last association is deleted.
JSON can be appropriate for external payloads or genuinely variable, infrequently queried attributes. It becomes expensive when required relationships and frequently filtered fields are hidden inside it. Keep an exception only after documenting how it is queried, validated, and migrated. In YourERD's blog sample, inspect the tag association before adding attributes such as tagging time or author.
Reference and next step
Examples use simplified business rules for teaching. Check the database documentation for the exact enforcement behavior of your chosen engine.