Database Design Errors to Avoid & How To Fix Them

Six decisions cost more than any others once a table has rows in it: a missing foreign key, a missing or badly chosen index, structured data parked in a JSON column, normalization skipped or taken too far, names that follow no rule, and a schema nobody wrote down. Each one takes minutes on the design and a migration window afterwards.

Every section below has the same three parts: what the mistake looks like, what it costs, and the statement that fixes it. The SQL runs on PostgreSQL 18, and the decisions behind it apply to any relational database schema.

Foreign keys that were never declared

The first schema to look at is one where the relationship exists in everyone's head and nowhere in the DDL:

CREATE TABLE users (
  user_id  int PRIMARY KEY,
  username text NOT NULL
);

CREATE TABLE reviews (
  review_id   int PRIMARY KEY,
  user_id     int NOT NULL,
  review_text text NOT NULL
);

INSERT INTO users VALUES (1, 'ada');
INSERT INTO reviews VALUES (10, 1, 'Fits the socket'), (11, 42, 'Never arrived');

Review 11 belongs to user 42, who was never created. PostgreSQL accepts it, because nothing in reviews says that user_id has to match a row in users:

SELECT r.review_id, r.user_id, u.username
FROM reviews r
LEFT JOIN users u ON u.user_id = r.user_id
ORDER BY r.review_id;

The cost arrives later than the mistake. An inner join drops review 11 from every report that counts reviews per user, an outer join reports it against a blank name, and the two numbers disagree by one. Nothing failed at insert time, so the search starts with the report rather than with the row.

Add the missing foreign keys

Declare the relationship the reader of the schema is currently guessing at. An ALTER TABLE statement adds the constraint to a table that already holds data:

ALTER TABLE reviews
  ADD CONSTRAINT fk_reviews_users
  FOREIGN KEY (user_id) REFERENCES users (user_id)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

Adding a constraint scans the table to verify that every existing row satisfies it[2], so on the table above the statement fails until review 11 is deleted or repointed at a real user. Where the table is large enough that the scan is a problem, add the constraint with NOT VALID: the scan is skipped, later inserts and updates are still rejected, and ALTER TABLE reviews VALIDATE CONSTRAINT fk_reviews_users checks the old rows once you have cleaned them up[2].

The two actions decide what happens to the child rows. ON DELETE CASCADE deletes the referencing rows when the referenced row is deleted, and ON UPDATE CASCADE copies a changed key value into them[1]. The default, NO ACTION, lets the delete run and then checks the foreign key, so it usually ends in an error and leaves the cleanup to you. Where the check is deferred to later in the transaction, a statement further along can still delete the dangling rows before the constraint is checked, and the transaction commits[1].

In DbSchema the same relationship is drawn rather than typed. Double-click a table header on the diagram to open the Table Dialog and add the key on the Foreign Keys tab, or drag from the referencing column to the referenced one on the diagram. Double-click the relationship line to open the Foreign Key Editor, where the column mapping and both actions sit in one place. Every edit so far changes the model file only; Schema → Synchronize Model with Database is the step that generates the ALTER TABLE and runs it against the database.

Missing or badly chosen indexes

The foreign key added above indexes nothing on the child side. PostgreSQL indexes the referenced columns, since they have to be a primary key or unique, and the declaration of a foreign key constraint does not automatically create an index on the referencing columns[1]. So this delete has to find the reviews belonging to user 1 by reading the whole reviews table:

DELETE FROM users WHERE user_id = 1;

The symptoms are recognizable before you open a query plan. Response time tracks the size of the table rather than the size of the result, a filter on an unindexed column gets slower every month, and a join between two growing tables gets slower faster than either of them.

Add and verify the right indexes

Start with the columns that appear in WHERE, JOIN, and ORDER BY clauses, and with the referencing side of every foreign key. The CREATE INDEX syntax is the same idea on every engine:

CREATE INDEX idx_reviews_user_id ON reviews (user_id);

Then measure, with EXPLAIN ANALYZE on the query you are trying to speed up, before and after. An index is not free after it is built: the system has to keep it synchronized with the table, which adds overhead to every insert, update, and delete of the indexed column[5]. An index nobody's query uses is a write cost with no read benefit. The interaction between indexes and foreign keys is the one worth checking first, because the cost of missing it is paid by a delete rather than by a select.

In DbSchema, indexes are managed on the Indexes tab of the Table Dialog, the same tab that holds the primary key, so the whole set is in front of you while you decide. Adding one there changes the model; the CREATE INDEX reaches the database when you synchronize.

Structured data kept in a JSON or JSONB column

A json or jsonb column is the usual answer when a set of fields is optional, differs between rows, or is still changing shape. PostgreSQL stores the two types differently: json keeps an exact copy of the input text, while jsonb keeps a decomposed binary form that is slightly slower to write, faster to process because nothing is reparsed, and supports indexing[3].

What you give up is the type check. Both of these rows go in:

INSERT INTO users VALUES (2, 'grace');

CREATE TABLE user_settings (
  user_id  int PRIMARY KEY REFERENCES users (user_id),
  settings jsonb NOT NULL
);

INSERT INTO user_settings VALUES
  (1, '{"theme": "dark", "page_size": 50}'),
  (2, '{"theme": "dark", "page_size": "50"}');

SELECT user_id, jsonb_typeof(settings -> 'page_size') AS page_size_type
FROM user_settings
ORDER BY user_id;

