Analyze and Plan an Excel Migration

Audit a messy Excel system and design a normalized PostgreSQL schema.

Introduction

30 Second Summary

Every business has one. That critical Excel spreadsheet that started as a simple tracker and grew into a tangled monster of VLOOKUPs, duplicated data, and color-coded business rules that only one person understands.

In this project, you will analyze a messy production management workbook and produce the professional migration artifacts that a development team needs to build its web replacement: a system audit, a normalized ER diagram, a PostgreSQL schema, and a data dictionary.

What You'll Build

A complete migration analysis package for a fictional "SteelWorks Manufacturing" production system trapped in Excel. You'll hand off a set of deliverables that a dev team could pick up and start building from tomorrow.

By the end of this project, you'll have:

  • A system audit document that maps every entity, hidden business rule, and pain point buried in the spreadsheet.
  • A normalized ER diagram in draw.io that transforms flat spreadsheet data into a proper relational model with primary keys, foreign keys, and cardinality notation.
  • A PostgreSQL DDL schema that implements the normalized model with constraints encoding the business rules you discovered.
  • Secret Mission: Write SQL INSERT statements that migrate sample data from the flat spreadsheet into your normalized tables, proving the transformation works end-to-end.

Are there any prerequisites?

You should have a basic understanding of relational databases and SQL. Familiarity with spreadsheet formulas (like VLOOKUP) will help you follow along with the legacy system analysis.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

You're about to analyze a messy Excel production management system and produce the professional migration artifacts a development team would need to build its web replacement.

Create the Legacy Spreadsheet

Before you can analyze a system, you need a system to analyze. In this step, you'll build a deliberately messy Excel workbook that mirrors how production tracking systems actually evolve in the real world.

Real legacy spreadsheets grow organically over years. Customer data gets copy-pasted into every row, materials get referenced by name instead of ID, and critical business logic hides inside formulas nobody documents. You'll recreate these exact anti-patterns so your later analysis has something realistic to untangle.

In this step, get ready to:
  • Create a LibreOffice Calc workbook with four interrelated sheets.
  • Populate each sheet with sample data that includes deliberate data quality problems.
  • Add cross-sheet VLOOKUP formulas and conditional formatting that encode hidden business rules.
Create the workbook and set up four sheets

The production system you're simulating tracks work orders, materials inventory, scheduling, and quality inspections across four separate sheets in one workbook.

  • Switch to LibreOffice Calc from earlier.
  • Click File in the menu bar, then click Save As.
  • Navigate to your project workspace folder on your Desktop.
  • Name the file production-tracker.xlsx and click Save.
  • If prompted about the file format, click Use ODF Format! or Keep Current Format to save as .xlsx.

Now set up the four sheet tabs at the bottom of the workbook.

  • Right-click the default sheet tab (usually named Sheet1) at the bottom of the window and select Rename Sheet.
  • Type WorkOrders and press Enter.
  • Click the + button to the left of the sheet tabs to add a new sheet.
  • Name it Materials and click OK.
  • Repeat to create a third sheet named Schedule.
  • Repeat once more to create a fourth sheet named QualityChecks.

You should now see four sheet tabs at the bottom of the workbook: WorkOrders, Materials, Schedule, and QualityChecks.

Don't see the + button for new sheets?

Right-click any existing sheet tab and select Insert Sheet instead. In the dialog, type the sheet name and click OK.

Help me add new sheets in LibreOffice Calc

Populate the Materials sheet

Start with the Materials sheet because other sheets will reference it. This sheet tracks raw material inventory with supplier info embedded directly in each row.

  • Click the Materials tab at the bottom.
  • Click cell A1 and type the column headers across row 1, using the reference below:
A1: MaterialName
B1: Category
C1: UnitCost
D1: StockQty
E1: ReorderLevel
F1: Supplier
G1: SupplierContact
  • Starting in row 2, enter the following four materials:
Row 2: Steel Sheet 4mm | Raw Metal | 45.50 | 200 | 50 | MetalWorks Supply | jim@metalworks.com
Row 3: Aluminum Rod 10mm | Raw Metal | 32.00 | 150 | 30 | MetalWorks Supply | jim@metalworks.com
Row 4: Industrial Adhesive | Consumable | 18.75 | 80 | 20 | ChemBond Inc | sales@chembond.com
Row 5: Rubber Gasket Kit | Component | 12.00 | 300 | 100 | SealPro | orders@sealpro.com

What does this data show?

Each pipe character (|) separates values into different columns. Type each value into the next cell to the right.

Notice that supplier info is embedded in the same row as material info. In a proper database, suppliers would be their own table. This is one of the anti-patterns you'll identify later.

  • Save the file with Ctrl+S (Windows) or Cmd+S (macOS).

Your Materials sheet should now show 4 rows of inventory data beneath the header row.

Populate the WorkOrders sheet with duplicated customer data

The WorkOrders sheet is where the worst anti-patterns live. Customer contact information is repeated on every single row for the same customer, and materials are referenced by name instead of any kind of ID.

  • Click the WorkOrders tab.
  • In row 1, type these column headers across cells A1 through K1:
A1: OrderID
B1: CustomerName
C1: CustomerEmail
D1: CustomerPhone
E1: ProductName
F1: MaterialName
G1: Quantity
H1: Unit
I1: DueDate
J1: Status
K1: Priority
  • Starting in row 2, enter the first 5 work orders using the data below. Notice that Apex Industries appears multiple times with identical contact info repeated:
Row 2: WO-001 | Apex Industries | orders@apexindustries.com | 555-0101 | Mounting Bracket | Steel Sheet 4mm | 100 | pcs | 2026-07-25 | In Progress | High
Row 3: WO-002 | Apex Industries | orders@apexindustries.com | 555-0101 | Support Frame | Steel Sheet 4mm | 50 | pcs | 2026-08-01 | Pending | Medium
Row 4: WO-003 | BuildRight Corp | procurement@buildright.com | 555-0202 | Pipe Connector | Aluminum Rod 10mm | 200 | pcs | 2026-07-20 | Complete | High
Row 5: WO-004 | Delta Manufacturing | delta.orders@deltamfg.com | 555-0303 | Seal Assembly | Rubber Gasket Kit | 75 | pcs | 2026-08-10 | Pending | Low
Row 6: WO-005 | Apex Industries | orders@apexindustries.com | 555-0101 | Bracket Assembly | Industrial Adhesive | 60 | pcs | 2026-07-18 | DONE | Medium
  • Now enter rows 7 through 11 to complete the data set:
Row 7: WO-006 | BuildRight Corp | procurement@buildright.com | 555-0202 | Flange Adapter | Steel Sheet 4mm | 120 | pcs | 2026-07-22 | In Progress | High
Row 8: WO-007 | Delta Manufacturing | delta.orders@deltamfg.com | 555-0303 | Valve Housing | Aluminum Rod 10mm | 80 | pcs | 2026-08-05 | Pending | Medium
Row 9: WO-008 | Apex Industries | orders@apexindustries.com | 555-0101 | End Cap | Rubber Gasket Kit | 150 | pcs | 2026-07-15 | Completed | Low
Row 10: WO-009 | BuildRight Corp | procurement@buildright.com | 555-0202 | Joint Connector | Industrial Adhesive | 90 | pcs | 2026-07-28 | In Progress | Medium
Row 11: WO-010 | Delta Manufacturing | delta.orders@deltamfg.com | 555-0303 | Support Plate | Steel Sheet 4mm | 110 | pcs | 2026-07-30 | Pending | High

Where are the deliberate anti-patterns?

Look at the Status column. You have "In Progress", "Pending", "Complete", "DONE", and "Completed" all meaning similar things. This inconsistency is intentional.

Apex Industries appears in rows 2, 3, 6, and 9 with its email and phone duplicated every time. If they changed their phone number, you'd need to update 4 rows manually. That's an update anomaly you'll document in your audit.

  • Save the file with Ctrl+S (Windows) or Cmd+S (macOS).

Your WorkOrders sheet should now show 10 data rows with 11 columns of production order information.

Dates showing as numbers?

If your DueDate column shows numbers like 46222 instead of dates, select the DueDate column, right-click, choose Format Cells, pick the Date category, and select a date format like YYYY-MM-DD.

Help me fix date formatting in LibreOffice Calc

Populate the Schedule and QualityChecks sheets

The Schedule sheet assigns work orders to production slots, and QualityChecks records inspection results. Both reference OrderIDs by text match with no validation.

  • Click the Schedule tab.
  • Enter these headers in row 1 and data in rows 2 through 6:
A1: OrderID | B1: WorkStation | C1: StartDate | D1: EndDate | E1: AssignedTo | F1: Shift | G1: Notes

Row 2: WO-001 | Station 1 | 2026-07-15 | 2026-07-20 | Mike Chen | Morning | Priority order - expedite
Row 3: WO-001 | Station 2 | 2026-07-21 | 2026-07-24 | Mike Chen | Morning | Assembly phase
Row 4: WO-002 | Stn 1 | 2026-07-25 | 2026-07-30 | Sarah Kim | Afternoon |
Row 5: WO-003 | WS-3 | 2026-07-10 | 2026-07-18 | James Obi | Morning | Completed ahead of schedule
Row 6: WO-006 | Station 1 | 2026-07-19 | 2026-07-22 | Sarah Kim | Night | Rush order

Another hidden anti-pattern

Look at the WorkStation column: "Station 1", "Stn 1", and "WS-3" all refer to physical workstations but use completely inconsistent naming. You could never reliably filter or group by workstation with data like this.

  • Click the QualityChecks tab.
  • Enter these headers and data:
A1: OrderID | B1: InspectionDate | C1: Inspector | D1: Result | E1: DefectType | F1: Notes

Row 2: WO-003 | 2026-07-18 | Lisa Park | Pass | | All units within tolerance
Row 3: WO-003 | 2026-07-17 | Lisa Park | Conditional Pass | Surface finish | Minor scratches on 3 units
Row 4: WO-001 | 2026-07-20 | Tom Rivera | Fail | Dimensional | 5 units out of spec on hole alignment
Row 5: WO-005 | 2026-07-18 | Lisa Park | Pass | | Standard check
Row 6: WO-008 | 2026-07-15 | Tom Rivera | Pass | | All OK
  • Save the file with Ctrl+S (Windows) or Cmd+S (macOS).

Click through all four sheet tabs to confirm each one has its headers and data populated.

Add cross-sheet VLOOKUP formulas and conditional formatting

In a real legacy spreadsheet, the sheets talk to each other through VLOOKUPs. These formulas act as pseudo-relationships between sheets, and they break silently if anyone deletes or reorders a row.

  • Click the WorkOrders tab.
  • Click cell L1 and type the header MaterialCost.
  • Click cell L2 and enter this VLOOKUP formula that pulls the unit cost from the Materials sheet:
=VLOOKUP(F2;Materials.$A:$C;3;0)

What does this formula do?

This formula takes the MaterialName in cell F2 and searches for it in the first column of the Materials sheet (column A). When it finds a match, it returns the value from column 3 (UnitCost).

LibreOffice Calc uses semicolons (;) as argument separators and a dot (.) between the sheet name and cell range. The 0 at the end means exact match only.

  • Confirm cell L2 now shows 45.50 (the cost of Steel Sheet 4mm).
  • Copy cell L2 and paste it into cells L3 through L11 to fill the formula for all rows.

Each row should now display the correct unit cost pulled from the Materials sheet.

  • Click cell M1 and type the header TotalCost.
  • In cell M2, enter this formula to calculate total material cost:
=L2*G2
  • Confirm cell M2 shows 4550 (45.50 multiplied by 100 units).
  • Copy M2 and paste into M3 through M11.

Now add a VLOOKUP on the Schedule sheet that pulls customer names from WorkOrders.

  • Click the Schedule tab.
  • Click cell H1 and type the header CustomerName.
  • In cell H2, enter this formula:
=VLOOKUP(A2;WorkOrders.$A:$B;2;0)
  • Confirm cell H2 shows Apex Industries.
  • Copy H2 and paste into cells H3 through H6.

Each schedule row now displays the customer associated with that order.

Finally, add conditional formatting to highlight overdue work orders.

  • Switch back to the WorkOrders tab.
  • Select the range I2:I11 (the DueDate values).
  • Click Format in the menu bar, then Conditional, then Condition.
  • In the Conditional Formatting dialog, set the first dropdown to Formula is.
  • In the formula field, type:
I2<TODAY()
  • In the Apply Style dropdown, select New Style.
  • In the style dialog, go to the Background tab and choose a red or orange color.
  • Click OK to save the style, then click OK to close the Conditional Formatting dialog.

What does this conditional formatting do?

The formula I2<TODAY() checks if the due date in each row is before today's date. If it is, the cell gets highlighted in the color you chose. This is a business rule (overdue detection) encoded entirely in formatting rather than in any documented specification.

  • Save the file with Ctrl+S (Windows) or Cmd+S (macOS).

Before you check your work, predict: how many DueDate cells do you expect to see highlighted as overdue based on today's date?

Look at the DueDate column. Any dates that have already passed (like 2026-07-15, 2026-07-18, 2026-07-20) should now be highlighted in your chosen color. The number of highlighted cells depends on today's date, but you should see at least a few colored cells.

