[Database]AI Database Normalization Design

Summary: Let AI help you break a messy spreadsheet into a clean, duplication-free normalized database.

1. The Pain Point

Have you ever dealt with a sheet like this — an Excel sales table stretching twenty or thirty columns from left to right: order ID, customer, phone, address, product, unit price, quantity, salesperson, department... When a single order covers three items, the customer's name and phone number have to be copied over three times. One day you need to change a customer's phone number and you have to hunt across the whole sheet; worse still, a slip of the finger edits the wrong row's "unit price," and nobody knows which row holds the real number.

This kind of "wide and messy" sheet is, in technical jargon, an "un-normalized" table (one that hasn't been tidied up according to the rules yet). It works well enough in the short term, but as more people use it and the data grows, it becomes a breeding ground for errors — fix one spot and miss another, data won't line up, queries return wrong results.

Today we'll use AI (Qwen) as our consultant to break such a messy table apart properly, into several clean, duplication-free small tables. No need to memorize a textbook — just follow along.

2. What You'll Get

After finishing this article, you'll walk away with three things:

  1. A normalized structure: the original wide table is split into 5 small tables, each handling its own concern — a Customer table, a Product table, a Salesperson table, an Order table, and an Order Detail table.
  2. Crystal-clear primary and foreign keys: for each table you'll see at a glance which field is the Primary Key (PK — a little tag that uniquely pins down a row, like "Order ID"), and which field is a Foreign Key (FK — a field that points to another table's primary key to "claim the relationship," like the "Customer ID" in the Order table pointing to the Customer table).
  3. A ready-to-run table-creation SQL: SQL (Structured Query Language — the commands specifically used to build databases, create tables, and query data). The version below uses MySQL syntax, but it works in Access with minor tweaks.

Along the way we'll also explain the three "Normal Forms" (NF — a set of rules that keep tables free of duplication and chaos) — 1NF, 2NF, 3NF — in plain language, so that next time someone throws a table at you, you can tell at a glance which normal form it's stuck at.

3. Hands-on Practice

3.1 First, look at this "messy table"

Suppose you're the person handling orders at a small company, and you have an "Overall Sales Sheet" exported from the system. Here's what it looks like in its rawest form (note the last column, "Products Purchased"):

Order ID Order Date Customer Customer Phone Customer Address Products Purchased (ID | Name | Qty) Salesperson Salesperson Dept
D20240001 2024-01-05 张三 13800001111 北京市朝阳区 P001 机械键盘 1；P002 无线鼠标 2 小李 华北大区
D20240002 2024-01-06 李四 13900002222 上海市浦东区 P003 显示器 1 小王 华东大区

The problem is obvious at a glance:

  • The single cell "Products Purchased" crams in multiple products (ID, name, and quantity all mixed together) — it's awkward even for Excel to read, let alone use to build a database.
  • When an order covers several items, the information is squeezed into one cell, so you can't tally up "how many mechanical keyboards were sold" separately.

This "one cell holding multiple values" situation is the classic symptom of failing the First Normal Form. Next we'll break it down step by step with AI.

3.2 Step 1: Let AI "flatten" the table to 1NF

What 1NF (First Normal Form) is: strictly speaking, 1NF is about column atomicity — every cell (field) holds only one value and cannot be split further. The "one cell crammed with multiple products" above must be split apart. It's also fine to remember it loosely as "one row says one thing," but that's only the effect of flattening the multi-values, not the formal definition of 1NF (1NF itself only looks at "whether the cell is atomic").

More precisely: 1NF requires fields to be "atomic" (Atomic — the smallest indivisible unit), and forbids repeated "groups."

How to do it: split "Products Purchased" into three independent fields — Product ID, Product Name, Quantity — and turn one order covering several items into several rows. Zhang San's order (keyboard + mouse) becomes two rows:

Order ID Order Date Customer Customer Phone Customer Address Product ID Product Name Product Category Unit Price Qty Salesperson Salesperson Dept
D20240001 2024-01-05 张三 13800001111 北京市朝阳区 P001 机械键盘 外设 299.00 1 小李 华北大区
D20240001 2024-01-05 张三 13800001111 北京市朝阳区 P002 无线鼠标 外设 99.00 2 小李 华北大区
D20240002 2024-01-06 李四 13900002222 上海市浦东区 P003 显示器 显示器 899.00 1 小王 华东大区

