Day 3 Plain-English Guide — Normalization, ERD & Change Impact

00Why this day, and why now

Without a reason to care, normalization is just three things to memorise.

Think of building a house.

Day 1 wireframes = the floor plan. What goes where.
Day 2 sequence & data flow = how people move through it. Front door to kitchen.
Day 3 ERD & normalization = the plumbing and wiring diagram. Invisible — and if you get it wrong you tear the walls back open.

Coding starts on Day 6. If the wiring diagram is wrong by then, every screen and API you have already built has to be undone. In a 10-day bootcamp that is fatal. Hence today.

What you will actually produce today — only three things ① a list of your tables and how they connect (the ERD) · ② a standard vocabulary your whole team agrees to use · ③ a list of what breaks if you change a table.
Everything else (1NF/2NF/3NF, cardinality symbols) is just tooling to get those three. Don't memorise the tooling — ship the three artefacts.

01Bottleneck ①: Normalization — "the story of one spreadsheet that ruins everything"

Start from the definition and you will bounce off it. So let's start from the pain.

1-0. The spreadsheet that looks fine

Say you are building an online course platform. At first, one sheet does everything.

Table A. Enrolment sheet (looks perfectly reasonable on day one)
#	Student	Student phone	Courses	Instructor	Instructor phone	Fee (₦)
1	Chidi Okeke	0801-111-1111	AI Product Planning, Data Analytics	Ngozi Eze	0809-999-9999	120,000
2	Amina Bello	0802-222-2222	AI Product Planning	Ngozi Eze	0809-999-9999	60,000
3	Chidi Okeke	0801-111-1112	Prompt Engineering Lab	Kwame Mensah	0808-888-8888	90,000

This sheet has already booked three accidents. Engineers call them anomalies.

Insert anomaly

You launch a new course, "AI Ethics", with zero students so far. → There is no way to record it. No student, no row.

Update anomaly

Chidi changes his number. → You must edit every row where Chidi appears. Someone edited row 3 only, so rows 1 and 3 now disagree. Which one is true?

Delete anomaly

Amina refunds, so you delete row 2. → If that was the last row for "AI Product Planning", the course itself vanishes too.

So: normalization is…

Splitting one sheet into purpose-built tables so those three accidents cannot happen. That's it. 1NF/2NF/3NF is just a three-step checklist for where to cut.

One principle to remember "Every fact is written down in exactly one place." — the moment a fact lives in two places, sooner or later the two will disagree. 1NF, 2NF and 3NF are three questions that enforce that single sentence.

1-1. 1NF — "one value per cell"

The question to ask: is there any cell where you stuffed several things in, separated by commas?

In Table A, Courses: "AI Product Planning, Data Analytics" is exactly that. Now try to answer "how many students are in AI Product Planning?" — you have to chop strings apart. That way lies pain.

The fix: add rows instead. One value per cell — Chidi becomes two rows.

The #1 mistake non-engineers make Putting A,B,C into a single cell for things like tags, interest categories, or attachments. It reads naturally in a planning doc and it is the most common 1NF violation there is. If a field smells like it could hold more than one thing → it becomes its own table.

1-2. 2NF — "only keep what the whole name tag decides"

After 1NF, each row is identified by a two-part name tag: (student + course). Chidi–AI Planning, Chidi–Data Analytics, and so on.

The question to ask: to fill in this cell, do I need both halves of the name tag, or just one?

Column	What do you need to know it?	Verdict
Enrolment date	student and course — both	stays in this table
Student phone	student only (course is irrelevant)	evicted → Student table
Course title / fee	course only (student is irrelevant)	evicted → Course table

So 2NF = "if half the name tag is enough to determine a column, that column moves out with that half." This is the step that kills the update anomaly — fix Chidi's number once in the Student table and you're done.

1-3. 3NF — "if a neighbouring column decides it, move it out"

Now look at the Course table: course_id, title, instructor_name, instructor_phone.

The question to ask: is this column determined by the name tag (course_id), or by another column sitting next to it?

instructor_phone is not determined by the course. It is determined by who the instructor is.
course_id → instructor → instructor_phone. Determined one hop away. This is called a transitive dependency.
If Ngozi changes her number, you must edit every course she teaches. The update anomaly is back.

The fix: create an Instructor table and leave only instructor_id in Course.

Three-line summary — this is all you need during the lab 1NF one value per cell (no comma lists).
2NF if half the name tag determines it, it moves out with that half.
3NF if a neighbouring column determines it, move it out.
For a non-engineering team, 3NF is a perfect score. You do not need BCNF or 4NF today.

Deep dive — for teammates with dev experience

Is normalization always right? No. Normalization buys write consistency and sells read performance. Split into five tables and a single screen now costs five JOINs.