No cells are highlighted?

Make sure you selected the date cells (I2:I11) before opening the Conditional Formatting dialog. Also confirm that your dates are stored as actual date values, not as plain text. You can verify by clicking a date cell and checking that the formula bar shows a date, not just text characters.

Help me troubleshoot conditional formatting in LibreOffice Calc

Also confirm your VLOOKUPs are still resolving correctly.

  • Click cell L5 on the WorkOrders sheet. It should show 12.00 (the cost of Rubber Gasket Kit).
  • Click the Schedule tab and check cell H5. It should show BuildRight Corp (the customer for WO-003).

✔️ Awesome, I've got everything!

Great. Double check you've saved your file, then move on to the next step.

ⓧ I'd like to double check my sheets

Scroll through each of the four sheet tabs and confirm:

  • WorkOrders has 11 columns (A through K) plus MaterialCost (L) and TotalCost (M), with 10 data rows.
  • Materials has 7 columns (A through G) with 4 data rows.
  • Schedule has 7 columns (A through G) plus a CustomerName column (H), with 5 data rows.
  • QualityChecks has 6 columns (A through F) with 5 data rows.
  • The VLOOKUP in WorkOrders L2 shows 45.50 and the VLOOKUP in Schedule H2 shows Apex Industries.
  • At least some DueDate cells have colored backgrounds from the conditional formatting.

Your legacy spreadsheet is now a realistic mess of duplicated data, inconsistent values, and hidden business logic. Next up, you'll put on your analyst hat and systematically document everything this workbook does for the business.

Audit the System and Document Business Logic

Your workbook is alive with sample data, formulas, and cross-sheet references. You now have a realistic legacy system sitting in front of you.

You cannot design a web replacement without first understanding what the spreadsheet actually does for the business. In this step, you will read the system as a user would, documenting every data flow and hidden rule before you try to normalize anything.

In this step, get ready to:
  • Create a system audit document and inventory every sheet.
  • Extract business rules hidden inside formulas.
  • Document pain points that justify migrating off Excel.
Create the system audit and inventory each sheet

Every migration starts with a discovery phase. You need a single document that captures what the spreadsheet contains, how sheets talk to each other, and what each formula is really doing for the business.

  • In VS Code, create a new file called system-audit.md in your project workspace.
  • Open production-tracker.xlsx in LibreOffice Calc side by side so you can reference it as you write.
  • Start the audit by adding the system overview and the first sheet's inventory. Copy the following into system-audit.md:
# SteelWorks Manufacturing - Production System Audit

## System Overview

The SteelWorks production management system is an Excel workbook (production-tracker.xlsx) with four sheets used by the manufacturing team to track work orders, materials, scheduling, and quality control.

Current users: 3 (production manager, floor supervisor, QA lead)
File location: Shared network drive
Last known size: ~2000 rows across all sheets

## Sheet Inventory

### Sheet 1: WorkOrders
- Purpose: Track customer orders through production
- Columns: OrderID, CustomerName, CustomerEmail, CustomerPhone, ProductName, MaterialName, Quantity, Unit, DueDate, Status, Priority
- Row count: ~500 active orders
- Notable: Customer info repeated on every row for same customer. Material referenced by name (not ID). Status values inconsistent (mix of "Complete", "Completed", "Done").

What does this section do?

The System Overview gives a future development team the big picture: what the workbook is, who uses it, and how large it has grown.

The Sheet Inventory entry for WorkOrders documents its columns and flags the notable problems: duplicated customer info across rows, name-based material references, and inconsistent status values. These are the exact anti-patterns you built into the workbook in Step 1.

  • Save system-audit.md.
  • Use VS Code's Markdown preview (click the split-page icon in the top right of the editor, or press Cmd+Shift+V on macOS / Ctrl+Shift+V on Windows) to confirm your headings render correctly.

You should see the title, overview paragraph, and the WorkOrders sheet entry formatted with bullet points.

  • Now add the remaining three sheets and the cross-sheet dependency map. Append the following below your WorkOrders entry:
### Sheet 2: Materials
- Purpose: Track raw material inventory
- Columns: MaterialName, Category, UnitCost, StockQty, ReorderLevel, Supplier, SupplierContact
- Row count: ~50 materials
- Notable: MaterialName is the only identifier (no ID). Supplier info embedded in same row (not normalized).

### Sheet 3: Schedule
- Purpose: Assign work orders to production slots
- Columns: OrderID, WorkStation, StartDate, EndDate, AssignedTo, Shift, Notes
- Row count: ~200 scheduled entries
- Notable: OrderID is a text match to WorkOrders sheet (no referential integrity). WorkStation is free text (inconsistent naming: "Station 1", "Stn 1", "WS-1").

### Sheet 4: QualityChecks
- Purpose: Record quality inspections on completed orders
- Columns: OrderID, InspectionDate, Inspector, Result, DefectType, Notes
- Row count: ~300 inspections
- Notable: Multiple inspections per order (re-inspections). Result is free text ("Pass", "Fail", "Conditional Pass"). No link validation to WorkOrders.

## Cross-Sheet Dependencies

1. Schedule.OrderID -> WorkOrders.OrderID (VLOOKUP to pull CustomerName and ProductName)
2. WorkOrders.MaterialName -> Materials.MaterialName (VLOOKUP to pull UnitCost for cost calculations)
3. QualityChecks.OrderID -> WorkOrders.OrderID (manual reference, no formula validation)

What does this section do?

Each sheet entry captures the same three things: what real-world entity it represents, what columns it holds, and what problems you can already see (missing IDs, free-text references, no validation).

The Cross-Sheet Dependencies section maps how data flows between sheets. These are the pseudo-relationships that your future database schema will replace with proper foreign keys.

  • Save system-audit.md and refresh the Markdown preview.
  • Confirm all four sheets are listed and the three cross-sheet dependencies appear as a numbered list.

Sheets not rendering as separate sections?

Make sure each ### heading has a blank line above it. Markdown requires blank lines before headings to render them correctly.

Also check that there is no trailing whitespace after bullet items, which can break list rendering in some preview engines.

Why isn't my Markdown preview showing headings correctly?

Extract business rules from formulas

The formulas in your workbook encode business decisions that nobody wrote down anywhere else. If you migrate without capturing these, the replacement system will silently lose functionality.

  • Switch to production-tracker.xlsx in LibreOffice Calc.
  • Click on cells that contain formulas (the ones using IF, VLOOKUP, and conditional formatting rules) and read the formula bar at the top.
  • For each formula, translate it into a plain-English business rule. Append the following section to system-audit.md:
## Business Rules (Extracted from Formulas)

1. **Overdue Detection**: `=IF(TODAY()>E2, "OVERDUE", "On Track")` - A work order is overdue when the current date exceeds its due date and status is not "Complete"
2. **Cost Calculation**: `=VLOOKUP(F2,Materials!A:D,4,FALSE)*G2` - Total material cost = unit cost of the named material multiplied by quantity ordered
3. **Reorder Alert**: `=IF(D2<=E2, "REORDER", "OK")` - A material needs reordering when stock quantity drops to or below the reorder level
4. **Schedule Conflict**: Conditional formatting highlights rows where the same WorkStation has overlapping date ranges (manual visual check, not formula-enforced)
5. **Priority Escalation**: Orders with Priority "High" and status not "Complete" within 2 days of DueDate are highlighted red (conditional formatting rule)

Why document formulas as business rules?

Each formula is a business decision that someone made. =IF(TODAY()>E2, "OVERDUE", "On Track") is not just a formula. It is the rule that defines when a work order is considered late.

When you build the web replacement, these rules become application logic or database constraints. Missing even one means the new system behaves differently from what users expect.

  • Save system-audit.md and check the Markdown preview.
  • Count the numbered rules. You should see 5 business rules, each with the original formula and a plain-English translation.
Document the system's pain points

Pain points are the reason the migration is happening. Without documenting them, stakeholders may question why the existing system needs replacing at all.

  • Think about what goes wrong when multiple people use this workbook day-to-day. Consider what happens when data changes, when the file grows, and when someone makes a mistake.
  • Append the following pain points section to system-audit.md:
## Pain Points

### Data Integrity Issues
- **Update anomalies**: Customer "Apex Industries" appears in 47 rows. When they changed their phone number, only 12 rows were updated.
- **No referential integrity**: Deleting a material from the Materials sheet leaves orphaned references in WorkOrders. No error is raised.
- **Inconsistent values**: Status column contains "Complete", "Completed", "Done", "DONE" - all meaning the same thing.
- **No unique constraints**: Two materials could have the same name with no error.

### Operational Issues
- **Single-user bottleneck**: File locked when one person has it open. Production floor cannot update schedule while manager reviews orders.
- **No audit trail**: No record of who changed what, or when.
- **No access control**: Everyone can edit everything. QA lead can accidentally modify orders.
- **Formula fragility**: One accidentally deleted row breaks VLOOKUPs across sheets.

### Scalability Issues
- **Performance degradation**: File takes 30+ seconds to open with recalculation.
- **No historical data**: Old orders deleted to keep file manageable, losing production history.
- **No reporting**: Generating monthly reports requires manual filtering and copy-paste to a separate file.

What makes these pain points useful?

Each pain point maps to a specific capability that the web replacement must have. Update anomalies justify data normalization. The single-user bottleneck justifies a multi-user web app. No audit trail justifies adding a change log.

A development team reading this section immediately understands not just what to build, but why each feature matters to the business.

  • Save system-audit.md and refresh the Markdown preview.
  • Scroll through the full document. You should see three pain point categories: Data Integrity Issues, Operational Issues, and Scalability Issues.

Bold text not rendering in the preview?

Make sure each bold term uses double asterisks with no space between the asterisks and the text: **Update anomalies** not ** Update anomalies **.

My Markdown bold formatting isn't working in the preview. What's wrong?

✔️ Awesome, I've got everything!

Great. Double check you have saved system-audit.md and all sections are visible in the preview.

ⓧ I'd like to double check the full code

Your system-audit.md should contain the following sections in order. Scroll up and confirm each one is present:

  • System Overview (3 metadata lines: users, file location, size).
  • Sheet Inventory with entries for WorkOrders, Materials, Schedule, and QualityChecks (each with Purpose, Columns, Row count, Notable).
  • Cross-Sheet Dependencies (3 numbered items mapping VLOOKUP relationships).
  • Business Rules (5 numbered rules, each with the formula and plain-English translation).
  • Pain Points with three subsections: Data Integrity Issues (4 items), Operational Issues (4 items), Scalability Issues (3 items).

Before you move on, take a moment to predict: how many distinct real-world entities can you count from your sheet inventory and dependencies?

Open your completed system-audit.md in VS Code and scan it. You should find at least 4 entities (customers, materials, work orders, quality checks), 5 documented business rules, and pain points organized into three categories. That is the full discovery artifact a consultant would deliver.

You now have a complete system audit. Every entity, relationship, and hidden rule has been surfaced into a single document. Next up, you will take this flat, messy data and design a proper normalized data model that eliminates the pain points you just documented.

Design the Normalized Data Model

Your audit revealed the problem: the WorkOrders sheet mashes customer info, material info, and order data into a single row. If you tried to turn each sheet into a database table directly, you'd inherit all of those problems.

This step forces you to confront that head-on. You'll attempt the naive one-sheet-one-table mapping, watch it crumble under anomalies, and then build a proper normalized entity-relationship diagram in draw.io that solves those problems.

In this step, get ready to:
  • Attempt a naive 1:1 sheet-to-table mapping and document why it fails.
  • Create entity shapes for six normalized tables in draw.io.
  • Add attributes, keys, and relationship connectors with cardinality notation.
Attempt the naive 1:1 mapping

Before jumping to normalization, try the simplest possible approach: turn each of the four spreadsheet sheets into its own database table with the same columns. What could go wrong?

  • Switch back to VS Code where you have system-audit.md open.
  • At the bottom of your audit document, add a new section heading by pasting the following:
## Naive Mapping Analysis (1 Sheet = 1 Table)

If we create one database table per sheet with identical columns, the following anomalies occur:
  • Now think through what happens with a direct mapping. Consider the WorkOrders sheet: it has CustomerName, CustomerEmail, and CustomerPhone repeated on every row for the same customer.
  • Add the following analysis below your heading to document the three types of anomalies:
### Update Anomalies
- Changing a customer's email requires updating every row in work_orders where that customer appears. Miss one row and the data is inconsistent.
- Renaming a material requires updating every work_orders row that references it by name.

### Insertion Anomalies
- Cannot add a new customer without creating a work order for them (customer info only exists inside a work order row).
- Cannot add a new material without it being used in a work order (if we only have the WorkOrders table for that reference).

### Deletion Anomalies
- Deleting the last work order for a customer loses all customer contact information permanently.
- Deleting a material row leaves orphaned name references in work_orders with no way to validate them.

What does this analysis prove?

Each anomaly type exposes a different structural flaw in the flat design. Update anomalies mean you can never trust that duplicated data is consistent. Insertion anomalies mean you can't store facts independently. Deletion anomalies mean removing one thing destroys unrelated data.

The fix is data normalization: splitting entities into their own tables so each fact is stored exactly once, and relationships are maintained via foreign keys.

  • Save system-audit.md.

You now have a documented argument for why the naive approach fails. This is exactly what a consultant presents to stakeholders to justify the additional work of proper schema design.

Open draw.io and enable the Entity Relation shape library

With the problem clearly documented, you can now design the solution. You'll build an ER diagram that splits the flat data into properly normalized tables.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type draw.io and press Enter to open it. If you use the web version, navigate to app.diagrams.net in your browser instead.
  • Create a new blank diagram. When prompted for a filename, enter production-erd.drawio and save it in your project workspace folder.

The default shape libraries don't include ER diagram shapes. You need to enable the Entity Relation library first.

  • Click +More Shapes at the bottom of the left sidebar.
  • In the dialog that opens, locate the Software category.
  • Select Entity Relation.
  • Click Apply.

You should now see Entity Relation shapes in your left sidebar, including entity rectangles with attribute rows and relationship connectors.

Don't see Entity Relation in the list?

Make sure you're looking under the Software section, not General or Networking. The Entity Relation library is grouped with UML and other software diagram types.

Help me find the Entity Relation shape library in draw.io.

Create entity shapes for each normalized table

Each entity in your ER diagram represents one normalized table. You need six entities total, derived from decomposing the four flat sheets.

  • Drag an entity shape (the rectangle with a title row and attribute rows) from the Entity Relation library onto your canvas.
  • Double-click the title row and name it customers.
  • Create five more entity shapes and name them: materials, work_orders, work_order_materials, schedule_entries, quality_checks.
  • Arrange them on the canvas so that customers and materials are at the top (they have no dependencies), work_orders is in the middle, and the rest are below or beside it.

Why six tables from four sheets?

The WorkOrders sheet contained data from three entities mashed together: customers, the orders themselves, and the materials used. Splitting customers out eliminates the update anomaly. Adding the work_order_materials junction table properly handles the many-to-many relationship between orders and materials.

You should see six named entity rectangles on your canvas. Save your diagram now with Cmd+S (macOS) or Ctrl+S (Windows).

Add attributes, keys, and relationship connectors

Each entity needs its columns listed as attribute rows, with primary keys and foreign keys clearly marked. Then you'll connect them with lines that show cardinality.

  • Select the customers entity. Add attribute rows for each column. Select an existing row and press Ctrl+Enter (or Cmd+Enter on macOS) to duplicate it and create additional rows.
  • Enter these attributes for customers: id PK, name, email, phone, created_at.
  • Fill in the materials entity with: id PK, name, category, unit_cost, stock_qty, reorder_level, supplier_name, supplier_contact, updated_at.
  • Fill in the work_orders entity with: id PK, order_ref, customer_id FK, product_name, quantity, unit, due_date, status, priority, created_at, updated_at.
  • Fill in work_order_materials with: id PK, work_order_id FK, material_id FK, quantity_used.
  • Fill in schedule_entries with: id PK, work_order_id FK, workstation, start_date, end_date, assigned_to, shift, notes.
  • Fill in quality_checks with: id PK, work_order_id FK, inspection_date, inspector, result, defect_type, notes.

Resize entities to fit

If your attribute rows overflow the entity shape, drag the bottom edge of the rectangle down to make room. You can also collapse entities temporarily by clicking the minus icon in the title row.

Now connect the entities with relationship lines that show how they relate to each other.

  • Hover over the customers entity until its outline turns blue (not green connection points). Drag a connector from customers to work_orders. This creates a sliding connector that repositions itself cleanly when you move shapes.
  • Select the connector you just drew. In the Style tab of the Format panel on the right, use the drop-down lists to change the start and end connector styles to show 1:N cardinality (one customer has many work orders).
  • Double-click the connector line to add a text label. Type 1:N.
  • Draw a connector from work_orders to work_order_materials (1:N) and from materials to work_order_materials (1:N). Together these represent the N:M relationship between work orders and materials.
  • Draw a connector from work_orders to schedule_entries and label it 1:N.
  • Draw a connector from work_orders to quality_checks and label it 1:N.

What do these relationships mean?

Each 1:N connector means "one record on the left can relate to many records on the right." One customer places many orders. One work order has many schedule entries and many quality checks.

The work_order_materials junction table handles the N:M (many-to-many): one order uses many materials, and one material appears in many orders. The junction table sits between them with foreign keys pointing both directions.

  • Save your diagram with Cmd+S (macOS) or Ctrl+S (Windows).

Before you move on, take a moment to predict: does your diagram account for every piece of data in the original spreadsheet? Can you trace any row from WorkOrders through the normalized tables without losing information?

  • Look at your ER diagram and confirm: you see at least 6 entity shapes (customers, materials, work_orders, work_order_materials, schedule_entries, quality_checks), each with attribute rows including PK and FK markers, and connectors labeled with 1:N cardinality between them.

Connectors not sliding when you move entities?

Make sure you connected to the shape outline (blue highlight), not to fixed connection points (green crosses). Fixed points lock the connector in place. To fix an existing connector, disconnect it from the green point, then reconnect by hovering until the entire shape outline turns blue.

Help me fix my draw.io connectors so they slide properly.

Your ER diagram now captures the full normalized structure. Every entity the spreadsheet contained is represented as its own table, with relationships explicitly defined instead of hidden in VLOOKUPs. Next up, you'll turn this visual design into executable code: a complete PostgreSQL schema with constraints that enforce your business rules at the database level.

Write the Data Dictionary and PostgreSQL Schema

Your ER diagram shows the structure visually, but a development team needs exact specifications to build from.

In this step, you will turn your diagram into two precise deliverables: a data dictionary documenting every column's type, constraints, and origin, and a PostgreSQL DDL script that proves the design is executable.

In this step, get ready to:
  • Create a data dictionary mapping every column from spreadsheet origin to database destination.
  • Write the full PostgreSQL DDL with proper constraints encoding your business rules.
  • Review for completeness by tracing every business rule and relationship to a constraint.
Create the data dictionary

A data dictionary is the Rosetta Stone between your old system and your new one. It documents every column's data type, constraints, default value, and exactly where in the spreadsheet it came from.

  • In VS Code, create a new file called data-dictionary.md in your project workspace.
  • Start with the heading and the first two reference tables by pasting this content:
# SteelWorks Manufacturing - Data Dictionary

## Table: customers

| Column | Type | Constraints | Default | Source |
|--------|------|-------------|---------|--------|
| id | BIGINT | PK, GENERATED ALWAYS AS IDENTITY | auto | New (derived from WorkOrders.CustomerName) |
| name | TEXT | NOT NULL, UNIQUE | - | WorkOrders.CustomerName |
| email | TEXT | NOT NULL | - | WorkOrders.CustomerEmail |
| phone | TEXT | - | - | WorkOrders.CustomerPhone |
| created_at | TIMESTAMP | NOT NULL | NOW() | New |

What does this structure capture?

Each row documents one column of the database table. The key fields are:

  • Type tells the dev team exactly what PostgreSQL data type to use.
  • Constraints lists every rule enforced on that column (primary key, not null, unique, foreign key, check).
  • Source traces the column back to its origin in the spreadsheet, or marks it as "New" if it did not exist before.
  • Now add the materials table section below the customers section:
## Table: materials

| Column | Type | Constraints | Default | Source |
|--------|------|-------------|---------|--------|
| id | BIGINT | PK, GENERATED ALWAYS AS IDENTITY | auto | New (replaces MaterialName as identifier) |
| name | TEXT | NOT NULL, UNIQUE | - | Materials.MaterialName |
| category | TEXT | NOT NULL | - | Materials.Category |
| unit_cost | NUMERIC(10,2) | NOT NULL, CHECK > 0 | - | Materials.UnitCost |
| stock_qty | INTEGER | NOT NULL, CHECK >= 0 | 0 | Materials.StockQty |
| reorder_level | INTEGER | NOT NULL, CHECK >= 0 | - | Materials.ReorderLevel |
| supplier_name | TEXT | NOT NULL | - | Materials.Supplier |
| supplier_contact | TEXT | - | - | Materials.SupplierContact |
| updated_at | TIMESTAMP | NOT NULL | NOW() | New |

Why does materials have CHECK constraints documented?

The business rule "Reorder Alert" from your audit said stock must drop to or below the reorder level. Documenting CHECK >= 0 on both stock_qty and reorder_level prevents negative inventory values from ever entering the system.

  • Save data-dictionary.md.
  • You should see two table sections with the markdown table syntax rendering in your editor.

Now add the remaining four tables. The work_orders table is the most complex because it has foreign keys, CHECK constraints for valid status and priority values, and multiple timestamps.

  • Add the work_orders table section below materials:
## Table: work_orders

| Column | Type | Constraints | Default | Source |
|--------|------|-------------|---------|--------|
| id | BIGINT | PK, GENERATED ALWAYS AS IDENTITY | auto | New (replaces WorkOrders.OrderID) |
| order_ref | TEXT | NOT NULL, UNIQUE | - | WorkOrders.OrderID |
| customer_id | BIGINT | FK -> customers.id, NOT NULL | - | Derived from WorkOrders.CustomerName |
| product_name | TEXT | NOT NULL | - | WorkOrders.ProductName |
| quantity | INTEGER | NOT NULL, CHECK > 0 | - | WorkOrders.Quantity |
| unit | TEXT | NOT NULL | - | WorkOrders.Unit |
| due_date | DATE | NOT NULL | - | WorkOrders.DueDate |
| status | TEXT | NOT NULL, CHECK IN list | 'pending' | WorkOrders.Status (normalized) |
| priority | TEXT | NOT NULL, CHECK IN list | 'medium' | WorkOrders.Priority |
| created_at | TIMESTAMP | NOT NULL | NOW() | New |
| updated_at | TIMESTAMP | NOT NULL | NOW() | New |

Valid status values: 'pending', 'in_progress', 'complete', 'cancelled'
Valid priority values: 'low', 'medium', 'high'

Why document valid values separately?

The original spreadsheet had inconsistent status values ("Complete", "Completed", "Done"). Listing the normalized values explicitly tells the dev team exactly what the CHECK constraint will allow. No ambiguity survives the migration.

  • Add the work_order_materials junction table section:
## Table: work_order_materials (junction)

| Column | Type | Constraints | Default | Source |
|--------|------|-------------|---------|--------|
| id | BIGINT | PK, GENERATED ALWAYS AS IDENTITY | auto | New |
| work_order_id | BIGINT | FK -> work_orders.id, NOT NULL | - | Derived from WorkOrders row |
| material_id | BIGINT | FK -> materials.id, NOT NULL | - | Derived from WorkOrders.MaterialName -> materials.id |
| quantity_used | NUMERIC(10,2) | NOT NULL, CHECK > 0 | - | WorkOrders.Quantity (material-specific) |
| UNIQUE(work_order_id, material_id) | - | - | - | Prevents duplicate material assignments |

What is this junction table?

This table did not exist in the spreadsheet. It resolves the many-to-many relationship between work orders and materials. In the original workbook, a work order could reference multiple materials, but they were crammed into a single row. The junction table gives each material-to-order link its own row with a specific quantity.

  • Add the schedule_entries table section:
## Table: schedule_entries

| Column | Type | Constraints | Default | Source |
|--------|------|-------------|---------|--------|
| id | BIGINT | PK, GENERATED ALWAYS AS IDENTITY | auto | New |
| work_order_id | BIGINT | FK -> work_orders.id, NOT NULL | - | Schedule.OrderID -> work_orders.order_ref |
| workstation | TEXT | NOT NULL | - | Schedule.WorkStation (normalized) |
| start_date | DATE | NOT NULL | - | Schedule.StartDate |
| end_date | DATE | NOT NULL | - | Schedule.EndDate |
| assigned_to | TEXT | NOT NULL | - | Schedule.AssignedTo |
| shift | TEXT | NOT NULL, CHECK IN list | - | Schedule.Shift |
| notes | TEXT | - | - | Schedule.Notes |

Valid shift values: 'morning', 'afternoon', 'night'
Constraint: end_date >= start_date
  • Add the final table section for quality_checks:
## Table: quality_checks

| Column | Type | Constraints | Default | Source |
|--------|------|-------------|---------|--------|
| id | BIGINT | PK, GENERATED ALWAYS AS IDENTITY | auto | New |
| work_order_id | BIGINT | FK -> work_orders.id, NOT NULL | - | QualityChecks.OrderID -> work_orders.order_ref |
| inspection_date | DATE | NOT NULL | - | QualityChecks.InspectionDate |
| inspector | TEXT | NOT NULL | - | QualityChecks.Inspector |
| result | TEXT | NOT NULL, CHECK IN list | - | QualityChecks.Result (normalized) |
| defect_type | TEXT | - | - | QualityChecks.DefectType |
| notes | TEXT | - | - | QualityChecks.Notes |

Valid result values: 'pass', 'fail', 'conditional_pass'
  • Save data-dictionary.md.
  • Scroll through the file. You should see six table sections, each with a markdown table documenting every column, plus validity-value notes beneath work_orders, schedule_entries, and quality_checks.

