Data Modeling Anti-Patterns: EAV, Comma-Separated Lists, Repeating Columns and the One True Lookup Table

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, Cumulative Update 10).

Guess First: How Many Classics Do We Have?

The marketing team wanted to tag books, and the quickest solution was to add a tags column to the books table. Tags are separated by commas. Six books were tagged and the campaign needs the books tagged "classic":

ALTER TABLE dbo.books ADD tags VARCHAR(200) NULL;
GO
UPDATE dbo.books SET tags = 'classic,romance'      WHERE book_id IN (1, 2);
UPDATE dbo.books SET tags = 'classic, gothic'      WHERE book_id = 5;
UPDATE dbo.books SET tags = 'scifi,classic'        WHERE book_id = 26;
UPDATE dbo.books SET tags = 'scifi,modern-classic' WHERE book_id = 30;
UPDATE dbo.books SET tags = 'scifi,clasic'         WHERE book_id = 28;

SELECT book_id, title, tags
FROM dbo.books
WHERE tags LIKE '%classic%'
ORDER BY book_id;

Guess: does this query return the right number of books? If nobody made a mistake while entering tags, yes; but one person added a space, one book was tagged "modern-classic", and Foundation's tag is missing a letter. The result:

(2 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
book_id  title                      tags
-------  -------------------------  --------------------
1        Pride and Prejudice        classic,romance
2        Emma                       classic,romance
5        Wuthering Heights          classic, gothic
26       Dune                       scifi,classic
30       The Left Hand of Darkness  scifi,modern-classic

(5 rows affected)

Five rows came back; one is wrong (The Left Hand of Darkness, "modern-classic") and one is missing (Foundation, "clasic"). The query could notice neither. In this post we look at such modeling anti-patterns with the same template: why it looks tempting, which query makes it blow up, what the right model is, and when it is really acceptable. The examples run in the dm_09 database, which holds the Bookstore schema of the SQL Temelleri series.

EAV: Flexibility That Turns Everything into Rows

The bookshop starts selling e-books and audiobooks. An e-book has a file size, an audiobook has a narrator and a duration; a printed book has none of these. A common answer to "are we adding a column for every format?" is EAV (Entity-Attribute-Value): every property becomes a row made of the book id, the attribute name and the value.

CREATE TABLE dbo.book_attributes (
    book_id         INT           NOT NULL,
    attribute_name  VARCHAR(50)   NOT NULL,
    attribute_value NVARCHAR(200) NULL,
    CONSTRAINT PK_book_attributes PRIMARY KEY (book_id, attribute_name),
    CONSTRAINT FK_book_attributes_books FOREIGN KEY (book_id) REFERENCES dbo.books (book_id)
);
INSERT INTO dbo.book_attributes (book_id, attribute_name, attribute_value) VALUES
    (26, 'format', N'ebook'),     (26, 'file_size_mb', N'4.2'),
    (28, 'format', N'ebook'),     (28, 'file_size_mb', N'7.8'),
    (30, 'format', N'ebook'),     (30, 'filesize_mb',  N'6.1'),
    (10, 'format', N'ebook'),     (10, 'file_size_mb', N'unknown'),
    (34, 'format', N'audiobook'), (34, 'narrator',     N'Grace Hopper');

SELECT f.book_id, CAST(s.attribute_value AS DECIMAL(9,2)) AS file_size_mb
FROM dbo.book_attributes AS f
JOIN dbo.book_attributes AS s
     ON s.book_id = f.book_id AND s.attribute_name = 'file_size_mb'
WHERE f.attribute_name = 'format' AND f.attribute_value = N'ebook'
  AND CAST(s.attribute_value AS DECIMAL(9,2)) > 5;

(10 rows affected)
Msg 8114, Level 16, State 5, Server b8be943222fa, Line 15
Error converting data type nvarchar to numeric.

Where it looks tempting is clear: a new property needs no schema change. Where it blows up is just as clear: an ordinary question like "e-books larger than 5 MB" needs a self-join and a text-to-number conversion, and a single 'unknown' value brings down the whole query. You can silence the error with TRY_CAST:

SELECT f.book_id, TRY_CAST(s.attribute_value AS DECIMAL(9,2)) AS file_size_mb
FROM dbo.book_attributes AS f
JOIN dbo.book_attributes AS s
     ON s.book_id = f.book_id AND s.attribute_name = 'file_size_mb'
WHERE f.attribute_name = 'format' AND f.attribute_value = N'ebook'
  AND TRY_CAST(s.attribute_value AS DECIMAL(9,2)) > 5;

book_id  file_size_mb
-------  ------------
28       7.80

(1 rows affected)

The query ran, but the result is wrong. The 6.1 MB row of The Left Hand of Darkness was entered as filesize_mb and disappeared without hitting any condition. With EAV the database cannot give the three guarantees it gives for columns: data type (every value is text), mandatory presence (you cannot say an e-book must have a file size) and name (the attribute name is data, and a typo is valid data). Since every property needs its own self-join, screen queries grow quickly too.

The right model is usually plain columns: a nullable column if the property is shared by a few formats, a separate table per format if the formats really differ (a subtype, one of the 1-1 reasons from the 3rd post of the series). If the properties really are flexible, SQL Server 2025's native json type is a better middle ground, because you can put constraints and indexes on the JSON (JavaScript Object Notation) document:

ALTER TABLE dbo.books ADD details JSON NULL;
GO
ALTER TABLE dbo.books ADD
    book_format AS CAST(JSON_VALUE(details, '$.format') AS VARCHAR(20)),
    CONSTRAINT CK_books_details_format
        CHECK (JSON_VALUE(details, '$.format') IN ('paperback', 'hardcover', 'ebook', 'audiobook')),
    CONSTRAINT CK_books_details_file_size
        CHECK (JSON_VALUE(details, '$.fileSizeMb') IS NULL
               OR COALESCE(TRY_CAST(JSON_VALUE(details, '$.fileSizeMb') AS DECIMAL(9,2)), 0) > 0);
GO
CREATE INDEX IX_books_book_format ON dbo.books (book_format);
GO
UPDATE dbo.books SET details = '{"format":"ebook","fileSizeMb":4.2}' WHERE book_id = 26;
UPDATE dbo.books SET details = '{"format":"ebook","fileSizeMb":7.8}' WHERE book_id = 28;
UPDATE dbo.books SET details = '{"format":"audiobook","narrator":"Grace Hopper"}' WHERE book_id = 34;
UPDATE dbo.books SET details = '{"format":"ebook","fileSizeMb":"unknown"}' WHERE book_id = 10;
GO
SELECT book_id, book_format, JSON_VALUE(details, '$.fileSizeMb' RETURNING DECIMAL(9,2)) AS file_size_mb
FROM dbo.books
WHERE book_format = 'ebook';

(1 rows affected)

(1 rows affected)

(1 rows affected)
Msg 547, Level 16, State 1, Server b8be943222fa, Line 4
The UPDATE statement conflicted with the CHECK constraint "CK_books_details_file_size". The conflict occurred in database "dm_09", table "dbo.books", column 'details'.
The statement has been terminated.
book_id  book_format  file_size_mb
-------  -----------  ------------
26       ebook        4.20
28       ebook        7.80

(2 rows affected)

This time 'unknown' did not get into the database. I fell into a small trap while writing the constraint: in my first attempt the condition was TRY_CAST(...) > 0 and 'unknown' got through, because TRY_CAST returns NULL, NULL > 0 becomes UNKNOWN, and CHECK only rejects a FALSE result. That is why the COALESCE is there. The most frequently filtered property (book_format) was pulled out as a computed column and indexed. PostgreSQL's counterpart is jsonb with a GIN (Generalized Inverted Index) index; the details are in the PostgreSQL series.

When is EAV acceptable? When the properties are defined by the end user, not the developer: like every customer adding their own "custom fields" in a SaaS (Software as a Service) product. Even then, keep the property definitions (name, type, mandatory) in a separate metadata table and validate values by their type. For an application that knows its own properties, EAV means taking the schema out of the database and scattering it across application code.

Comma-Separated Lists

The tags column in the guess question is this anti-pattern itself. It is tempting because the screen shows tags separated by commas anyway, and adding one column is easier than adding two tables. Splitting the list and looking for an exact match instead of LIKE fixes the wrong row:

SELECT b.book_id, b.title
FROM dbo.books AS b
CROSS APPLY STRING_SPLIT(b.tags, ',') AS t
WHERE TRIM(t.value) = 'classic'
ORDER BY b.book_id;

book_id  title
-------  -------------------
1        Pride and Prejudice
2        Emma
5        Wuthering Heights
26       Dune

(4 rows affected)

But the missing row is still missing, and every query has to split the text of every row; you cannot build a useful index on tags. Renaming a tag (science-fiction instead of scifi) means editing text in every row, and counting how many books use a tag is a parsing job. This is exactly the repeating group we fixed with 1NF in the 6th post. The right model is a tag table and an M-N link table; while moving existing data, the list is useful one last time:

CREATE TABLE dbo.tags (
    tag_id INT IDENTITY(1,1) NOT NULL,
    name   VARCHAR(50)       NOT NULL,
    CONSTRAINT PK_tags PRIMARY KEY (tag_id),
    CONSTRAINT UQ_tags_name UNIQUE (name)
);
CREATE TABLE dbo.book_tags (
    book_id INT NOT NULL,
    tag_id  INT NOT NULL,
    CONSTRAINT PK_book_tags PRIMARY KEY (book_id, tag_id),
    CONSTRAINT FK_book_tags_books FOREIGN KEY (book_id) REFERENCES dbo.books (book_id),
    CONSTRAINT FK_book_tags_tags FOREIGN KEY (tag_id) REFERENCES dbo.tags (tag_id)
);
CREATE INDEX IX_book_tags_tag_id ON dbo.book_tags (tag_id);

INSERT INTO dbo.tags (name)
VALUES ('classic'), ('romance'), ('gothic'), ('scifi'), ('modern-classic');

INSERT INTO dbo.book_tags (book_id, tag_id)
SELECT b.book_id, t.tag_id
FROM dbo.books AS b
CROSS APPLY STRING_SPLIT(b.tags, ',') AS s
JOIN dbo.tags AS t ON t.name = TRIM(s.value);

SELECT b.book_id, b.tags
FROM dbo.books AS b
WHERE b.tags IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM STRING_SPLIT(b.tags, ',') AS s
      JOIN dbo.tags AS t ON t.name = TRIM(s.value)
      GROUP BY ()
      HAVING COUNT(*) = (SELECT COUNT(*) FROM STRING_SPLIT(b.tags, ',')));

(5 rows affected)

(11 rows affected)
book_id  tags
-------  ------------
28       scifi,clasic

(1 rows affected)

The last query of the migration script lists books whose tags are not all recognized: Foundation's "clasic" was caught here. The same mistake cannot happen in the new model, because book_tags can only point to a tag that exists in tags. When is a list acceptable? When the database never looks inside it and only stores and returns it: a raw line in an import log, or a field from an external system that has to be kept as is. The day you search, count or join inside it, the list should become a table.

Repeating Columns: phone1, phone2, phone3

A customer can have several phones. Adding columns instead of a table is tempting: phone2, phone3. This is the list anti-pattern spread across columns. The question "whose number is this?" needs a search across three columns at once:

ALTER TABLE dbo.customers ADD phone2 VARCHAR(20) NULL, phone3 VARCHAR(20) NULL;
GO
UPDATE dbo.customers SET phone = '+90 532 000 0001', phone2 = '+90 216 000 0101' WHERE customer_id = 1;
UPDATE dbo.customers SET phone = '+90 533 000 0002', phone3 = '+90 532 000 0001' WHERE customer_id = 2;

SELECT c.customer_id, c.first_name, p.slot
FROM dbo.customers AS c
CROSS APPLY (VALUES ('phone', c.phone), ('phone2', c.phone2), ('phone3', c.phone3)) AS p (slot, number)
WHERE p.number = '+90 532 000 0001';

