Common SQL Antipatterns and Why They Hurt

1. Why structural antipatterns are so persistent

An antipattern differs from a simple bug in that it works at first. Queries return correct results, the application runs, the first tests pass green. The problem only shows up as data volume grows, user count rises, or a new feature needs to build on the existing structure and suddenly costs a disproportionate amount of effort. This delay between decision and visible damage is exactly what makes antipatterns so hard to spot before they become a real problem.

A second reason is that many of these patterns arise from an understandable, short-term motivation: a generic solution that supposedly covers every future requirement, a quick column that saves a migration, a missing index that simply got forgotten during prototyping. Only in hindsight does it become clear that the supposed flexibility or time savings came at a substantial follow-up cost.

This collection deliberately focuses on structural antipatterns in schema design, not on individual flawed queries. The distinction matters: a bad query can be fixed in isolation, a bad schema runs through the entire application and requires a coordinated migration.

2. Antipattern 1: EAV misuse for data with a known schema

The entity-attribute-value model stores attributes not as dedicated columns but as rows in a generic table with columns entity_id, attribute_name, and value. For genuinely dynamic attributes unknown at design time, say user-defined product properties in a catalog system, this model is legitimate. It gets misused when developers deploy it to avoid schema changes altogether, even for attributes that are known and stable from the start, like a record's name, price, or creation date.

The symptoms show up quickly: every meaningful query requires multiple self-joins against the same table, one per queried attribute. Type checking and NOT NULL constraints stop working, because the value column has to store everything as text or a generic type. Aggregations like averages become a nightmare, because numeric values first need to be parsed out of text. The optimizer can barely maintain meaningful statistics over such a generic structure.

The refactoring path leads back to a classic relational schema with dedicated columns for known, stable attributes. Only genuine extensibility unknown at design time justifies EAV, and even then a hybrid approach often pays off: fixed columns for the core, a limited EAV structure or a JSON column type only for the truly variable extra attributes.

-- Antipattern: every attribute requires another join
SELECT e.entity_id,
       n.value AS name,
       p.value AS price,
       d.value AS created_at
FROM entities e
LEFT JOIN attribute_values n ON n.entity_id = e.entity_id AND n.attribute_name = 'name'
LEFT JOIN attribute_values p ON p.entity_id = e.entity_id AND p.attribute_name = 'price'
LEFT JOIN attribute_values d ON d.entity_id = e.entity_id AND d.attribute_name = 'created_at';

-- Refactoring: known, stable attributes as dedicated columns
SELECT entity_id, name, price, created_at
FROM entities;

3. Antipattern 2: columns instead of rows for repeated values

This pattern often shows up in tables with columns like phone_1, phone_2, phone_3, or tag_1 through tag_5. The motivation is usually to avoid a one-to-many relationship because it means additional joins. The result is a fixed, arbitrary upper bound on the number of values and a query that has to be written separately for every possible column when searching for a given value, regardless of which column it lives in.

Concrete symptoms are long OR chains in WHERE clauses that query each numbered column individually, plus application code that simply runs out of room to store a value once it hits the sixth tag or the fourth phone number. UPDATE statements also become unwieldy, because adding a value first requires checking which of the numbered columns is still free.

The clean path is a separate table with one row per value and a foreign key back to the main table, a classic one-to-many model. That makes the number of values unbounded, simplifies queries down to a single WHERE condition, and allows an index directly on the value column, which is practically impossible with the numbered variant.

-- Antipattern: fixed upper bound and unwieldy search
-- Table: contacts(id, phone_1, phone_2, phone_3)
SELECT id FROM contacts
WHERE phone_1 = '+491701234567'
   OR phone_2 = '+491701234567'
   OR phone_3 = '+491701234567';

-- Refactoring: dedicated table, unbounded count, indexable
CREATE TABLE contact_phones (
  id         BIGINT PRIMARY KEY,
  contact_id BIGINT NOT NULL REFERENCES contacts(id),
  phone      VARCHAR(32) NOT NULL
);
CREATE INDEX idx_contact_phones_phone ON contact_phones(phone);

SELECT contact_id FROM contact_phones WHERE phone = '+491701234567';

4. Antipattern 3: foreign keys without an accompanying index

A foreign key constraint guarantees referential integrity, but doesn't automatically create an index on the referencing column, that depends on the specific database system and is by no means universal. Without that index, every join across the foreign key relationship turns into a full table scan on the referencing table instead of a targeted index lookup.

The symptom often only surfaces as data volume grows: a query that ran unnoticeably fast at a thousand rows becomes noticeably slow at a million rows, without any change to the SQL code itself. This is especially insidious for DELETE operations on the referenced table, because the database has to check whether dependent rows exist before deleting, a check that also triggers a full scan without an index on the foreign key column.

The fix is usually straightforward: add an index on every foreign key column, unless the database system already creates one automatically. For composite foreign keys, the column order in the index should match the most common query direction. A regular check for foreign keys without an accompanying index belongs in every schema review routine.

