Data Modeling

From requirements to tables

1. Find the entities. Listen for the nouns in requirements and user stories: customer, order, product, invoice.
2. Write down the attributes of each, and the rules: what is required, unique or limited to certain values?
3. Define the relationships and their cardinality: one to many, or many to many (which needs a join table).
4. Choose keys. Every entity needs a stable identifier. Prefer a surrogate key (a generated id) when natural values like names or emails can change.
5. Sketch it as a simple diagram (an entity-relationship diagram, ERD) and review it with someone who knows the business.

Rules for a sound model

• Store each fact once. Duplicates drift apart. See normalization in Relational Database Concepts.
• Let the database enforce rules with constraints, types and foreign keys as well as application code.
• Pick the right type. Store dates in date types and money as an exact decimal (see Number Representation and Dates, Times and Time Zones).
• Name consistently. Choose singular or plural table names and one casing style, and stick to it.
• Model for the questions you will ask. A reporting need may justify a denormalized copy of data, but make that a conscious decision.
• Plan for change. Use versioned migration scripts kept in source control instead of editing a production database by hand.

Fixes for common problems

Put list items in a related table. Use a constrained set of values or a lookup table for statuses. Consider soft deletes or audit tables to keep history, and check privacy rules for personal data (see Data Privacy Awareness). Give the public API its own shape so clients are not coupled to your internal structure.

Try it

Pick a small domain you know, such as a library with books, members and loans. List the entities, draw the relationships on paper, and decide which table holds each foreign key. Then identify one rule you would enforce with a constraint.