(1 rows affected)

(1 rows affected)
customer_id  first_name  slot
-----------  ----------  ------
1            Ada         phone
2            Alan        phone3

(2 rows affected)

The same number was stored as Ada's first and Alan's third phone, and no constraint prevented it, because you cannot write the rule "a number belongs to a single customer" across three columns. UNIQUE asks for the uniqueness of one column or of a combination of columns; uniqueness across columns is not its job. A fourth phone is an ALTER TABLE, and the query "customers without a phone" is three conditions. The right model is a child table:

CREATE TABLE dbo.customer_phones (
    customer_id INT         NOT NULL,
    phone_type  VARCHAR(10) NOT NULL,
    phone       VARCHAR(20) NOT NULL,
    CONSTRAINT PK_customer_phones PRIMARY KEY (customer_id, phone),
    CONSTRAINT UQ_customer_phones_phone UNIQUE (phone),
    CONSTRAINT FK_customer_phones_customers FOREIGN KEY (customer_id) REFERENCES dbo.customers (customer_id),
    CONSTRAINT CK_customer_phones_type CHECK (phone_type IN ('mobile', 'home', 'work'))
);
INSERT INTO dbo.customer_phones (customer_id, phone_type, phone) VALUES
    (1, 'mobile', '+90 532 000 0001'),
    (1, 'home',   '+90 216 000 0101'),
    (2, 'mobile', '+90 533 000 0002');
