Schema Antipatterns

Most bad schemas start as kindness to the first screen: "just put the item ids in one column," "just make phone1 and phone2," "just store the total." A schema antipattern is a table shape that feels convenient at first and then makes ordinary questions awkward, unsafe, or silently wrong.

Section 10 has shown the positive path: model, normalize, then denormalize with a contract. This lesson is the review lens: the shapes to pause on before they become production habits.

Jaywalking: comma-separated lists in one column

Jaywalking is the antipattern of storing several ids in one text column, usually comma-separated. Here a cafe stores which menu items belong to each promotion:

CREATE TABLE menu_items (
  menu_item_id integer PRIMARY KEY,
  item_name text NOT NULL
);
 
CREATE TABLE promo_bad (
  promo_id integer PRIMARY KEY,
  promo_name text NOT NULL,
  item_ids text NOT NULL
);
 
INSERT INTO menu_items (menu_item_id, item_name)
VALUES
  (1, 'Latte'),
  (2, 'Cookie'),
  (10, 'Travel Mug');
 
INSERT INTO promo_bad (promo_id, promo_name, item_ids)
VALUES
  (101, 'Coffee snack', '1,2'),
  (102, 'Mug launch', '10');
 
SELECT promo_id, promo_name, item_ids
FROM promo_bad
WHERE item_ids LIKE '%1%'
ORDER BY promo_id;

CREATE TABLE
CREATE TABLE
INSERT 0 3
INSERT 0 2
 promo_id |  promo_name  | item_ids
----------+--------------+----------
      101 | Coffee snack | 1,2
      102 | Mug launch   | 10
(2 rows)

That result looks plausible until you remember the question: "promotions containing item id 1." 10 is not 1, but text matching does not know you meant ids. It only knows the character '1' appears inside '10'.

You can write a more careful text query:

SELECT promo_id, promo_name, item_ids
FROM promo_bad
WHERE '1' = ANY(string_to_array(item_ids, ','))
ORDER BY promo_id;

 promo_id |  promo_name  | item_ids
----------+--------------+----------
      101 | Coffee snack | 1,2
(1 row)

That fixes this one search, but it does not fix the design. The real fix is the relationship shape you already know: a junction table.

CREATE TABLE promos (
  promo_id integer PRIMARY KEY,
  promo_name text NOT NULL
);
 
CREATE TABLE promo_items (
  promo_id integer NOT NULL REFERENCES promos(promo_id),
  menu_item_id integer NOT NULL REFERENCES menu_items(menu_item_id),
  PRIMARY KEY (promo_id, menu_item_id)
);
 
INSERT INTO promos (promo_id, promo_name)
VALUES
  (101, 'Coffee snack'),
  (102, 'Mug launch');
 
INSERT INTO promo_items (promo_id, menu_item_id)
VALUES
  (101, 1),
  (101, 2),
  (102, 10);
 
SELECT p.promo_id,
       p.promo_name,
       mi.item_name
FROM promos AS p
JOIN promo_items AS pi
  ON pi.promo_id = p.promo_id
JOIN menu_items AS mi
  ON mi.menu_item_id = pi.menu_item_id
WHERE mi.menu_item_id = 1
ORDER BY p.promo_id;

CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 3
 promo_id |  promo_name  | item_name
----------+--------------+-----------
      101 | Coffee snack | Latte
(1 row)

Now relationships are values again. The database can protect both sides, and the query asks for item id 1 as an integer, not as a substring.

Rarely fine: raw imported text you are preserving for audit, or display-only labels that are never queried as relationships and are not the source of truth. The moment the app asks "which rows are connected?", use a real relationship table.

Multi-column attributes

The sibling of jaywalking is the numbered-column table:

customer_contacts
 customer_id | phone1       | phone2       | phone3
-------------+--------------+--------------+-------
           1 | 555-0101     | 555-0102     | NULL

It happens because the form has three boxes. The cost arrives when a customer has a fourth phone, when you need to search all phone columns, or when phone2 is empty but phone3 is not. The fix is the same 1NF move from 10.2: customer_phones(customer_id, phone_no, phone_number, phone_type), one phone per row.