Notice that the "key" of a single row is now two keys joined together: "Order ID" alone can't pin down a row (the same order has two rows), so you need to add "Product ID." This "two keys joined to serve as the primary key" is called a Composite Primary Key — remember this term, we'll need it in the next step.

At this point the table has passed 1NF: every cell is a single indivisible value (column atomicity is satisfied). After flattening, the intuitive result is "one row says one thing (one product within one order)" — but remember, this is the effect of flattening the multi-values; 1NF itself only looks at whether the cell is atomic. The flaw remains — Zhang San's information is copied in both rows of D20240001, heavily redundant. Let's move on to 2NF.

3.3 Step 2: Let AI remove "partial dependency" to reach 2NF

What 2NF (Second Normal Form) is: building on 1NF, you can't "determine the content by looking at only half the key."

More precisely: 2NF requires no Partial Dependency — that is, "a non-key field must not depend on only part of the composite primary key; it must depend on the whole key."

Compare against the table above, where the composite primary key is (Order ID, Product ID). Let's check, one by one, which "key" each non-key field listens to:

  • Customer, Phone, Address, Order Date, Salesperson, Department: actually these are determined by just the "Order ID" half of the key, having nothing to do with "Product ID" → this is a Partial Dependency, and must be split out.
  • Product Name, Product Category, Unit Price: these are determined by just the "Product ID" half of the key, unrelated to "Order ID" → also a Partial Dependency, and must be split out too.
  • The Quantity: must be determined by both "Order ID + Product ID" together (only for the same order and same product is there a unique quantity) → it depends on the whole key, so it stays.