✔️ Awesome, I've got everything!

Great. Confirm you have saved the file, then move on to the schema.

ⓧ I'd like to double check the full code

Scroll through your file and confirm these sections appear in order:

  • Title heading: "SteelWorks Manufacturing - Data Dictionary".
  • Table: customers (5 columns: id, name, email, phone, created_at).
  • Table: materials (9 columns: id, name, category, unit_cost, stock_qty, reorder_level, supplier_name, supplier_contact, updated_at).
  • Table: work_orders (11 columns plus validity notes for status and priority).
  • Table: work_order_materials (4 columns plus UNIQUE constraint row).
  • Table: schedule_entries (8 columns plus shift and date constraint notes).
  • Table: quality_checks (7 columns plus result validity note).

Markdown tables not aligning?

Markdown table alignment depends on the pipe characters lining up. Each row must have the same number of pipe-separated cells as the header row. If a cell is empty, leave the space between pipes blank (e.g., | - |).

Help me fix my markdown table formatting.

Write the PostgreSQL DDL schema

Now you will turn the data dictionary into executable SQL. The schema must create tables in dependency order: tables that are referenced by foreign keys must be created before the tables that reference them.

  • Create a new file called schema.sql in your project workspace.
  • Start with the header, DROP statements for clean re-runs, and the first CREATE TABLE by pasting this code:
-- SteelWorks Manufacturing - Production Management Schema
-- PostgreSQL DDL - Tables in dependency order

-- Drop tables if they exist (for clean re-runs)
DROP TABLE IF EXISTS quality_checks CASCADE;
DROP TABLE IF EXISTS schedule_entries CASCADE;
DROP TABLE IF EXISTS work_order_materials CASCADE;
DROP TABLE IF EXISTS work_orders CASCADE;
DROP TABLE IF EXISTS materials CASCADE;
DROP TABLE IF EXISTS customers CASCADE;

-- Customers: extracted from repeated data in WorkOrders sheet
CREATE TABLE customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    phone TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

What does this code do?

The DROP TABLE IF EXISTS ... CASCADE block lets you re-run this script from scratch without errors. Tables are dropped in reverse dependency order (dependent tables first).

GENERATED ALWAYS AS IDENTITY creates an auto-incrementing numeric ID. This replaces the text-based OrderID from the spreadsheet with a proper surrogate key.

  • Save schema.sql.
  • You should see the SQL syntax highlighted in VS Code, with keywords like CREATE TABLE and NOT NULL in a distinct color.
  • Add the materials table below the customers table:
-- Materials: normalized from Materials sheet (supplier info kept inline for simplicity)
CREATE TABLE materials (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    category TEXT NOT NULL,
    unit_cost NUMERIC(10,2) NOT NULL CHECK (unit_cost > 0),
    stock_qty INTEGER NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
    reorder_level INTEGER NOT NULL CHECK (reorder_level >= 0),
    supplier_name TEXT NOT NULL,
    supplier_contact TEXT,
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

What do these CHECK constraints enforce?

  • CHECK (unit_cost > 0) prevents a material from having a zero or negative price.
  • CHECK (stock_qty >= 0) prevents inventory from going below zero. The spreadsheet had no such guard.
  • NUMERIC(10,2) stores monetary values with exactly 2 decimal places, avoiding floating-point rounding.
  • Save schema.sql and confirm your file now has the DROP block plus two CREATE TABLE statements (customers and materials).
  • Add the work_orders table, which introduces foreign key references:
-- Work Orders: core production tracking, references customers
CREATE TABLE work_orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_ref TEXT NOT NULL UNIQUE,
    customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    product_name TEXT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit TEXT NOT NULL,
    due_date DATE NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'in_progress', 'complete', 'cancelled')),
    priority TEXT NOT NULL DEFAULT 'medium'
        CHECK (priority IN ('low', 'medium', 'high')),
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

What's happening here?

  • REFERENCES customers(id) ON DELETE RESTRICT means you cannot delete a customer who still has work orders. This solves the spreadsheet's deletion anomaly.
  • CHECK (status IN (...)) enforces exactly four normalized values. No more "Complete" vs "Completed" vs "Done" confusion.
  • order_ref keeps the original text-based OrderID as a human-readable reference, while id serves as the proper numeric primary key.
  • Save schema.sql.
  • Check that customer_id BIGINT NOT NULL REFERENCES customers(id) points to a table that already exists above it in the file. This is the dependency ordering at work.
  • Add the junction table and the schedule_entries table below work_orders:
-- Junction table: many-to-many between work orders and materials
CREATE TABLE work_order_materials (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    work_order_id BIGINT NOT NULL REFERENCES work_orders(id) ON DELETE CASCADE,
    material_id BIGINT NOT NULL REFERENCES materials(id) ON DELETE RESTRICT,
    quantity_used NUMERIC(10,2) NOT NULL CHECK (quantity_used > 0),
    UNIQUE (work_order_id, material_id)
);

-- Schedule: production slot assignments
CREATE TABLE schedule_entries (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    work_order_id BIGINT NOT NULL REFERENCES work_orders(id) ON DELETE CASCADE,
    workstation TEXT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    assigned_to TEXT NOT NULL,
    shift TEXT NOT NULL CHECK (shift IN ('morning', 'afternoon', 'night')),
    notes TEXT,
    CHECK (end_date >= start_date)
);

What do these tables do?

  • UNIQUE (work_order_id, material_id) prevents the same material from being assigned to the same work order twice. One row per material-order relationship.
  • ON DELETE CASCADE on work_order_id means if a work order is deleted, its material assignments and schedule entries are automatically cleaned up.
  • CHECK (end_date >= start_date) is a table-level constraint that prevents logically impossible schedules. The spreadsheet had no such protection.
  • Save schema.sql and confirm both REFERENCES point to tables defined above (work_orders and materials).
  • Add the final table for quality inspections:
-- Quality inspections: multiple inspections per work order allowed
CREATE TABLE quality_checks (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    work_order_id BIGINT NOT NULL REFERENCES work_orders(id) ON DELETE CASCADE,
    inspection_date DATE NOT NULL,
    inspector TEXT NOT NULL,
    result TEXT NOT NULL CHECK (result IN ('pass', 'fail', 'conditional_pass')),
    defect_type TEXT,
    notes TEXT
);

Why is defect_type nullable?

A passing inspection has no defect to record. Making defect_type nullable matches the real-world rule: the field is only relevant when result is 'fail' or 'conditional_pass'.

  • Save schema.sql.
  • Scroll through the file from top to bottom. You should see 6 DROP statements followed by 6 CREATE TABLE statements, each progressively referencing tables defined above it.

✔️ Awesome, I've got everything!