Rarely fine: a truly fixed set of different facts, like billing_phone and emergency_phone, can be separate columns because they mean different things. phone1, phone2, phone3 means "I modeled a list as columns."

EAV: the schema that eats queries

Entity–Attribute–Value, or EAV, stores facts as rows like (entity_id, attribute_name, attribute_value):

product_attributes
 product_id | attribute_name | attribute_value
------------+----------------+----------------
          1 | roast_level    | medium
          1 | bag_weight_g   | 250
          1 | decaf          | false

EAV happens when a team wants "flexible custom fields." The trade is brutal: every value becomes text-shaped, constraints move out of the table, and a normal question becomes a self-join or aggregate puzzle. "Find 250g decaf medium-roast bags" is no longer a simple WHERE roast_level = 'medium' AND bag_weight_g = 250 AND decaf = false; it is a query over names and values.

Rarely fine: sparse, user-defined metadata where the database does not need to understand the values. The fix for core business facts is boring and strong: real columns with real types, or a separate table for a real subtype.

Polymorphic foreign keys

A polymorphic foreign key stores one id plus a type column:

CREATE TABLE order_notes_bad (
  note_id integer PRIMARY KEY,
  target_type text NOT NULL,
  target_id integer NOT NULL,
  note_text text NOT NULL
);
 
INSERT INTO order_notes_bad (note_id, target_type, target_id, note_text)
VALUES (1, 'ticket', 999, 'No matching ticket, but the table accepts it');
 
SELECT note_id, target_type, target_id, note_text
FROM order_notes_bad;

CREATE TABLE
INSERT 0 1
 note_id | target_type | target_id |                  note_text
---------+-------------+-----------+----------------------------------------------
       1 | ticket      |       999 | No matching ticket, but the table accepts it
(1 row)

The table accepts it because target_id does not reference one specific parent table. It might point at a ticket, a customer, a promo, or nothing at all. PostgreSQL cannot enforce "if target_type is ticket, check cafe_tickets; if customer, check customers" as a normal foreign key.

The usual fix is one table per relationship (ticket_notes, customer_notes) or a shared parent table that every note target has a row in. Rarely fine: logging or search-index tables that intentionally store loose references and are not the source of truth.

Derived data that drifts

Storing a derived value is not always wrong; 10.3 showed generated columns and summary tables with maintenance contracts. The antipattern is storing derived data with no owner. If order_lines stores quantity, charged_unit_price, and hand-written line_total, then one row can say 2 * 4.50 = 8.75.

Use a generated column when the row itself owns the formula. Use a summary table or materialized view when the derived value comes from many rows and has a refresh rule. Use a check query when you inherit a table and need to find drift:

CREATE TABLE line_totals_bad (
  ticket_id integer NOT NULL,
  line_no integer NOT NULL,
  quantity integer NOT NULL,
  charged_unit_price numeric(5,2) NOT NULL,
  line_total numeric(7,2) NOT NULL,
  PRIMARY KEY (ticket_id, line_no)
);
 
INSERT INTO line_totals_bad (ticket_id, line_no, quantity, charged_unit_price, line_total)
VALUES
  (1, 1, 2, 4.50, 9.00),
  (1, 2, 1, 3.00, 3.00),
  (2, 1, 2, 4.50, 8.75);
 
SELECT ticket_id,
       line_no,
       quantity * charged_unit_price AS computed_total,
       line_total AS stored_total
FROM line_totals_bad
WHERE line_total <> quantity * charged_unit_price
ORDER BY ticket_id, line_no;

CREATE TABLE
INSERT 0 3
 ticket_id | line_no | computed_total | stored_total
-----------+---------+----------------+--------------
         2 |       1 |           9.00 |         8.75
(1 row)

That one row is the whole warning: duplicate facts are promises. If nobody maintains the promise, the table will eventually lie.

Antipatterns are not moral failures. They are warning labels: if you choose one, you should be able to name the rare reason and the missing protection. Next, Section 11 turns from table shape to database behavior you can package: views, functions, and triggers.