From requirements to data model
🏠The everyday picture
Before building a warehouse, you decide what will be stored, how much, how it moves in and out, and who handles it. The shelving follows from that.
📘What it really is
- Start from the app's requirements (Module 50's project) and the questions the business will ask:
- "which customers haven't ordered in 90 days?";
- "what did we earn per month, by product?";
-
"which payments are overdue?".
-
Work through the design steps from Course 5.1:
- entities from the nouns (Module 53, Lesson 5);
- relationships and their kinds;
- columns, types, keys and constraints (Module 52);
- normalise to 3NF, with documented exceptions (Module 54);
- delete rules for every foreign key (Module 53);
-
check every requirement and question can be answered.
-
Include the CRM's core: customers, contacts or addresses, products or services, orders with items, payments, users with roles, and an activity log (who changed what, when).
-
Activity (audit) log: an append-only table of important changes:
(id, actor_user_id, action, entity, entity_id, details jsonb, created_at). It answers "who changed this customer's phone number?" and is often required for financial data.
🖱️Try it
Write REQUIREMENTS.md for the database side of your CRM: entities, the ten most important questions it must answer, and
rules (like "payments can't be deleted"). Draw the ER diagram.
✅Quick check
Why list the business questions before designing tables?
Show the answer
The design must make those questions answerable; listing them early avoids missing data or awkward structures.
What's an activity (audit) log for?
Show the answer
Recording who changed what and when, append-only, for accountability and investigating problems.