資料正規化完整指南：從 JSON 數據到結構化資料

在處理數據時，尤其是來自 JSON 檔案 的資料，經常會遇到 巢狀結構、不規則的欄位名稱，以及重複資訊。

如果數據沒有經過良好的整理與規範，不僅難以閱讀，也不易存入關聯式資料庫（如 MySQL、PostgreSQL）。

因此，我們需要透過 資料正規化（Data Normalization），將數據轉換成 有組織的結構，以利於後續查詢與分析。

本指南將 逐步解析 JSON 正規化的過程，並以 實際案例 為例，帶領你完成整個數據整理流程！

確認 JSON 資料結構

在進行正規化前，第一步是先 檢視 JSON 檔案的結構，確認它是 扁平結構（key-value）還是巢狀結構。

檢查要點

• 是否為單層 JSON？
  • 例如： { "id": 1, "name": "Alice" }
• 是否為巢狀結構？
  • 例如： { "user_id": 1, "name": "Alice", "orders": [ {"order_id": 101, "total": 50}, {"order_id": 102, "total": 30} ] }
• 是否有重複資訊？
  • 若 location 出現在多個地方，可能需要拆分成獨立表格。

解析 JSON 的方法

• 使用 JSON Formatter（如 jsonformatter.org）視覺化數據。
• 使用 Python 美化輸出 JSON：

import json
with open("data.json") as f:
    data = json.load(f)
print(json.dumps(data, indent=2))

• 用 pandas.json_normalize() 攤平成表格：

import pandas as pd
df = pd.json_normalize(data)
print(df.head())

找出數據間的關聯性

資料正規化的目標是 消除重複資訊，建立數據之間的關聯性，因此我們需要 識別主鍵（PK）與外鍵（FK）。

主要步驟

• 確認主鍵（Primary Key, PK）
  • 找出唯一識別碼（如 UID、id）。
• 識別外鍵（Foreign Key, FK）
  • 若 user_id 出現在 orders 內，就應該成為外鍵。
• 拆分巢狀結構
  • 例如 orders 內的資料應拆分到獨立表格。

🎯 解析範例

原始 JSON

{
  "UID": "669eab3226b3240c380463f1",
  "title": "新觀點音樂會",
  "showInfo": [
    {
      "time": "2025/02/19 19:30:00",
      "location": "高雄市鳳山區三多一路1號",
      "locationName": "衛武營國家藝術文化中心表演廳"
    }
  ]
}

正規化後

1️⃣ events 表：

2️⃣ show_info 表：

這樣一來，每場 showInfo 獨立存放，避免冗餘數據。

清理 & 重構數據

數據整理後，我們還需要確保：

• 欄位名稱一致（避免 UserID vs user_id）。
• 避免重複存放數據（locationName 不應存於 show_info）。
• 統一數據格式（如 日期格式 YYYY-MM-DD HH:MM:SS）。

轉換成適合的結構

數據整理完成後，可將其轉換為不同格式：

• 存入 MySQL/PostgreSQL

CREATE TABLE events (
    UID VARCHAR(255) PRIMARY KEY,
    title TEXT
);

CREATE TABLE show_info (
    showID INT PRIMARY KEY AUTO_INCREMENT,
    UID VARCHAR(255),
    time DATETIME,
    location TEXT,
    FOREIGN KEY (UID) REFERENCES events(UID)
);

• 轉為 DataFrame（方便分析）

df = pd.DataFrame(data)

• 存為 CSV 或 SQL

df.to_csv("data.csv", index=False)  # 存成 CSV
df.to_sql("table_name", conn, if_exists="replace")  # 存進 SQL

資料正規化：以「音樂表演資訊 | 政府資料開放平臺 (data.gov.tw)」為例

接下來，我們將以政府資料開放平臺（data.gov.tw）所提供的音樂表演資訊為範例。

逐步展示如何將 JSON 數據正規化，從第一正規化（1NF）到第三正規化（3NF），讓數據變得更加結構化，方便儲存、查詢與分析。

原始資料

政府資料開放平臺提供的 JSON 檔案包含了音樂表演資訊，其中包含演出名稱、演出時間、地點、售票資訊等，例如：

{
    "UID": "669eab3226b3240c380463f1",
    "title": "新觀點．新世界～Kimball Gallagher 2024台灣巡迴音樂會",
    "category": "1",
    "showInfo": [
        {
            "time": "2025/02/19 19:30:00",
            "location": "高雄市鳳山區三多一路1號",
            "locationName": "衛武營國家藝術文化中心表演廳",
            "onSales": "Y",
            "price": "",
            "latitude": "22.6230179238508",
            "longitude": "120.342434118507",
            "endTime": "2025/02/19 21:05:00"
        }
    ],
    "startDate": "2025/02/19",
    "endDate": "2025/02/19",
    "webSales": "https://www.opentix.life/program/1811063063948378113"
}

這段 JSON 包含了一場音樂表演的基本資訊，但有一些問題需要正規化來解決：

1. 巢狀結構（Nested Structure）：showInfo 是一個陣列，包含演出時間、地點等資訊，這在關聯式資料庫中不易直接存放。
2. 冗餘資訊：如果多場表演在相同地點，則locationName、latitude 和 longitude 可能會重複存儲。
3. 部分函數依賴（Partial Dependency）：locationName 依賴於 location，而不是 UID，這違反 2NF。
4. 傳遞依賴（Transitive Dependency）：latitude 和 longitude 依賴 location，這違反 3NF。

我們將按照 1NF → 2NF → 3NF 的方式來優化這組數據。

第一正規化（1NF）

條件

✅ 每個欄位只能有單一值，不能有巢狀結構。

問題點

"showInfo": [
  {
    "time": "2025/02/19 19:30:00",
    "location": "高雄市鳳山區三多一路1號",
    "locationName": "衛武營國家藝術文化中心表演廳",
    "onSales": "Y",
    "latitude": "22.6230179238508",
    "longitude": "120.342434118507"
  }
]

🔴 showInfo 為巢狀陣列，違反 1NF。

修正後

將 showInfo 拆分為獨立的 show_info 表，確保每筆資料為單一值：

第二正規化（2NF）

條件

✅ 非主鍵欄位需完全依賴主鍵，而非部分主鍵。

問題點

在原始的 show_info 表中：

• UID（演出活動的唯一識別碼）是 show_info 的主鍵。
• show_time 這個欄位 完全依賴 UID，因為每個表演場次都是與某個活動 UID 相關的。
• 問題點：locationName 並不是直接依賴 UID，而是依賴 location。
  • 錯誤的依賴關係： UID → location → locationName
  • 正確的做法：我們應該把 locationName 移到 locations 表，這樣 show_info 只需存 locationID。

修正後

拆分 locations 表：

更新 show_info 表：

這樣，locationName 就不會依賴 UID，確保所有非主鍵欄位都完全依賴於 UID。

這樣的修正確保：

1. show_info 表中的所有非主鍵欄位都依賴 UID，不再有部分函數依賴的問題。
2. locationName 現在完全依賴於 locationID，存放於 locations 表，符合 2NF 規範。

第三正規化（3NF）

條件

✅ 消除傳遞依賴，所有非主鍵欄位應完全依賴主鍵。

問題點

💡 在 2NF 階段，我們已經拆分了 locations 表

在 第二正規化（2NF） 時，我們發現 locationName 並不是 依賴 UID，而是依賴 location，所以我們把 locationName 獨立成 locations 表，如下：

📌 修正後的 2NF 表結構

show_info 表

locations 表

這樣 locationName 不再依賴 UID，而是依賴 locationID，因此符合 2NF。

💡 但 2NF 仍然存在問題

雖然 locationName 被拆開了，但 latitude 和 longitude 仍然存放在 show_info 表中，如下：

2NF 階段的 show_info 表

📌 問題出現了

• latitude 和 longitude 並不是依賴 UID，而是依賴 locationID，因為同一個 locationID（相同地點）會有相同的經緯度。
• 這樣的設計 違反了 3NF，因為 latitude 和 longitude 不應該存放在 show_info 表，而應該移到 locations 表中。

修正後

進一步拆分 locations 表：

在 第三正規化（3NF） 中，我們將 latitude 和 longitude 從 show_info 表移到 locations 表，讓它們只依賴 locationID。

🔹 show_info 表（修正後）

🔹 locations 表（修正後）

這樣，latitude 和 longitude 不再存於 show_info 表，而是存於 locations 表，確保所有非主鍵欄位都僅依賴於 locationID，從而符合 3NF。

補充：2NF 和 3NF　差異

📌 主要差異點：

🎯 關鍵差異

1. 2NF 處理「部分函數依賴」：
   • 某些欄位 沒有 ID，但不應該依賴 UID。
   • 需要 創建新的表 來存放這些欄位（例如 locations 表）。
2. 3NF 處理「傳遞依賴」：
   • 某些欄位 已經有 ID（例如 locationID），但還是間接依賴 UID。
   • 需要 移動欄位到正確的表，避免不必要的依賴。

📌 具體案例分析

假設我們有這張 show_info 表：

這張表的 主鍵（Primary Key, PK）是 UID，但有些欄位存在部分函數依賴和傳遞依賴的問題。

📌 🎯 第二正規化（2NF）

問題

• locationName 依賴 location，但 location 不是 show_info 的主鍵 UID。
• 這代表 locationName 不應該存放在 show_info，因為它不是由 UID 決定的，而是由 location 決定的。

解決方案

✅ 創建 locations 表，把 locationName 拆出去。

📌 🎯 第三正規化（3NF）

問題

• latitude 和 longitude 依賴 locationID，而 locationID 又依賴 UID，這就是 傳遞依賴。
• UID → locationID → latitude, longitude 這種 間接依賴 代表 latitude 和 longitude 不應該存放在 show_info 表，而應該存放在 locations 表。

解決方案

✅ 把 latitude 和 longitude 移到 locations 表，讓 show_info 只存 locationID。

📌 用簡單的話來解釋

💡 2NF

如果一個欄位（如 locationName）依賴 location，但 location 不是 show_info 的主鍵。

那麼這是部分函數依賴，我們應該 創建新的 locations 表，讓 show_info 只存 locationID。

💡 3NF

如果一個欄位（如 latitude 和 longitude）依賴 locationID，但 locationID 又依賴 UID，這就是傳遞依賴。

我們應該 把 latitude 和 longitude 移到 locations 表，確保 show_info 沒有任何 location 相關資訊，只存 locationID。

最終結果

events 表（活動基本資訊）

show_info 表（演出資訊）

locations 表（場地資訊）

結論

透過這些步驟，數據變得更整潔、更有組織，方便後續儲存與分析！ 🎶🎻