MySQL Schema Anti-Patterns: Common Database Design Mistakes

Some schema mistakes come back in project after project. Each one looks convenient at first and becomes expensive later. Here are the ones to recognise, with a real demonstration of the damage and the fix.

1. Comma-separated lists in a column

Storing several values in one column (“tags”, “category ids”, “roles”) breaks the first normal form (Normalization).

query.sql

SQL

CREATE TABLE posts_csv (id INT PRIMARY KEY, title VARCHAR(30), tags VARCHAR(100));
INSERT INTO posts_csv VALUES (1, 'Intro to SQL', 'sql,beginner'), (2, 'NoSQL guide', 'nosql,databases'), (3, 'Indexes', 'sql,performance');

SELECT title FROM posts_csv WHERE tags LIKE '%sql%' ORDER BY id;

Output

OUTPUT

+--------------+
| title        |
+--------------+
| Intro to SQL |
| NoSQL guide  |
| Indexes      |
+--------------+
3 rows in set

The search for “sql” also returns the NoSQL guide. FIND_IN_SET is more exact, but it cannot use an index, so it scans every row, and there is no foreign key to make sure a tag exists:

query.sql

SQL

SELECT title FROM posts_csv WHERE FIND_IN_SET('sql', tags) ORDER BY id;

Output

OUTPUT

+--------------+
| title        |
+--------------+
| Intro to SQL |
| Indexes      |
+--------------+
2 rows in set

Fix: a junction table, one row per post and tag (Many-to-Many):

query.sql

SQL

CREATE TABLE tags (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE post_tags (post_id INT NOT NULL, tag_id INT NOT NULL, PRIMARY KEY (post_id, tag_id), KEY idx_tag (tag_id), FOREIGN KEY (post_id) REFERENCES posts_csv (id), FOREIGN KEY (tag_id) REFERENCES tags (id));
INSERT INTO tags (name) VALUES ('sql'), ('nosql'), ('beginner'), ('databases'), ('performance');
INSERT INTO post_tags VALUES (1, 1), (1, 3), (2, 2), (2, 4), (3, 1), (3, 5);

SELECT p.title FROM posts_csv p JOIN post_tags pt ON pt.post_id = p.id JOIN tags t ON t.id = pt.tag_id WHERE t.name = 'sql' ORDER BY p.id;

Output

OUTPUT

+--------------+
| title        |
+--------------+
| Intro to SQL |
| Indexes      |
+--------------+
2 rows in set

An exact match, an index on the lookup, and a foreign key that stops typos.

2. Entity-Attribute-Value (EAV)

An EAV table stores every attribute of everything as a row of (entity_id, attribute, value), to avoid ever changing the schema:

query.sql

SQL

CREATE TABLE product_attrs (product_id INT, attr VARCHAR(30), val VARCHAR(50));
INSERT INTO product_attrs VALUES
(1, 'name', 'Pen'), (1, 'color', 'blue'), (1, 'price', '12.50'),
(2, 'name', 'Notebook'), (2, 'pages', '200'), (2, 'price', '80.00'),
(3, 'name', 'Marker'), (3, 'color', 'black'), (3, 'price', '45.00');

-- "name and price of products that cost less than 50": one self-join (or pivot) per attribute
SELECT n.val AS name, p.val AS price
FROM product_attrs n
JOIN product_attrs p ON p.product_id = n.product_id AND p.attr = 'price'
WHERE n.attr = 'name' AND p.val < 50
ORDER BY n.product_id;

Output

OUTPUT

+--------+-------+
| name   | price |
+--------+-------+
| Pen    | 12.50 |
| Marker | 45.00 |
+--------+-------+
2 rows in set

The query works, but look at what you give up: every value is text (so '80.00' < 50 compares the number 80 with 50 through a conversion, and '9' > '10' as text), nothing can be NOT NULL or UNIQUE, there are no real foreign keys, and each new column in the answer needs another self-join. Sorting by price as a number needs casts everywhere:

query.sql

SQL

SELECT product_id, val FROM product_attrs WHERE attr = 'price' ORDER BY val;

Output

OUTPUT

+------------+-------+
| product_id | val   |
+------------+-------+
|          1 | 12.50 |
|          3 | 45.00 |
|          2 | 80.00 |
+------------+-------+
3 rows in set

(80.00 sorts after 45.00 only because they have the same number of digits. With a price of 9.50, the text order would put it last.)

Fix: real columns for the attributes every item has (name, price), and for the truly variable rest use a JSON column (JSON vs Columns):

query.sql

SQL

CREATE TABLE products (
    id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, price DECIMAL(8,2) NOT NULL, attrs JSON NULL,
    CONSTRAINT chk_attrs_object CHECK (attrs IS NULL OR JSON_TYPE(attrs) = 'OBJECT')
);
INSERT INTO products VALUES (1, 'Pen', 12.50, '{"color": "blue"}'), (2, 'Notebook', 80.00, '{"pages": 200}'), (3, 'Marker', 45.00, '{"color": "black"}');
SELECT name, price, attrs->>'$.color' AS color FROM products WHERE price < 50 ORDER BY id;

Output

OUTPUT

+--------+-------+-------+
| name   | price | color |
+--------+-------+-------+
| Pen    | 12.50 | blue  |
| Marker | 45.00 | black |
+--------+-------+-------+
2 rows in set

3. FLOAT or DOUBLE for money

Binary floating point cannot store most decimal fractions exactly:

query.sql

SQL

1
2
3

CREATE TABLE amounts (id INT, as_float FLOAT, as_decimal DECIMAL(8,2));
INSERT INTO amounts VALUES (1, 0.1, 0.1), (2, 0.2, 0.2);
SELECT SUM(as_float) AS float_total, SUM(as_float) = 0.3 AS float_equals_0_3, SUM(as_decimal) AS decimal_total, SUM(as_decimal) = 0.3 AS decimal_equals_0_3 FROM amounts;

Output

OUTPUT

+---------------------+------------------+---------------+--------------------+
| float_total         | float_equals_0_3 | decimal_total | decimal_equals_0_3 |
+---------------------+------------------+---------------+--------------------+
| 0.30000000447034836 |                0 |          0.30 |                  1 |
+---------------------+------------------+---------------+--------------------+
1 row in set

The FLOAT total is not 0.3, and the equality test fails. Over millions of rows, such errors become real money. Fix: DECIMAL(10,2), or whole minor units in an INT or BIGINT (paise, cents) (DECIMAL and FLOAT).

4. Dates stored as text

query.sql

SQL

1
2
3

CREATE TABLE events_text (id INT, happened VARCHAR(12));
INSERT INTO events_text VALUES (1, '9/1/2026'), (2, '10/1/2026'), (3, '2/1/2026'), (4, '31/02/2026');
SELECT happened FROM events_text ORDER BY happened;

Output

OUTPUT

+------------+
| happened   |
+------------+
| 10/1/2026  |
| 2/1/2026   |
| 31/02/2026 |
| 9/1/2026   |
+------------+
4 rows in set

Text sorting puts 10/1/2026 first, and nothing noticed the impossible date 31/02/2026. A real DATE column refuses it:

query.sql

SQL

1
2

CREATE TABLE events_date (id INT, happened DATE);
INSERT INTO events_date VALUES (1, '2026-02-31');

Output

OUTPUT

ERROR 1292 (22007): Incorrect date value: '2026-02-31' for column 'happened' at row 1

A DATE column also gives you date arithmetic, BETWEEN, indexes that work for ranges, and the date functions (Date and Time Types).

5. Polymorphic associations

“A comment can belong to a post or a photo” is often modelled with two generic columns, a type and an id:

query.sql

SQL

CREATE TABLE posts (id INT PRIMARY KEY);
CREATE TABLE photos (id INT PRIMARY KEY);
INSERT INTO posts VALUES (1);

CREATE TABLE comments_poly (id INT AUTO_INCREMENT PRIMARY KEY, target_type VARCHAR(10) NOT NULL, target_id INT NOT NULL, body VARCHAR(100));
INSERT INTO comments_poly (target_type, target_id, body) VALUES ('post', 1, 'fine'), ('post', 999, 'points to nothing');
SELECT COUNT(*) AS comments_without_a_post FROM comments_poly c LEFT JOIN posts p ON c.target_type = 'post' AND p.id = c.target_id WHERE c.target_type = 'post' AND p.id IS NULL;

Output

OUTPUT

+-------------------------+
| comments_without_a_post |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set

A foreign key cannot point to “a post or a photo”, so the database cannot stop a comment that points at nothing. Fix: one real foreign key column per parent, and a CHECK that exactly one is set:

query.sql

SQL

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT NULL,
    photo_id INT NULL,
    body VARCHAR(100) NOT NULL,
    FOREIGN KEY (post_id) REFERENCES posts (id),
    FOREIGN KEY (photo_id) REFERENCES photos (id),
    CONSTRAINT chk_one_parent CHECK ((post_id IS NOT NULL) + (photo_id IS NOT NULL) = 1)
);
INSERT INTO comments (post_id, body) VALUES (1, 'fine');
INSERT INTO comments (post_id, photo_id, body) VALUES (1, 5, 'two parents');