jsonb_typeof returns the type of the stored value[4], and page_size is a number in one row and a string in the other. A column of type integer could not have held both, which is the whole point of the difference: every consumer of settings now has to defend against a key that moved, vanished, or changed type.

Keep structured data in columns

One question decides where a field belongs. A field that appears in a WHERE clause, a join, or a report is a column, with a type and a NOT NULL if it is required. A field that is only ever read back whole, with the rest of the document, can stay in the document. Use JSON or JSONB for the second kind, and revisit the decision when a query starts filtering on something inside the column.

Whatever stays in the column still deserves a description. In DbSchema, the text in the Description field of a table or a column is carried into the generated documentation and shown as a mouse-over tooltip in the HTML5 output, which is where the shape of a jsonb column can be written down for the next reader.

Normalization skipped, or taken too far

One table that holds everything is the shape a schema drifts into when each new field is added where it is first needed:

CREATE TABLE reviews_flat (
  review_id    int PRIMARY KEY,
  user_id      int  NOT NULL,
  user_name    text NOT NULL,
  phone_number text NOT NULL,
  movie_id     int  NOT NULL,
  rating       int  NOT NULL,
  comment      text NOT NULL
);

INSERT INTO reviews_flat VALUES
  (501, 1, 'Alice Martin', '555-342-9752', 101, 5, 'Enjoyed it'),
  (502, 2, 'Ben Carter',   '222-865-9876', 102, 4, 'Well written'),
  (503, 1, 'Alice Martin', '555-342-9752', 103, 3, 'Slow in the middle');

Alice's phone number is stored once per review she wrote. Changing it means updating every row that carries it, and an update that misses one row leaves the table holding two answers to the same question. The opposite mistake costs differently: a schema split past the point where the split means anything turns an ordinary read into six joins, and each join is a decision the next developer has to get right.

Normalize to 3NF, then stop

Third normal form is reached when every non-key column depends on the key and on nothing else. Here user_name depends on user_id, not on review_id, so it moves to the table keyed by user_id:

CREATE TABLE reviewers (
  user_id      int PRIMARY KEY,
  user_name    text NOT NULL,
  phone_number text NOT NULL
);

CREATE TABLE reviews_3nf (
  review_id int PRIMARY KEY,
  user_id   int NOT NULL REFERENCES reviewers (user_id),
  movie_id  int NOT NULL,
  rating    int NOT NULL,
  comment   text NOT NULL
);

The phone number now has one home, and the reviews table is shorter. Go to 3NF as the default and denormalize only against a query you have measured, because a denormalized copy is a fact stored twice and something has to keep the copies equal. On a DbSchema diagram the over-normalized case is the easier one to spot: a table split once too often shows up as a chain of one-to-one relations that every query has to walk.

No naming convention

Column names such as id, countryID, Country_id, and countryid inside one schema are a tax on everyone who writes a join, because the first thing each query needs is a guess about which spelling this particular table uses. Quoted identifiers make it worse: an identifier PostgreSQL folded to lowercase and one created as "Country_id" are two different names, and only one of them works unquoted.

Choose a naming convention and enforce it

Pick one convention and write it down: lowercase with underscores, plural or singular table names, and the same suffix for every key column. Which one you pick matters less than picking it before the tenth table.

Enforcement can be mechanical rather than social. Model Validation in DbSchema turns the convention into rules you run: each validator is a sequence of steps, where an operation selects what to iterate over (tables, columns, foreign keys, indexes), a filter narrows it, and a check is the condition that must hold, for example that all table names match a naming pattern. Click Validate and the results panel lists every object that failed a check, with the rule it broke; clicking a result goes to that object in the diagram. Validation reads the model rather than the database, so you can run it on a design that has not been deployed yet. Model Validation is in the Pro edition.

A schema nobody documented

Columns outlive the reason they were added. Six months on, nobody can say whether a nullable column is optional by design or optional because the migration that filled it half worked, and the next change either preserves behavior that nothing needs or breaks something load-bearing. Interactive database documentation is the cheapest answer, because it is generated from the schema rather than written by hand and then left behind.

Document the schema where it lives

A comment on the object travels with the object:

COMMENT ON COLUMN orders.order_date IS 'The date the order was placed';

COMMENT stores the text in the database itself[6], so it survives a dump and restore and reaches anyone who reads the catalog, which a wiki page does not.

DbSchema reads those comments into the Description field of each table and column, and Diagram → Export HTML5 or PDF Documentation turns the model into an HTML5, PDF, or Markdown document. The HTML5 output opens in a browser with no server behind it, carries the diagram as a vector image, and shows each description as a mouse-over tooltip. Generating documentation reads the model and writes files; nothing is changed in the database.

All six mistakes share a shape: a decision that is free while the table is empty and expensive once it is not. Declaring the key, indexing the referencing column, moving the repeated column out, agreeing the naming rule, writing the comment, each of them takes minutes on the model, and a schema design procedure that puts them in that order stops them accumulating. Download DbSchema at https://dbschema.com/download.html, connect to your database, and reverse-engineer it into a diagram to see which of the six your schema is carrying: connecting, reverse-engineering, and the interactive diagram are in the free Community Edition, while Model Validation, the HTML5 documentation export, and schema synchronization are Pro.

Sources

1. PostgreSQL 18 Documentation, Constraints
2. PostgreSQL 18 Documentation, ALTER TABLE
3. PostgreSQL 18 Documentation, JSON Types
4. PostgreSQL 18 Documentation, JSON Functions and Operators
5. PostgreSQL 18 Documentation, Indexes
6. PostgreSQL 18 Documentation, COMMENT