Designing Database Schemas for Clarity

Whenever I create a new table or column in a web app, I aim for clarity so that anyone connecting directly to the database can easily understand it.

My heuristic involves designing the database so that direct queries yield sensible results without needing any additional information beyond the database itself. 

There are trade-offs with this approach. For example, you might sacrifice some performance, so keep that in mind.

Let me give some examples: 

1. Enums: String vs Integer
Using integer enums like priority = 0, 1, 2 can be confusing when querying directly.

Using string enums like priority = 'urgent', 'medium', 'low' makes results instantly clear when running SELECT *.

👉 Use string enums unless performance is critical.

2. Nullable vs Non-Nullable
If something is required logically, enforce NOT NULL.
Don’t leave half your schema nullable "just in case."

👉 This makes the schema self-documenting.

3. More generally any validation that can be added on the DB should be there

Eg: user_invoices might contain a unique validation on user_id + invoice_id so add a unique index on ['invoice_id', 'user_id'] on that table

4. Relationship context 

If you need to store the creator of a record, don't just add user_id. Consider a more descriptive name like creator_id or even created_by_author_id. 

Inspired by Igor Aleksandrov post on running migrations multiple times: