Day 3 Summary Codex AI Architect Technical Design

Day 3. Technical Design (Data Normalization) Course Summary

Master the key information of the full collaborative design pipeline: turn meeting notes written in natural language into data structures (Mermaid ERD), build a project standard term dictionary, analyze the impact of design changes and preserve decisions.

Part 1. Background
Part 2. Lab goals
Part 3. Follow along
Part 4. How it works
Part 5. Teaching guide
Part 6. Final Task

Bottlenecks in technical design

In real projects, misunderstandings between planning, design and development lead directly to missing database items and broken consistency. Let's see why guaranteeing consistency at the design stage matters.

Understanding gap

Looking at the same screen plan, planners, designers and developers picture different data models and relationships, which ends in gaps or data mismatches (runtime errors).

Vocabulary gap

Using different names for the same subject, like 'member, customer, User, Member', creates big unnecessary translation and communication rework costs when naming variables.

Sharing gap

Fixing only the DB schema for a sudden new service policy without updating the API or screen plan makes system mismatches snowball and can bring the system down.

ChatGPT vs Codex Client (Workspace-based)

Compare ChatGPT, limited to a single chat context, with Codex Client, which understands the whole folder structure and dependencies between files.

Compared ChatGPT (general chat AI) Codex Client (Workspace-based AI)
Context awareness Understands only the single file/text the user pastes, locally Understands the dependencies between DDL schema, plans and API specs in the folder at the same time
Term standardization Stays at one-off translation; can't keep variable names consistent across the project Recommends consistent standard names and DB column names based on the project term dictionary
Impact analysis Suggests isolated schema errors or made-up table fixes Works backward from small schema changes (e.g. adding a column) to list API specs and screen parts to fix

Summary of today's final outputs

The list of final outputs learners create during the Day 3 session, and the purpose of each.

Task 01. Data structuring

A Mermaid ERD script and spec rendering entities and relationships pulled from a natural-language plan (output: erd-schema.md)

Task 02. Term standardization

A project standard term dictionary defining scattered planning terms as standard English columns (output: project-glossary.md)

Task 03. Share change impact

A change impact analysis listing API and screen fixes for adding booking approval (WAIT_APPROVE) (output: change-impact-report.md)

Mission 01. Design record

A Design Decision Log (DDL) archiving the reasons for normalization and data design decisions and rejected alternatives (output: design-decision-log.md)

Mission 02. Find exception scenarios

An Edge Case analysis report with DB improvements for concurrent payment errors, data lost on account deletion (referential integrity) and transaction rollback (output: edge-case-analysis.md)

Input / Output data pipeline

A mapping of the inputs used in the lab and the outputs produced through Codex Workspace reasoning.

01
Data structure
02
Terms
03
Change impact
M1
Decisions
M2
Exceptions
Task 01. Resolve data structure misunderstandings
šŸ“„ Input (planning meeting notes)
• day3_meeting_notes.md (meeting notes) - User flow scenarios and booking process as unstructured prose
Codex Workspace āž”
šŸ’¾ Output (ERD spec)
erd-schema.md with Mermaid ERD code - Visualizes Primary Key/Foreign Key relationships of the User, Expert, Reservation and Payment entities

STEP 1. Task 01 - Resolve data structure misunderstandings

Enter the meeting scenario prose and derive precise normalized relationships (1:N, 1:1) and Mermaid code.

šŸ” Codex request prompt (data structuring)

Analyze the provided meeting notes from a data structure view, derive the key entities and relationships, and write a data structure doc and Mermaid ERD code.
  • Check that the PK and FK settings for the 4 entities User, Expert, Reservation and Payment don't conflict with the scenario logic.
  • Check that cardinality notation is correct when the Mermaid diagram (`erDiagram`) renders.

STEP 2. Task 02 - Term standardization

Compare the gap between Figma plan terms and DB dev specs and build a domain term master dictionary.

šŸ” Codex request prompt (term dictionary)