At bootcamp-MVP scale (thousands of rows), JOIN cost is effectively zero. Go to 3NF. Premature denormalization wrecks far more teams than JOINs ever do.
The real exception is snapshot values. An order's amount_paid must not point at the product's current price — if the price changes later, every past receipt is silently rewritten. Facts that belong to a moment in time get copied, not referenced.
The test: "is this the fact as of now, or the fact as of then?" As-of-now → reference (normalize). As-of-then → copy (deliberate denormalization). Whichever you choose, write it into today's Decision Log (Mission 01).

02Bottleneck ②: ERD & cardinality — "the metro map of your tables"

The symbols aren't the hard part. The hard part is that nobody tells you how to decide which symbol to use.

2-1. An ERD is a metro map

ERD (Entity Relationship Diagram), in plain words: "a picture of what exists and what connects to what."

Entity = one table = a station. Student, Course, Instructor.
How to spot one: a noun your service has to count and manage separately. If you can say "3 students, 5 courses", it's an entity.
Attribute = a column = information about that station. Name, phone.
Relationship = a line = "a student enrols in a course."
Cardinality = the symbol at the end of the line = how many to how many.

2-2. How to decide cardinality: ask twice, in both directions

Say these two sentences out loud ① "One student can enrol in how many courses?" → many
② "One course can hold how many students?" → many
Both "many" → N:M. Only one side "many" → 1:N. Both "one" → 1:1. That's the whole method.

Type	Reads as	Example	How it's actually built
1:1	one to one	User ↔ User profile detail	Usually just merge into one table. If you split it, write down why.
1:N	one to many (most common)	1 instructor → many courses	Put the "1" side's ID on the "N" side (instructor_id lives in Course). ← beginners get this backwards constantly
N:M	many to many	Student ↔ Course	Cannot exist in a database. See below

This is Day 3's biggest trap — N:M cannot physically exist "Student ↔ Course" is impossible with two tables. Put course_id in Student? There are many. Put student_id in Course? Also many.
The fix: the relationship gives birth to a third table. It's called a junction table (link table, associative entity). Here it is Enrolment.
And that table is not just glue. It is where facts that belong to the relationship itself live — enrolment date, payment status, progress percentage. Miss it and that information has nowhere to go, gets dumped into the wrong table, and your normalization collapses again.

↑ One sheet became four tables. Note how the Student–Course N:M was split into two 1:N relationships via ENROLMENT.

2-3. Reading the symbols (crow's foot notation)

Symbol	Means	Mermaid
\|\|	exactly one (mandatory)	`\|\|--\|\|`
o\| / \|o	zero or one (optional)	`\|o--\|\|`
o{ (crow's foot)	zero or more	`\|\|--o{`
\|{	one or more (at least one required)	`\|\|--\|{`

The little circle means "it's allowed to be empty." If an instructor can exist with no courses yet, use ||--o{. If they must have at least one, use ||--|{. That distinction becomes your "is this field required?" rule later.

Mermaid: these five lines are enough to start

erDiagram
    STUDENT ||--o{ ENROLMENT : "enrols in"
    COURSE  ||--o{ ENROLMENT : "contains"
    STUDENT {
        int student_id PK
        string name
    }

Keep entity names in UPPERCASE English — some tools break on other scripts. PK is the table's own ID; FK is an ID pointing at another table.

2-4. Foreign keys & referential integrity — "you can't order under a customer who doesn't exist"

Foreign key (FK) = a finger pointing at another table. ENROLMENT.student_id points at STUDENT.
Referential integrity = the rule that something must actually be there where the finger points.
How it breaks: you delete a student but their enrolment rows survive. Those rows now point at a ghost.

So here is today's decision for your team: "when a user deletes their account, do we delete their records too, or keep them?" — this is not a technical question. Refunds, settlements and reporting all hang on it. Which is exactly why a non-engineer has to make the call.

Deep dive

Databases offer three options: CASCADE (delete children too) / RESTRICT (refuse deletion while children exist) / SET NULL (cut the link, keep the record). In practice most live services never hard-delete — they add a deleted_at column (soft delete). But note the tension: data-protection law may require actual erasure, so decide explicitly what you anonymise vs. what you retain and log it today. Day 8 (accounts) and Day 9 (payments) will hand you the bill for this decision.

03Bottleneck ③: Change impact analysis — "we haven't built anything yet, so why?"

Task 03 feels impossible because it asks you to imagine something breaking that doesn't exist yet.

Schema = the blueprint of your tables: which columns exist and what type they are. Think of it as the constitution of your data.

Impact analysis is not forecasting. It is map-reading. You start at one column and follow the lines, asking "does this pass through here?"

3-1. The domino from renaming one column

↑ Change one column and at least five places wobble. Three of them are not code — they are documents and people.

This diagram is also the answer to "why does Task 02 (terminology standardisation) matter?" Agree on the words up front and this domino never starts. Standardising vocabulary isn't admin busywork — it is the cheapest insurance you will ever buy.

3-2. Three grades of change — rate the risk first

Grade	Change	Why	What to do
Safe	Add an optional column	Existing code doesn't know it exists, so it ignores it	Just do it
Caution	Rename a column; add a required column	Everything reading it breaks; and there is no value to backfill	Write the impact list first
Dangerous	Drop a column, change a type, split a table	Irreversible. Data actually disappears	Backup + full team agreement + decision log

3-3. Copy this impact-analysis template

Your Task 03 deliverable can be exactly this table. One row per change.

Change	Grade	APIs affected	Screens affected	Existing data	Decision / alternative
USER.phone → mobile	Caution	GET /users, POST /signup	My Page, Signup form	Copy values, keep old column 2 weeks	Chose keep both for a while over an immediate drop
Add pay_status to ENROLMENT	Safe	GET /enrolments	My Courses	Default 'pending'	Proceed as-is

Stuck? Walk this order — it takes five minutes ① Which screen shows this column? → ② Which API feeds that screen? → ③ What does the API spec (Day 4) say about it? → ④ What happens to data already stored? → ⑤ Does anyone on the team call this thing by another name?

Deep dive

The professional answer is an expand–contract migration: ⓐ add the new column → ⓑ dual-write to both → ⓒ backfill existing rows → ⓓ switch reads to the new column → ⓔ drop the old one much later. You never rename in one shot. "Keep both for a while" in the table above is the compressed version. Seen this way, impact analysis is a dependency-graph problem, not an ERD problem — the database is the root, not a leaf, and APIs, screens, docs, analytics queries and even your team's vocabulary all sit on top of it.

04How to actually interrogate Codex AI

Today the AI is not your database designer. It is your hostile reviewer. Copy its answer down and you will never learn why it's right.

Bad prompt

"Design the database for our service."

No context, so you get generic output — and then you present an ERD you cannot explain.

Good prompt

"Here are our meeting notes. Extract candidate entities and, for each one, justify why it deserves its own table. Where it's ambiguous, say it's ambiguous."

Five prompts to use verbatim today

[Task 01] "From these meeting notes, extract candidate entities and attributes. For each candidate, judge whether it is a real entity or just an attribute, using 'can it be counted independently?' as the test, and give your reasoning."
[Task 01] "Find every N:M relationship in this ERD. For each, propose the required junction table and the attributes that belong to the relationship itself."
[Task 01] "Judge my table against 1NF, 2NF and 3NF separately. Where it fails, name the exact functional dependency that causes it. Do not fix it — only diagnose."
[Task 02] "In this column-name list, group the ones that mean the same thing but are written differently (user_name / userName / customer). Propose one standard for each group with reasoning."
[Mission 02] "Find three scenarios where referential integrity could break in this schema, and three problems that arise when two users act on the same record at once. Our service context is ___."

Where the AI will be wrong today — expect all three · It helpfully invents columns that were never in your notes (created_at, is_active…). Delete what you don't need.
· It draws N:M as a single line and forgets the junction table. Always check this yourself.
· It cannot know your business rules. "Keep records after account deletion" is not something an AI can decide for you.
That is precisely why Mission 01 (decision log) exists: what you accepted, what you rejected, and why — that is your actual skill.

05Exit check

All ticked? You're ready for Day 4 (API design). Anything unticked is your question for the AI or the TA.

I can list our tables and explain in one sentence why each one exists separately.
I can give a concrete example of why Table A is dangerous, naming insert, update or delete anomaly.
I counted how many N:M relationships our ERD has, and built a junction table for each.
I verified every line's cardinality by saying both directions out loud.
We found and unified at least three words that teammates were using differently.
Our team has an answer to "what happens to a user's data when they delete their account?"
I filled the impact-analysis table for at least one schema change.
I logged at least one rejected design option and why. (No rejected option means you never considered alternatives.)
I can point at the parts of the AI's design that I personally changed.
I can already guess our Day 4 API names from our table names. (e.g. COURSE → /courses)

06Mini glossary

Term	In plain words	Why you should care
Entity	one table / a noun you count separately	Get it wrong and you end up with far too many or too few tables
Attribute	a column	"Entity or attribute?" is today's core judgement call
Schema	the blueprint; the constitution of your data	Change it and everything on top shakes
Cardinality	how many to how many	Get 1:N backwards and the screen cannot be built
Primary key (PK)	that row's ID number	The basis for deciding what counts as a duplicate
Foreign key (FK)	a finger pointing at another table	This is what actually creates a relationship
Referential integrity	something must exist where the finger points	Prevents ghost records
Normalization	splitting so each fact is written once	Stops your data from contradicting itself
Anomaly	the three accidents caused by not splitting	The entire reason normalization exists
Junction table	the middle table an N:M gives birth to	Miss it and the design collapses
Migration	moving already-stored data to a new design	The real cost of any change
Mermaid	a tool where you write text and get a diagram	Lets you version-control an ERD like code

ACE AI Startup Bootcamp · Day 3 companion guide — this does not replace the official curriculum (Day 3 AI Classroom). Use it as pre-reading and as a lab reference.

When stuck, in this order: ① the matching section above → ② Codex AI with one of the five prompts → ③ your TA or instructor. Never sit on the same blocker for more than 20 minutes.