How I Think About Database Schema Design

The starting point

When I start a schema, I try not to begin by asking which tables I need. I begin by asking what the system knows, which things have independent identity, how those things relate, and which states must never exist. Tables emerge from those answers. This approach is slower for the first ten minutes and usually faster over the lifetime of the project because the model starts from domain facts rather than implementation convenience.

Start with entities and ownership

An entity is something the system needs to identify independently. In a publishing system, an article, category, tag, and media item each have different lifetimes and relationships. Asking who owns what prevents accidental duplication and clarifies where foreign keys should point.

Ownership also helps define deletion behavior. If an article is removed, should its media disappear? Should a category disappear when it has no articles? Those are domain decisions, not merely SQL syntax choices.

Then design around real queries

A perfectly normalized model can still be awkward if the application's important queries are unclear. I therefore look at the pages and workflows the system actually needs. A public article list needs published records ordered by date. An article page needs a slug lookup and related content. The schema and indexes should support those paths directly.

The result is a model that balances integrity and practical access patterns. The database is neither an abstract academic exercise nor a pile of columns shaped around today's UI.