Data modeling

Schema outlives application code, usually by years. Code gets rewritten; the data stays, and everything written since inherits whatever shape you chose. Wrong data is also far harder to fix than wrong logic — you can deploy a fix for logic, but bad rows are permanent unless you can reconstruct the truth.

Push correctness into the database. Every rule the schema enforces is a rule the application cannot violate, including from a script someone runs at 2am.

1. Model the real relationships

Get cardinality right before anything else — it determines the whole structure.

• One-to-many: a foreign key on the many side
• Many-to-many: a join table. Give it its own key and any attributes of the relationship itself
• One-to-one: usually the same table, unless you are genuinely splitting for access or size reasons