INSERT INTO dbo.customer_phones (customer_id, phone_type, phone) VALUES
    (2, 'work',   '+90 532 000 0001');

(3 rows affected)
Msg 2627, Level 14, State 1, Server b8be943222fa, Line 14
Violation of UNIQUE KEY constraint 'UQ_customer_phones_phone'. Cannot insert duplicate key in object 'dbo.customer_phones'. The duplicate key value is (+90 532 000 0001).
The statement has been terminated.

The rule is now a single constraint, and the phone type is a column. When is it acceptable? When the columns really mean different things: billing_address and shipping_address are not a repeating group but two different roles. The test is this: if deleting the number from the column names leaves the same name (phone1, phone2), there is a repeating group; if different names remain, there is not.

The One Big Lookup Table

When lots of small lookup tables pile up in the schema, someone suggests gathering them all in one table: order statuses, payment methods and languages, all in a single lookups table separated by a lookup_type column. It is tempting because a single admin screen, a single cache and a single API (Application Programming Interface) endpoint are enough. The problem is in the foreign key:

CREATE TABLE dbo.lookups (
    lookup_id   INT IDENTITY(1,1) NOT NULL,
    lookup_type VARCHAR(30)       NOT NULL,
    code        VARCHAR(30)       NOT NULL,
    label       NVARCHAR(100)     NOT NULL,
    CONSTRAINT PK_lookups PRIMARY KEY (lookup_id),
    CONSTRAINT UQ_lookups_type_code UNIQUE (lookup_type, code)
);
INSERT INTO dbo.lookups (lookup_type, code, label) VALUES
    ('order_status',   'Pending',   N'Pending'),
    ('order_status',   'Shipped',   N'Shipped'),
    ('payment_method', 'card',      N'Credit card'),
    ('language',       'tr',        N'Turkish');

ALTER TABLE dbo.orders ADD status_id INT NULL
    CONSTRAINT FK_orders_status_lookup REFERENCES dbo.lookups (lookup_id);
GO
UPDATE dbo.orders
SET status_id = (SELECT lookup_id FROM dbo.lookups WHERE lookup_type = 'language' AND code = 'tr')
WHERE order_id = 1;

SELECT o.order_id, l.lookup_type, l.code
FROM dbo.orders AS o
JOIN dbo.lookups AS l ON l.lookup_id = o.status_id;

(4 rows affected)

(1 rows affected)
order_id  lookup_type  code
--------  -----------  ----
1         language     tr

(1 rows affected)

The order's status is now "Turkish". The foreign key only checks that the lookup_id exists; it does not know which type it is. The original orders.status column in the Bookstore schema could not make this mistake, because a CHECK restricted it to five values. If you have to stay with a single table, you can make the type part of the foreign key; a constant persisted computed column is enough for that:

ALTER TABLE dbo.orders ADD
    status_type AS CAST('order_status' AS VARCHAR(30)) PERSISTED,
    status_code VARCHAR(30) NULL;
GO
ALTER TABLE dbo.orders ADD CONSTRAINT FK_orders_status_lookup
    FOREIGN KEY (status_type, status_code) REFERENCES dbo.lookups (lookup_type, code);
GO
UPDATE dbo.orders SET status_code = 'Shipped' WHERE order_id = 1;
UPDATE dbo.orders SET status_code = 'tr' WHERE order_id = 2;

(1 rows affected)
Msg 547, Level 16, State 1, Server b8be943222fa, Line 2
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_orders_status_lookup". The conflict occurred in database "dm_09", table "dbo.lookups".
The statement has been terminated.

Shipped was accepted and tr was rejected. But this is a patch: you add a constant column to every referencing table, and the single label column of lookups leaves no room for type-specific attributes such as a payment method's commission rate or a language's writing direction. The right model is a separate table per concept, or a CHECK constraint for small sets that change together with code; the details of this decision are in the "Enum mı Lookup Tablosu mu?" post of the Çözüm Desenleri series.

The same family includes the polymorphic foreign key: connecting a comment table to books, authors and orders through target_type and target_id columns. Unlike the one big lookup table, here one column points to several tables, and again no foreign key can be defined. The alternatives (a separate nullable foreign key per target plus a CHECK, a shared supertype table, a link table per target) are covered in detail in the "Polymorphic Association" post of the Çözüm Desenleri series; I won't repeat them here.

Leaving Every Column Nullable

Leaving a column nullable is the easiest way to postpone a decision. ORMs (Object-Relational Mappers) help with this too: in a C# project with nullable reference types turned off, every string becomes a nullable column. The result is that the answer to "can this be empty?" disappears from the schema. Listing the nullable columns of a schema is a good starting point for a review:

SELECT t.name AS table_name, c.name AS column_name
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
WHERE c.is_nullable = 1
  AND c.is_computed = 0
  AND t.name IN ('books', 'customers', 'orders')
  AND c.name NOT IN ('tags', 'details', 'phone2', 'phone3', 'status_code')
ORDER BY t.name, c.column_id;

table_name  column_name
----------  --------------
books       subtitle
books       category_id
books       published_year
books       page_count
customers   phone
customers   city
orders      shipped_at

(7 rows affected)

(I left out the example columns added in this post.) The Bookstore has seven nullable columns, and each one's NULL says something different: for subtitle "it has no subtitle", for page_count "we don't know", for shipped_at "not shipped yet", that is, a state. The question we asked for nullable foreign keys in the 3rd post, "unknown or absent?", holds for every nullable column. When everything is nullable, none of these meanings is written down, queries fill up with COALESCE, and nobody remembers why COUNT(page_count) and COUNT(*) differ. The rule is simple: the default is NOT NULL, and for every nullable column the meaning of NULL is written in one sentence. The cost of avoiding NULL with sentinel values is in the "Sentinel Value'lar" post of the Çözüm Desenleri series.

FLOAT for Money

FLOAT is a fast type with a wide range; it is the right choice for measurements, coordinates and scientific calculations. It is wrong for money, because in binary it cannot represent decimal values such as 0.1 exactly. Let's add up ten payments of 0.10:

CREATE TABLE #payments (amount_float FLOAT NOT NULL, amount_decimal DECIMAL(10,2) NOT NULL);
INSERT INTO #payments (amount_float, amount_decimal)
SELECT 0.1, 0.1 FROM GENERATE_SERIES(1, 10);

SELECT SUM(amount_float)                         AS float_sum,
       CAST(SUM(amount_float) AS DECIMAL(20,17))  AS float_sum_exact,
       IIF(SUM(amount_float) = 1.0, 'equal', 'not equal') AS float_check,
       SUM(amount_decimal)                       AS decimal_sum,
       IIF(SUM(amount_decimal) = 1.0, 'equal', 'not equal') AS decimal_check
FROM #payments;

(10 rows affected)
float_sum            float_sum_exact     float_check  decimal_sum  decimal_check
-------------------  ------------------  -----------  -----------  -------------
0.99999999999999989  .99999999999999989  not equal    1.00         equal

(1 rows affected)

The FLOAT sum is not equal to 1; the DECIMAL sum is exactly 1.00. The difference is invisible once the screen rounds it, but it shows up in equality checks, reconciliation reports and "accounts whose total is zero" queries. Use DECIMAL(p, s) for money, choose the scale by the precision the currency needs, and round deliberately in code. The money value object, VAT (Value Added Tax) calculation and distributing cents are in the "Para ve Yuvarlama" post of the Çözüm Desenleri series.

In Production

Fixing an anti-pattern is a migration, and it usually cannot be done in one step. As in the tag example, build the new structure next to the old one, move the data, change the application first to write to both structures and then to read only the new one, and drop the old column last. The deployment side of this expand-contract flow is in the "Migration'ların Deploy'daki Yeri" post of the Deployment Stratejileri series.

Put an "unrecognized data" report into the migration script. The last query of t3.sql caught Foundation's typo; in real data this list is long and every row needs a decision. The row-level error report and the staging table are in the "Toplu Import ve Validation Raporu" post of the Çözüm Desenleri series.

Meet the need for flexible fields by questioning it. Behind a "product properties should be flexible" request there are usually a few known properties. List them first; if a truly unknown part remains, put it in a JSON column, constrain it, and pull frequently queried properties out as computed columns.

Look for anti-patterns in review with a query. Columns with numbers in their names, text columns whose names end in tags, ids or list, an attribute_name/attribute_value pair and columns like lookup_type can be found with a simple query on sys.columns.

Common Pitfalls

Fixing an anti-pattern without a migration plan. Dropping the column and adding the table in a single deploy causes errors during the minutes the old code keeps running. Expand first, then contract.

Answering every request for flexibility with EAV. The price of EAV is that the database gives up type, mandatory and name checks; pay it only if end users really define the properties.

Searching a list with LIKE. '%classic%' finds "modern-classic" as well; wrapping in commas (',' + tags + ',') reduces wrong matches but does not solve spaces and typos. If the list is queried, it is a table.

Forgetting NULL in a CHECK constraint. CHECK only rejects a FALSE result; every expression that yields UNKNOWN passes. The trap we hit with the JSON constraint applies to every CHECK on a nullable column.

Mistaking the one big lookup table for "reducing repetition". Different concepts sitting in the same table is not normalization; merging rows with the same structure that don't carry the same meaning takes the meaning away from the foreign key.

A Small Experiment

★☆☆ Add the correct classic tag for Foundation to book_tags and write the "classic books" query against the new model. How many rows come back?

★★☆ Write a script that moves the data in book_attributes into the books.details JSON column. What do you do with the filesize_mb and unknown rows? Which rows should the script report?

★★★ Draw up an expand-contract plan that splits the lookups table into one table per concept (order_statuses, payment_methods, languages): which column is added at which step, when does the application move to the new table, and when is the old foreign key dropped?

Most anti-patterns come from wanting a shortcut in a normalized OLTP (Online Transaction Processing) schema. In the next post we look at a completely different need: reporting. Using the Bookstore's sales data, we will see why a normalized OLTP schema struggles with analytical queries and build fact and dimension tables, a star schema and the slowly changing dimension types.