Output

OUTPUT

ERROR 3819 (HY000): Check constraint 'chk_one_parent' is violated.

The second insert fails the check constraint (error 3819). (If a third kind of parent is added later, you add a column. That is a small, explicit change.)

6. The “one big table”

Putting everything about customers, orders and products into a single wide table brings back every anomaly of Normalization, plus many NULLs for columns that do not apply to a row. Split it by entity.

7. Other things to avoid

Anti-pattern	Problem	Better
Magic values: `-1` or `0` for “unknown”, `'0000-00-00'` for no date	Break sums, joins and date logic	`NULL` and a `NOT NULL` where a value is required
Repeating columns: `phone1`, `phone2`, `phone3`	Hard to query, a hard limit	A child table of phones
A status column of free text	Typos create new “statuses”	`ENUM`, or a lookup table with a foreign key
Storing big files (images, PDFs) in the database	Bloats backups and the buffer pool	Files in object storage, the path or key in MySQL
No primary key	Slow replication, no stable row identity, unsafe updates	Always a primary key
No foreign keys “for speed”	Orphan rows accumulate	Use them. The cost is small compared with the cost of bad data
Reusing one column for different meanings	Nobody knows what it holds	One column, one meaning
Over-indexing every column	Slows writes, wastes memory (Index Design Rules)	Index for real queries

lightbulb

Convenience now, cost later

Each anti-pattern saves a few minutes at design time and costs hours every time somebody has to query, fix or migrate the data. If a shortcut makes the database unable to enforce a rule, it is the wrong shortcut.