-- Foreign key without index: join and DELETE check become table scans
ALTER TABLE order_items
  ADD CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id) REFERENCES orders(id);

-- Missing but necessary index
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

5. Antipattern 4: polymorphic associations without database enforcement

In a polymorphic association, a column like commentable_id references either the posts table or the photos table, depending on a second column commentable_type. At the application level this feels flexible, at the database level it's problematic: a classic foreign key constraint can't conditionally point to two different target tables, so referential integrity has to be entirely rebuilt in application logic.

The symptom shows up as orphaned records once somewhere in the application logic a delete operation skips the check or a bug sets the type column incorrectly. Such inconsistencies often only get noticed when a report suddenly shows comments on posts that no longer exist, and tracking down the cause is tedious, because the database itself doesn't raise any error about it.

A clean replacement is a dedicated join table per target entity, say post_comments and photo_comments, each with a real foreign key constraint. Where a combined query across both types is needed, that can be solved with a UNION or a view, without sacrificing referential integrity at the database level.

6. Antipattern 5: comma-separated values in a single column

A column like tags containing 'sql,performance,index' saves an extra table at first glance. In practice it creates a handful of solid problems: searching for a single tag requires a LIKE query with wildcards that can't use a regular index and therefore almost always means a full table scan. Removing or renaming a tag requires string manipulation instead of a simple UPDATE or DELETE.

Referential integrity is lost too: nothing stops the application from inserting a typo like 'perfromance', which then exists as its own, never-again-findable value in the system. Aggregations like counting how often each tag is used require expensive string splitting, which is supported to varying degrees depending on the database system and is rarely performant.

The solution, as with repeated columns, is a classic join table, here item_tags with columns item_id and tag, or for a fixed set of possible values a normalized tags table with a foreign key relationship. For database systems with native array or JSON support, a typed array field with a matching index can be an acceptable middle ground in individual cases, a comma-separated text column is not.

-- Antipattern: search requires a wildcard scan
SELECT id FROM items WHERE tags LIKE '%performance%';

-- Refactoring: normalized join table
CREATE TABLE item_tags (
  item_id BIGINT NOT NULL REFERENCES items(id),
  tag     VARCHAR(64) NOT NULL,
  PRIMARY KEY (item_id, tag)
);
CREATE INDEX idx_item_tags_tag ON item_tags(tag);

SELECT item_id FROM item_tags WHERE tag = 'performance';

7. Symptoms that point to a structural antipattern

A recurring warning sign is application code that contains an unusual amount of logic for cleaning, parsing, or assembling data that the database itself should really be handling. If an application regularly has to split text to extract multiple values from a column, or repeats the same check that a foreign key constraint would handle automatically, that points to a structural problem in the schema, not a bug in the application code.

A second signal is queries that slow down disproportionately as data volume grows, even though the number of returned rows stays constant. That almost always points to a missing index or a structure that doesn't allow efficient index access, as is typical for EAV tables or comma-separated columns.

A third signal is organizational: if every new requirement to store an additional attribute demands an ALTER TABLE migration, and teams start dreading or avoiding those migrations, that's often a sign that an antipattern already exists somewhere in the schema as a supposed shortcut that was meant to solve exactly this problem.

8. Strategy for a safe, incremental refactoring

A schema refactoring in a production application should never happen as a single, large big-bang step. The proven path is to build the new structure alongside the old one, gradually move application code to read from the new structure, while writes go into both structures during the transition, and only remove the old structure after the migration is fully complete.

For each of the patterns described above, it's worth taking stock before starting: how many rows are affected, which parts of the application currently read and write to it, and are there reports or external integrations relying on the old structure. That inventory prevents a migration from unexpectedly breaking a remote report that nobody had on their radar anymore.

Automated tests that compare behavior before and after the migration are especially valuable here, because a schema refactoring naturally touches many queries at once. Where possible, it also pays to run the migration in several small, independently deployable steps instead of rebuilding all affected tables in a single release.

9. When an antipattern may be a deliberate, acceptable tradeoff

Not every deviation from textbook design is automatically a mistake. A prototype that gets discarded within a few weeks may benefit more from fast development than from a clean schema. A comma-separated column for an internal debug field that never appears in a WHERE clause is harmless, because none of the described symptoms ever materialize.

What matters is that these exceptions are made deliberately and documented, rather than arising from ignorance. A comment in the migration script explaining why the norm was deliberately broken at this point, and under what conditions that should be reconsidered, saves the next developer a lot of time and prevents a deliberate short-term decision from accidentally becoming a permanent, unreflected standard.

The decisive difference between a legitimate shortcut and a genuine antipattern lies less in the structure itself than in the deliberate tradeoff behind it, and the willingness to revisit it once the underlying conditions change.

10. Summary

11. FAQ: SQL Antipatterns: Key Takeaways