Design a consistent database with fewer data bugs

Bad database design usually does not explode on day one. The problems show up months later, when data starts drifting, one table says one thing, another says something else, the application is full of special-case fixes, migrations become painful, and every business rule change risks breaking old data.

If your goal is a database that survives long term, the first priority is not query optimization. It is consistency in the design itself.

Quick conclusion

If you only want a short checklist when designing a database:

1. every entity should have one clear source of truth
2. naming should stay consistent across tables, columns, and keys
3. important relationships should usually have foreign keys
4. core rules like uniqueness, required fields, and allowed values should live in the database through unique, not null, and check
5. do not duplicate data unless you have a clear reason
6. multi-table updates should be wrapped in transactions

Those 6 points already prevent a large percentage of long-term data inconsistency problems.

What does consistency mean in database design?

In practice, consistency has 2 layers here:

• data consistency: data does not contradict itself, does not leave orphan records, and does not duplicate meaninglessly
• design consistency: naming, column styles, table boundaries, and state modeling stay uniform across the system

Examples of inconsistency:

• users uses id, another table uses userId, another uses uid
• one place stores status = active, another stores is_active = 1
• orders.user_id still points to a user that no longer exists
• emails get duplicated because validation exists only in application code, not as a database constraint

These look small at first, but the cost grows with every new table.

When does a database lose consistency most easily?

Risk increases when:

• multiple developers change the schema without a clear convention
• business rules live only in application code and the database enforces almost nothing
• data is copied between tables "for convenience"
• multi-table updates happen without transactions
• soft delete, status fields, and audit fields are modeled differently in every table

That is why database design needs rules early, not after every table evolves on its own.

Principle 1: Every entity needs one source of truth

One important rule is:

A critical piece of information should have one clear canonical location.

Examples:

• a user's email should come from users
• the current product price should come from products
• an order's current status should come from a standard field in orders

If the same meaning exists in many places without a good reason, it will drift sooner or later.

Bad example:

users.email
orders.customer_email
invoices.customer_email

If all of them are supposed to mean "the user's current email", that design will drift. Duplicate it only when you intentionally need a historical snapshot, such as the billing email captured at invoice time.

Principle 2: Lock down naming conventions early

Consistency in naming removes a surprising amount of confusion from code and migrations.

Decide early:

• snake_case or camelCase
• whether primary keys are always called id
• whether foreign keys always use xxx_id
• whether timestamps always use created_at, updated_at

A practical convention:

users
orders
order_items

id
user_id
order_id
created_at
updated_at
deleted_at

The key point is not the exact style. It is staying consistent once you choose one.

Principle 3: Use clear primary keys and foreign keys

Primary keys identify records. Foreign keys tell the database how tables are related.

Example:

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Practical benefits of foreign keys:

• they prevent orphan records
• they make relations clearer during queries and migrations
• they force developers to respect data relationships instead of writing around them

Not every system uses FKs everywhere, but you should skip them only for a clear reason, not just for convenience.

Principle 4: Important rules should live in the database, not only in code

Many teams validate only in the API:

• emails must be unique
• status must be from a known set
• every row must have a user_id

But if the rule exists only in application code:

• background jobs can violate it
• import scripts can violate it
• other services can bypass it

That is why core rules should also exist in the database:

• NOT NULL
• UNIQUE
• CHECK
• DEFAULT

Example:

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  status VARCHAR(20) NOT NULL CHECK (status IN ('active', 'inactive', 'blocked')),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Application validation still matters for cleaner errors, but the database should be the final enforcement layer.

Principle 5: Normalize enough, and do not duplicate too early

A common mistake is duplicating data too early just to make queries feel easier.

Examples:

• storing user_name in orders
• storing product_price in cart_items
• storing department_name across many tables

Unless you intentionally need a historical snapshot, most of these values should be derived from their source table.

The goal is:

• one home for source data
• derived or denormalized data only when truly needed

Over-normalization is also not ideal if queries become unusably complex. But for most CRUD systems, preventing data drift matters more than premature denormalization.

Principle 6: The same kind of data should use the same format

Examples of inconsistency:

• IDs stored as INT in one place and VARCHAR in another
• money stored as FLOAT in one table and DECIMAL in another
• statuses stored as strings in one place and numbers in another
• timestamps stored as local time in one service and UTC elsewhere

Practical rules:

• money should usually use DECIMAL, not FLOAT
• timestamps should be standardized in UTC
• foreign keys should use the same type as the primary key they reference
• status fields should follow one representation model

Principle 7: Multi-table updates need transactions

This is where many systems lose consistency even when the schema looks decent.

Example order flow:

1. create a row in orders
2. create multiple rows in order_items
3. reduce inventory
4. write a payment log

If step 3 fails after steps 1 and 2 already committed, the database is already inconsistent.

These flows should be wrapped in a transaction:

BEGIN;

-- insert orders
-- insert order_items
-- update inventory

COMMIT;

Consistency is not just about schema. It is also about how writes are executed.

Principle 8: Soft delete, status fields, and audit fields need shared patterns

A system becomes messy quickly when:

• one table uses is_deleted
• another uses deleted
• another uses deleted_at

The same goes for audit fields:

• created_at
• updated_at
• created_by
• updated_by

Not every table needs full auditing, but if you use these patterns, they should be consistent.

A practical pattern:

created_at
updated_at
deleted_at

Using deleted_at IS NULL is often clearer than a simple is_deleted boolean.

Principle 9: Indexes support queries, but they do not fix bad design

Indexes matter a lot, but they do not rescue a schema with broken integrity.

Examples:

• if duplicated data is wrong, indexing it only makes wrong data faster
• if relationships are unclear, indexes do not restore integrity
• if status values are stored inconsistently, faster queries still return messy meaning

A better order of priorities:

1. consistent schema
2. clear constraints
3. correct queries
4. then optimize with indexes

What does a small but consistent schema look like?

Example:

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  full_name VARCHAR(255) NOT NULL,
  status VARCHAR(20) NOT NULL CHECK (status IN ('active', 'inactive')),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled')),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
  id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INT NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(12,2) NOT NULL CHECK (unit_price >= 0),
  CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id)
);

What matters here is not that the schema is "advanced". It is that:

• naming is uniform
• constraints are explicit
• relationships are explicit
• data types are consistent

Very common database design mistakes

1. Missing unique constraints on fields that should obviously be unique

Examples:

• user emails
• order codes
• transaction references

Application-level checks alone are not enough.

2. Status values represented in multiple ways

Examples:

• active
• 1
• enabled

Those may mean the same thing, but they make queries and application code much harder to maintain.

3. Source data copied into too many tables

If it is not a deliberate snapshot, it is usually a future inconsistency problem.

4. No transaction around multi-step writes

Even a clean schema will drift if write flows are not atomic.

5. Foreign keys with different data types than the primary keys they reference

For example, users.id as BIGINT but orders.user_id as INT or VARCHAR. Those mismatches always cause friction later.

Conclusion

Consistent database design does not mean making the schema academically perfect. The more practical goal is:

• data should not contradict itself
• core business rules should be enforced by the database
• tables should follow the same conventions
• multi-step write flows should not leave half-finished states behind

If you must prioritize only a few things, lock these down first:

1. naming conventions
2. primary keys and foreign keys
3. not null, unique, and check
4. transactions for multi-table flows

Those four alone already make a database much easier to keep healthy over time.