Great. Make sure the file is saved, then move on to the completeness review.

ⓧ I'd like to double check the full code

Scroll through your file and confirm these sections appear in this exact order:

  • Header comments (2 lines) and 6 DROP TABLE IF EXISTS statements.
  • CREATE TABLE customers (5 columns: id, name, email, phone, created_at).
  • CREATE TABLE materials (9 columns with CHECK constraints on unit_cost, stock_qty, reorder_level).
  • CREATE TABLE work_orders (11 columns with REFERENCES customers(id), CHECK on status and priority).
  • CREATE TABLE work_order_materials (4 columns with two FKs and UNIQUE constraint).
  • CREATE TABLE schedule_entries (8 columns with FK, shift CHECK, and end_date >= start_date CHECK).
  • CREATE TABLE quality_checks (7 columns with FK and result CHECK).

Seeing a syntax error in VS Code?

Check that every CHECK constraint that uses IN has matching parentheses and single-quoted strings. A common mistake is using double quotes (which mean column names in PostgreSQL) instead of single quotes for text values.

Also confirm every REFERENCES clause names a table and column that actually exist in the CREATE statements above it. A typo in the referenced table name will cause a failure at execution time.

Help me find the syntax error in my PostgreSQL schema.

Review for completeness

A schema without a completeness trace is a guess. You need to verify that nothing from the original system slipped through the cracks.

  • Open system-audit.md alongside schema.sql (use VS Code's split editor by right-clicking the tab and selecting Split Right).
  • Trace each business rule from your audit to a constraint in the schema:
  • "Overdue Detection" rule: the due_date DATE NOT NULL column in work_orders enables the application to compute overdue status. The rule moves from a spreadsheet formula to application logic.
  • "Cost Calculation" rule: unit_cost in materials and quantity_used in work_order_materials let the application compute costs with a JOIN instead of a VLOOKUP.
  • "Reorder Alert" rule: CHECK (stock_qty >= 0) and CHECK (reorder_level >= 0) protect the values. The comparison logic moves to the application.
  • "Schedule Conflict" rule: CHECK (end_date >= start_date) prevents invalid ranges. Overlap detection becomes a proper SQL query.
  • "Priority Escalation" rule: CHECK (priority IN ('low', 'medium', 'high')) enforces valid values. Escalation logic moves to the application.
  • Now verify that every relationship in your ER diagram has a matching FOREIGN KEY:
  • customers 1:N work_orders: matched by customer_id BIGINT NOT NULL REFERENCES customers(id) in work_orders.
  • work_orders N:M materials: matched by the work_order_materials junction table with two FKs.
  • work_orders 1:N schedule_entries: matched by work_order_id BIGINT NOT NULL REFERENCES work_orders(id) in schedule_entries.
  • work_orders 1:N quality_checks: matched by work_order_id BIGINT NOT NULL REFERENCES work_orders(id) in quality_checks.
  • Finally, check that no data from the original spreadsheet is lost. Open production-tracker.xlsx in LibreOffice Calc and verify every column across all four sheets has a destination in your data dictionary.

What about formulas?

Spreadsheet formulas do not migrate as database constraints. They become application logic (queries, computed views, or API endpoints). Your data dictionary documents the raw data. The business rules in your audit tell the dev team what logic to build on top of this schema.

Before you call this done, take a moment to predict: if you ran this schema against a real PostgreSQL database, would every FOREIGN KEY resolve? Would any NOT NULL column be missing data?

Trace through mentally. Every REFERENCES points to a PRIMARY KEY in a table created above it. Every NOT NULL column either has a DEFAULT value or will always be provided during an INSERT (because the spreadsheet always had that data). The schema is valid.

You now have a complete migration analysis package: an audit, an ER diagram, a data dictionary, and an executable schema. Next up, the Secret Mission challenges you to prove this design actually works with real data.

Secret mission

Migrate Sample Data Into Your Normalized Schema

Your schema looks great on paper, but can it actually hold real data? In this challenge, you'll write SQL INSERT statements that decompose flat spreadsheet rows into your normalized tables, proving the transformation works end-to-end.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs or cloud resources to worry about. Decide whether to keep your project files for future reference or delete them entirely.

Resources you used:

  • Local project files: system-audit.md, data-dictionary.md, schema.sql, production-erd.drawio, migrate-data.sql, production-tracker.xlsx.
  • Pre-installed tools (VS Code, LibreOffice Calc, draw.io). These remain on your system regardless of your choice below.

Keep everything running

No action needed. Choose this if you plan to revisit your migration artifacts, extend the schema, or use these documents as templates for analyzing other Excel systems.

  • Your project files remain in the folder on your Desktop where you created them.
  • You can reopen production-erd.drawio in draw.io any time to update the ER diagram.
  • If you later get access to a PostgreSQL instance, you can run schema.sql and migrate-data.sql to see the full database in action.

Pause - I'll come back to this later

Since all files are local, there is nothing to shut down or pause. Your files sit on disk and consume no memory or processing power when not open.

  • Close the files in VS Code and LibreOffice Calc to free up application memory.
  • Close draw.io if you have the diagram open.
  • Your files will be waiting in the same folder when you return.

Delete - I don't want to use this again

Remove all project files from your system. This is permanent.

  • Close any open project files in VS Code, LibreOffice Calc, and draw.io.
  • Delete the project folder that contains your files. On macOS or Linux, open Terminal and run:
cd ~/Desktop
rm -rf steelworks-migration

On Windows, open PowerShell and run:

cd ~\Desktop
Remove-Item -Recurse -Force steelworks-migration

Find your folder name

If you named your project folder something different, replace steelworks-migration with whatever folder name you used on your Desktop.

  • Confirm the folder is gone by running ls ~/Desktop (macOS/Linux) or ls ~\Desktop (Windows). Your project folder should no longer appear in the listing.

Nice Work!

Nice Work!

You just produced a complete migration analysis package for a legacy Excel production system. This is the exact deliverable set a consultant hands to a development team before building begins.

Here's what you learned:

  • Audited a messy, organically-grown Excel workbook and extracted its hidden business rules, data flows, and structural pain points into a professional system audit document.
  • Transformed flat spreadsheet data into a normalized relational model using draw.io with proper ER notation, primary keys, foreign keys, and cardinality connectors.
  • Wrote a complete PostgreSQL DDL schema with CHECK constraints, FOREIGN KEY relationships, and a data dictionary tracing every field from its spreadsheet origin to its database destination.
  • Secret Mission: Proved the design works end-to-end by writing INSERT statements that decompose flat spreadsheet rows into normalized tables, respecting foreign key dependencies and applying data transformations.

Ready to quiz yourself?