Analyze all the terms mixed across the project, like 'member/User/customer/Member' and 'counselor/Expert/teacher/Counselor', and write a project standard term dictionary as a Markdown table.
  • Check that standard English names and column types that fit the business domain are given correctly.

STEP 3. Task 03 - Share change impact

Find the API and table impact when the release plan changes to add an 'approval' step to booking.

šŸ” Codex request prompt (find impact)

The booking confirmation process changes to add a 'waiting for approval' state and 'pay after expert approval'. Write the affected API specs, DB schema, screens and a change sharing report.
  • Check that it detected the chain impact of the Reservations table status change, new API specs and button state transitions on the planned screens.

STEP 4. Mission 01 - Design Decision Log

Log the rejection reasons and agreements for normalization decisions, splitting booking history physically, and so on.

šŸ” Codex request prompt (decision record)

Extract normalization and database structure decisions from the meeting and write a Design Decision Log including the decision item, background reason, alternatives reviewed, approver and chance of future change.
  • Check that the reason for splitting the history table (DEC-003) and the alternative (slower performance with one table) are clearly logged.

STEP 5. Mission 02 - Find exception scenarios (Edge Case)

Audit system weak points to prevent referential integrity violations (account deletion) and broken transactions.

šŸ” Codex request prompt (derive Edge Cases)

Derive Edge Case exception scenarios that break data consistency in the currently defined User, Expert, Reservation and Payment table structure, and suggest improvements.
  • Check the plan to keep referential consistency with Soft Delete (logical delete) and the response to distributed transaction rollback.

How It Works & Key Mechanisms

Schema-aware reasoning

Going beyond simple text matching, it understands entity referential integrity (FK) and transaction lifecycles, and works backward from a natural-language plan to DDL (Data Definition Language) queries and state machine rules automatically.

Meta term clustering

It groups differently written terms (customer name vs Nickname) by meaning (Clustering) to produce a company-wide term master dictionary, and syncs the data dictionary's consistency based on it.

How the five Tasks/Missions work together in technical design

See how data and analysis from each step go around the technical design loop to maximize quality.

[Agree on the data model] (Task 01. Structuring)
ā–¼
[Unify domain terms] (Task 02. Standard dictionary)
ā–¼
[Trace design change impact] (Task 03. Change control)
ā–¼
[Log architecture decisions] (Mission 01. Keep the Why)
ā–¼
[Prevent consistency and Edge Case problems] (Mission 02. Safe design)

This 5-step loop blocks exceptions at the source from the planning and design stage, giving developers who start coding a big productivity boost and removing human error.

šŸ’” Course design specs

  • Difficulty: ā˜…ā˜…ā˜…ā˜…ā˜† (advanced - includes database normalization and distributed transaction concepts)
  • Lab time: 1h 30min ~ 2h (combined design lab)
  • Real-world use: ā˜…ā˜…ā˜…ā˜…ā˜…

Learner questions/discussion topics

Topics that work well for class discussion or interview questions.

Q1. Account deletion strategy

"If you choose Soft Delete (logical delete) instead of Hard Delete for account deletion, what are the performance downsides, and what index/DB partitioning strategy can make up for them?"

Q2. Terminology Debt

"If a project runs to the middle without a standard term dictionary, how does the 'terminology debt' inside the dev team affect the real schedule?"

Final course Task for the planning/design stage

An overview of all the lab tasks and missions covered on Day 3.

Task Topic How AI is used
Task 01 Resolve data structure misunderstandings Turn meeting content into a data structure and organize entities and relationships
Task 02 Term standardization Analyze terms across the project and create a standard term dictionary
Task 03 Share change impact Analyze the impact of design changes and trace related docs and features
Mission 01 Record design decisions Write a Decision Log to manage design reasons and change history
Mission 02 Find exception scenarios Analyze missing Edge Cases and work out risks and improvements
← Enter Day 3 Classroom View full roadmap