How to split (have AI divide the tables according to this logic):

  • Pull the information that "follows only the Order ID," together with the Order ID, into a standalone Order table.
  • Pull the information that "follows only the Product ID," together with the Product ID, into a standalone Product table.
  • The remaining "Order ID + Product ID + Quantity" forms a standalone Order Detail table (it's the bridge for the many-to-many relationship).

After splitting it looks like this (don't worry yet about whether Customer and Salesperson need further splitting — that's a 3NF matter):

  • Order table: Order ID (PK), Order Date, Customer, Customer Phone, Customer Address, Salesperson, Salesperson Dept
  • Product table: Product ID (PK), Product Name, Product Category, Unit Price
  • Order Detail table: Order ID + Product ID (together as the composite primary key), Quantity

At this point 2NF is satisfied: every non-key field in the Order Detail table (currently only "Quantity") honestly depends on the whole key.

3.4 Step 3: Let AI remove "transitive dependency" to reach 3NF

What 3NF (Third Normal Form) is: building on 2NF, you can't "determine the content by taking a detour."

More precisely: 3NF requires no Transitive Dependency — that is, "non-key field A determines non-key field B, and B then determines C, so C depends on the primary key via a detour," and this situation must be split.

Look closely at the Order table we just split out: it contains two fields, "Salesperson" and "Salesperson Dept." One salesperson belongs to only one department, so "Salesperson → Salesperson Dept" holds; and "Salesperson" itself follows the "Order ID" (the primary key). So the chain becomes: Order ID → Salesperson → Salesperson Dept.

This is a transitive dependency — "Salesperson Dept" depends on the primary key only by taking a detour. 3NF removes it: pull the salesperson out into a standalone Salesperson table, and leave only a "Salesperson ID" in the Order table to point to it.

Similarly, customer information (Customer, Phone, Address) essentially belongs to the independent person/entity "Customer," and should have its own table rather than being mixed into the order — this both removes redundancy and avoids having to change the phone number across the whole sheet when a customer switches numbers. So we also pull out a Customer table, and leave only "Customer ID" in the Order table to point to it.

The final 3NF structure — 5 tables:

Table Fields Primary / Foreign Key
Customer Cust. ID, Cust. Name, Cust. Phone, Cust. Address Cust. ID (PK)
Product Prod. ID, Prod. Name, Prod. Category, Unit Price Prod. ID (PK)
Salesperson Sales. ID, Sales. Name, Sales. Dept Sales. ID (PK)
Order Order ID, Order Date, Cust. ID, Sales. ID Order ID (PK); Cust. ID (FK→Customer), Sales. ID (FK→Salesperson)
Order Detail Order ID, Prod. ID, Qty (Order ID, Prod. ID) Composite PK; Order ID (FK→Order), Prod. ID (FK→Product)

Tip: Every "entity table" (Customer, Product, Salesperson) gets a stable ID as its primary key. Don't use "name" as the primary key — names can collide (two "Zhang San") and can change, so they make unreliable keys. An ID is the stable choice.

3.5 Design diagram: how tables "claim relationships"

Drawing the relationships above gives a clear "snowflake" structure. Tables connect to each other through foreign keys (FK):

        ┌─────────────┐
        │  Customer   │
        │ PK Cust.ID  │
        └──────┬──────┘
               │ 1
               ▼ N
   ┌─────────┐      ┌──────────────────┐      ┌──────────┐
   │  Order  │1────N│   Order Detail   │N────1│ Product  │
   │ PK Ord. │      │ PK(Ord.,Prod.ID) │      │ PK Prod. │
   └────┬────┘      └──────────────────┘      └──────────┘
        │ 1
        ▼ N
   ┌─────────────┐
   │ Salesperson │
   │ PK Sales.ID │
   └─────────────┘

The relationships in one sentence each:

  • Customer 1 : N Order: one customer can place multiple orders.
  • Salesperson 1 : N Order: one salesperson can handle multiple orders.
  • Order 1 : N Order Detail: one order can contain multiple products.
  • Product 1 : N Order Detail: the same product can appear in many order details.

Foreign key landing points:

  • Order.Cust. ID → Customer.Cust. ID
  • Order.Sales. ID → Salesperson.Sales. ID
  • Order Detail.Order ID → Order.Order ID
  • Order Detail.Prod. ID → Product.Prod. ID

3.6 A ready-to-use table-creation SQL

The following is a MySQL table-creation script you can paste into the database and run as-is (the table names use Chinese). If your server's identifier character set supports Chinese (MySQL 8 defaults to utf8mb4), it's recommended to prefer wrapping Chinese identifiers in backticks for safety, e.g. `客户表` — the examples below leave the backticks off for readability; just add them yourself in your own database. If Chinese table names still aren't supported, switch to English names like t_customer. Each block is commented, so you can follow along easily:

sql
-- 客户表：存"谁买的"
CREATE TABLE 客户表 (
    客户编号   CHAR(6)       PRIMARY KEY,        -- 主键：唯一标识一个客户
    客户姓名   VARCHAR(20)   NOT NULL,
    客户电话   VARCHAR(20),
    客户地址   VARCHAR(100)
);

-- 产品表：存"卖的是什么"
CREATE TABLE 产品表 (
    产品编号   CHAR(6)       PRIMARY KEY,        -- 主键：唯一标识一件产品
    产品名称   VARCHAR(50)   NOT NULL,
    产品类别   VARCHAR(20),
    单价       DECIMAL(10,2) NOT NULL            -- DECIMAL 存金额，避免小数算错
);

-- 销售员表：存"谁卖的"（部门跟着销售员走，不进订单表，避免传递依赖）
CREATE TABLE 销售员表 (
    销售员编号 CHAR(4)       PRIMARY KEY,
    销售员姓名 VARCHAR(20)   NOT NULL,
    销售员部门 VARCHAR(20)
);

-- 订单表：一次购买的"头信息"
CREATE TABLE 订单表 (
    订单号     CHAR(10)      PRIMARY KEY,
    下单日期   DATE          NOT NULL,
    客户编号   CHAR(6)       NOT NULL,
    销售员编号 CHAR(4)       NOT NULL,
    -- 外键：认回客户表和销售员表
    FOREIGN KEY (客户编号)   REFERENCES 客户表(客户编号),
    FOREIGN KEY (销售员编号) REFERENCES 销售员表(销售员编号)
);

-- 订单明细表：订单和产品的"多对多桥"，用两把钥匙拼复合主键
CREATE TABLE 订单明细表 (
    订单号     CHAR(10)      NOT NULL,
    产品编号   CHAR(6)       NOT NULL,
    数量       INT           NOT NULL,
    -- 复合主键：同一订单里同一产品只许出现一行
    PRIMARY KEY (订单号, 产品编号),
    FOREIGN KEY (订单号)   REFERENCES 订单表(订单号),
    FOREIGN KEY (产品编号) REFERENCES 产品表(产品编号)
);

For Access users: Access's SQL syntax differs slightly, mainly in three places —

  1. Text types use TEXT(n) instead of VARCHAR(n), and long text uses MEMO;
  2. Date types use DATETIME instead of DATE;
  3. Auto-increment primary keys use AUTOINCREMENT (e.g. ID AUTOINCREMENT PRIMARY KEY); for foreign-key constraints it's more reliable to drag them manually in Access's "Relationships" view than to write SQL.

The rest — primary keys, foreign keys, and composite primary keys — work on exactly the same idea, so just carry them over to build tables and relationships in Access.

3.7 The complete prompt for Qwen (copy-ready)

Paste the following to Qwen, and it will produce a split plan and SQL for you. Just check it against the acceptance checklist in 3.2–3.6:

我有一张 Excel 销售表，原始字段是：订单号、下单日期、客户、客户电话、客户地址、买的商品（一个格子里写了"编号 名称 数量"，可能含多件）、销售员、销售员部门。
请帮我做下面几件事：
1）先判断这张表现在违反了第几范式（1NF/2NF/3NF），说清楚违反在哪；
2）按 1NF → 2NF → 3NF 的顺序，一步一步把它拆成多张规范的小表，每一步说明"拆掉了哪种依赖"；
3）给出每张表的表名、主键、字段，以及表与表之间的主外键关系；
4）给一份可直接执行的建表 SQL（MySQL 语法），表名可用中文，字段加中文注释；
5）最后告诉我，如果要在 Access 里实现，语法上要改哪几处。

How to verify the response (check off each item):

  • Did it first determine "which normal form it's stuck at" rather than dumping SQL right away?
  • When splitting, did it clearly state "whether it removed a partial dependency or a transitive dependency"?
  • Does every table have a primary key, and are cross-table fields connected with foreign keys?
  • Can the SQL actually be built (first create two tables in a small test database and insert a few rows to try)?
  • After splitting, is the original "one order, multiple products" information preserved — nothing lost?

4. Principle Recap

The essence of Normal Forms (NF) boils down to four words — divide and conquer. Each level you ascend eliminates a class of "data duplication / misalignment" hazards: 1NF governs "one cell, one value," first flattening the messy multi-values; 2NF governs "don't look at only half the key," kicking out information that relates to only part of the primary key into its own table; 3NF governs "no detours," breaking the "A determines B, B determines C" transitive chain so that every non-key field answers directly to the primary key. Following this path of splitting, data returns to its own tables and "claims relationships" via IDs (primary/foreign keys); change one place and only that place moves, and duplication and contradiction naturally dwindle.

5. Pitfall Guide

  1. Don't grind toward 3NF right from the start. For small projects, single-user use, and small data volumes, splitting to 2NF is often enough. Let AI first tell you which normal form you're stuck at, and split on demand — don't chase "normalization for normalization's sake" and overcomplicate simple things.
  2. Don't store "computed" columns in a table. For example, "amount = quantity × unit price" is computed; once stored, it easily drifts out of sync with the detail (you change the quantity but forget the amount). To look up an amount, compute it on the fly with a query (SELECT 数量*单价 …) or a View (a saved query used like a table) — don't hard-code it into the table.
  3. Don't use "changeable" fields as the key for your primary key. Customer names can collide and change, and product names can change too. Give every entity table a stable ID (Customer ID, Product ID, Salesperson ID) as the primary key — that's the most reliable.
  4. Don't put AI-generated SQL straight into production. First create two tables in a test database, insert a few rows, and run a query to verify — confirm it builds, queries, and that the relationships are correct — before using it for real.
  5. Higher normal forms aren't always better. The "wide table" commonly used for reports and statistics (multiple tables joined into one big sheet) is precisely deliberate Denormalization — trading a bit of normalization for query speed. Weigh normalization against performance by scenario; don't apply a one-size-fits-all rule.

6. Further Reading

If "what is a table, and how to create your first table in Access" still feels unfamiliar, first revisit B13 "Designing Data Tables" to lay the groundwork; after splitting tables, if you want to write your own queries to pull the data out, head to B14 "Ask AI for SQL Queries Without a Struggle" to learn how to get SQL from AI.

To take it up a level — from "can build a database" to "uses it fluently" — the next hearty dishes are: JOIN (multi-table queries that reassemble the split tables by their relationships), Index (a "table of contents" added to a table that makes queries lightning-fast), and View (a "virtual table" that stores common query results for on-demand use) — these are advanced topics unfolded at L4, so we'll meet again next time.