SQL資料函式庫設計最佳實踐

在資料函式庫設計中，清晰的命名規範和有效的約束控制是確保資料函式庫易於維護和查詢的關鍵。本文將介紹如何選擇合適的命名慣例，例如蛇形命名法，並說明如何避免使用可能導致問題的保留關鍵字和特殊字元。此外，我們還會探討不同約束型別的應用，例如 CHECK、UNIQUE 和 NOT NULL，以及如何利用這些約束來確保資料的完整性和一致性。最後，我們將深入比較自然鍵和代理鍵作為主鍵的優缺點，並提供在 PostgreSQL 中實作複合主鍵和自動遞增代理主鍵的程式碼範例，以幫助讀者更好地理解和應用這些概念。

資料函式庫設計的最佳實踐

在資料分析的世界裡，良好的資料函式庫設計是成功的關鍵。當你需要從數十個表格和數百萬行資料中挖掘出有價值的資訊時，一個組織良好、命名合理的資料函式庫將會帶來極大的幫助。本章將延續第7章的內容，介紹如何組織和最佳化SQL資料函式庫的實務方法。

遵循命名規範

不同的程式語言和SQL編碼者偏好不同的命名規範。常見的命名規範包括駝峰式命名法（camel case）、帕斯卡命名法（Pascal case）和蛇形命名法（snake case）。例如，berrySmoothie是駝峰式命名法，BerrySmoothie是帕斯卡命名法，而berry_smoothie則是蛇形命名法。

在這本文中，我們採用蛇形命名法，如表格us_counties_pop_est_2019所示。無論你偏好哪種命名規範，重要的是要保持一致性。檢查你的組織是否有相關的風格，並嚴格遵循它。

參照識別符號的影響

在PostgreSQL中，如果不使用雙引號包圍識別符號，系統會將其視為小寫處理。例如，以下兩個CREATE TABLE陳述式在本質上是相同的：

CREATE TABLE customers (
    customer_id text,
    --snip--
);

CREATE TABLE Customers (
    customer_id text,
    --snip--
);

第二個陳述式不會建立一個名為Customers的新表格，而是會丟擲錯誤，因為customers已經存在。要保留大小寫並建立一個名為Customers的表格，你需要使用雙引號：

CREATE TABLE "Customers" (
    customer_id serial,
    --snip--
);

然而，這樣做會使查詢變得複雜，因為你總是要記住參照該表格名稱。

參照識別符號的陷阱

使用引號雖然允許你使用特殊字元或保留關鍵字作為識別符號，但這也可能帶來問題。例如，使用"trees planted"作為列名會導致每次查詢時都需要使用引號，否則資料函式庫會將trees和planted視為不同的列並丟擲錯誤。

識別符號命名

綜合考慮，使用簡單、不加引號且一致的識別符號名稱是最佳實踐。以下是一些建議：

• 使用蛇形命名法（snake case），如trees_planted。
• 避免使用SQL保留關鍵字作為識別符號。
• 保持名稱簡潔明瞭。

透過遵循這些最佳實踐，你可以建立一個組織良好、易於維護的資料函式庫，從而提高資料分析的效率和準確性。

資料函式庫設計：識別符命名與約束控制

在資料函式庫設計中，識別符的命名對於資料的可讀性和維護性至關重要。適當的命名慣例可以使多字詞名稱更容易理解，例如 video_on_demand 比 videoondemand 更具可讀性。避免使用模糊的縮寫也是良好的實踐，例如在建立與旅行相關的資料函式庫時，使用 arrival_time 作為欄位名稱比 arv_tm 更為清晰。

表格命名慣例

對於表格名稱，建議使用複數形式。表格中儲存著多行資料，每一行代表一個實體的例項，因此使用複數名稱，如 teachers、vehicles 或 departments，可以更準確地反映表格的內容。不過，在某些情況下，也會出現例外，例如為了保留匯入的 CSV 檔案名稱作為表格名稱，特別是在一次性匯入的情況下。

識別符的長度限制

不同資料函式庫應用程式對識別符名稱的字元數限制各不相同。SQL 標準規定為 128 個字元，但 PostgreSQL 限制為 63 個字元，而舊版的 Oracle 系統則限制為 30 個字元。因此，在編寫可能在其他資料函式庫系統中重複使用的程式碼時，傾向於使用較短的識別符名稱是一個好的做法。

表格副本的命名

在建立表格副本時，應使用有助於稍後管理的名稱。一個有效的方法是在建立副本時，將日期（格式為 _YYYY_MM_DD）附加到表格名稱後，例如 vehicle_parts_2021_04_08。這樣做的一個額外好處是，表格名稱將按日期順序排列。

使用約束控制欄位值

除了資料型別外，約束（Constraints）可以用來進一步控制欄位可以接受的資料。約束允許根據規則和邏輯測試進一步指定可接受的值，從而避免「垃圾進，垃圾出」的現象，即低品質的資料導致分析結果不準確或不完整。良好的約束設計有助於維護資料的品質，並確保表格之間的關係完整性。

約束型別

SQL 中常用的約束型別包括：

• CHECK：只允許布林運算式評估為 true 的列
• UNIQUE：確保欄位或欄位群組中的值在表格中的每一列都是唯一的
• NOT NULL：防止欄位中出現 NULL 值

可以透過兩種方式新增約束：作為欄位約束或表格約束。欄位約束僅適用於該欄位，而表格約束則可以提供適用於一個或多個欄位的標準。

主鍵：自然鍵與代理鍵

主鍵是一欄或多欄的組合，其值能夠唯一識別表格中的每一列。主鍵是一種約束，對組成主鍵的欄位或欄位群組施加兩個規則：值必須是唯一的，且不能有缺失值。

使用現有欄位作為自然鍵

自然鍵使用表格中現有的欄位或欄位組合來滿足主鍵的要求，即對於每一列都是唯一的且永不為空。例如，在一個司機資料函式庫中，駕駛執照號碼（driver_id）可以作為自然鍵。但是，如果在全國範圍內編制駕駛執照資料函式庫，由於不同州可能獨立發出相同的駕駛執照號碼，因此單獨的 driver_id 欄位可能不具有唯一性。在這種情況下，可以透過結合 driver_id 和州名（st）來建立複合主鍵，以確保每一列的組合都是唯一的。

引入欄位作為代理鍵

代理鍵是一個單獨的欄位，用於儲存人工產生的值。當表格中沒有適合建立自然主鍵的資料時，可以使用代理鍵。代理鍵可以是資料函式庫自動產生的連續編號，例如使用 serial 資料型別或 IDENTITY 語法。

CREATE TABLE drivers (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

內容解密：

此範例展示瞭如何建立一個名為 drivers 的表格，其中包含一個自動遞增的 id 欄位作為主鍵，以及 first_name 和 last_name 欄位用於儲存司機的姓名。SERIAL 資料型別用於自動產生唯一的整數值作為 id。

CREATE TABLE driver_licenses (
    driver_id INTEGER,
    st VARCHAR(2),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    PRIMARY KEY (driver_id, st)
);

內容解密：

此範例展示瞭如何建立一個名為 driver_licenses 的表格，用於儲存駕駛執照資訊。其中，driver_id 和 st（州程式碼）共同組成複合主鍵，以確保每一列的組合都是唯一的。這個設計適用於全國範圍內的駕駛執照資料函式庫，因為單獨的 driver_id 可能不具有唯一性。

資料表主鍵設計考量

在資料函式庫設計中，主鍵的選擇是一項重要的決策。主鍵用於唯一識別資料表中的每一筆記錄，並且在資料函式庫的完整性維護中扮演著關鍵角色。開發者通常會在自然鍵（Natural Key）和代理鍵（Surrogate Key）之間做出選擇。

自然鍵與代理鍵的比較

自然鍵是指資料表中已經存在的欄位或欄位組合，可以唯一識別每一筆記錄。例如，在一個儲存駕駛執照資料的資料表中，駕駛執照號碼可以作為自然鍵。另一方面，代理鍵是指一個與資料本身無關，但用於唯一識別記錄的欄位，通常是一個自動遞增的整數或UUID。

自然鍵的優缺點

• 優點：
  • 資料表中已經存在，不需要額外新增欄位。
  • 具有實際意義，可以減少在查詢時需要進行的表格連線操作。
• 缺點：
  • 如果資料發生變化，違反了主鍵的唯一性或非空性要求，則需要修改資料表的結構。

代理鍵的優缺點

• 優點：
  • 不受資料變化的影響，具有更好的彈性。
  • 保證了唯一性。
• 缺點：
  • 需要額外新增欄位，增加了儲存空間的需求。

主鍵設計例項

以下是一個建立單一欄位主鍵的範例，使用 PostgreSQL 資料函式庫。首先，我們建立一個名為 natural_key_example 的資料表，並將 license_id 欄位宣告為主鍵。

CREATE TABLE natural_key_example (
    license_id text CONSTRAINT license_key PRIMARY KEY,
    first_name text,
    last_name text
);

或者，我們也可以使用表格約束（Table Constraint）語法來宣告主鍵：

CREATE TABLE natural_key_example (
    license_id text,
    first_name text,
    last_name text,
    CONSTRAINT license_key PRIMARY KEY (license_id)
);

在上述範例中，我們使用了 CONSTRAINT 關鍵字來指定主鍵的名稱。如果省略 CONSTRAINT 關鍵字和主鍵名稱，PostgreSQL 將會自動為主鍵命名。

主鍵衝突範例

當我們嘗試插入重複的主鍵值時，PostgreSQL 將會丟擲錯誤。以下是一個範例：

INSERT INTO natural_key_example (license_id, first_name, last_name)
VALUES ('T229901', 'Gem', 'Godfrey');

INSERT INTO natural_key_example (license_id, first_name, last_name)
VALUES ('T229901', 'John', 'Mitchell');

執行第二個 INSERT 陳述式時，將會出現以下錯誤：

ERROR:  duplicate key value violates unique constraint "license_key"
DETAIL:  Key (license_id)=(T229901) already exists.

複合主鍵範例

如果單一欄位無法滿足主鍵的要求，我們可以使用複合主鍵（Composite Primary Key）。以下是一個範例：

CREATE TABLE natural_key_composite_example (
    student_id text,
    school_day date,
    present boolean,
    CONSTRAINT student_key PRIMARY KEY (student_id, school_day)
);

在這個範例中，我們使用了 student_id 和 school_day 兩個欄位的組合來作為複合主鍵。當我們嘗試插入重複的 student_id 和 school_day 組合時，PostgreSQL 將會丟擲錯誤。

UUID 作為主鍵的考量

有些開發者喜歡使用 UUID（Universally Unique Identifier）作為主鍵。UUID 是一個由 32 個十六進位制數字組成的程式碼，通常用於識別電腦硬體或軟體。PostgreSQL 提供了 UUID 資料型別以及兩個產生 UUID 的模組：uuid-ossp 和 pgcrypto。

然而，需要注意的是，由於 UUID 的長度較大，因此相比於其他選項（如 bigint），它們的效率較低。在選擇 UUID 作為主鍵時，需要仔細評估其優缺點。

UUID 的優缺點

• 優點：
  • UUID 的唯一性很高，可以避免主鍵衝突。
  • UUID 不受資料變化的影響，具有更好的彈性。
• 缺點：
  • UUID 的長度較大，儲存和索引的效率較低。

總之，在設計資料表的主鍵時，需要根據實際需求和資料特性選擇合適的主鍵型別，並考慮其優缺點，以確保資料函式庫的完整性和效能。

資料函式庫主鍵設計與實作

在資料函式庫設計中，主鍵（Primary Key）扮演著至關重要的角色，用於唯一識別表中的每一筆記錄。本文將探討主鍵的兩種主要型別：複合主鍵（Composite Primary Key）與代理主鍵（Surrogate Key），並介紹如何在 PostgreSQL 中實作這些主鍵。

複合主鍵的應用與限制

複合主鍵是由多個欄位共同組成的主鍵，能夠確保資料表中每一筆記錄的唯一性。以下是一個使用複合主鍵的範例，記錄學生的出勤情況：

INSERT INTO natural_key_composite_example (student_id, school_day, present)
VALUES(775, '2022-01-22', 'Y');
INSERT INTO natural_key_composite_example (student_id, school_day, present)
VALUES(775, '2022-01-23', 'Y');
INSERT INTO natural_key_composite_example (student_id, school_day, present)
VALUES(775, '2022-01-23', 'N');

內容解密：

1. 複合主鍵的定義：此範例中的 natural_key_composite_example 表使用了 student_id 和 school_day 作為複合主鍵，確保每個學生在同一天的出勤記錄是唯一的。
2. 重複鍵值的錯誤：第三個 INSERT 陳述式嘗試插入與第二個相同的 student_id 和 school_day 值，違反了主鍵的唯一性約束，因此會產生錯誤。
3. 錯誤訊息分析：錯誤訊息指出違反了唯一性約束 “student_key”，並詳細說明瞭重複的鍵值是 (775, 2022-01-23)。

自動遞增的代理主鍵

代理主鍵是一種人工產生的主鍵，通常使用自動遞增的整數值。在 PostgreSQL 中，可以使用 IDENTITY 語法來建立自動遞增的欄位。

CREATE TABLE surrogate_key_example (
    order_number bigint GENERATED ALWAYS AS IDENTITY,
    product_name text,
    order_time timestamp with time zone,
    CONSTRAINT order_number_key PRIMARY KEY (order_number)
);

INSERT INTO surrogate_key_example (product_name, order_time)
VALUES ('Beachball Polish', '2020-03-15 09:21-07'),
       ('Wrinkle De-Atomizer', '2017-05-22 14:00-07'),
       ('Flux Capacitor', '1985-10-26 01:18:00-07');

SELECT * FROM surrogate_key_example;

內容解密：

1. IDENTITY 語法的應用：order_number 欄位被定義為自動遞增的 bigint 型別，使用 GENERATED ALWAYS AS IDENTITY 語法。
2. 插入資料時省略主鍵欄位：在插入資料時，無需指定 order_number 的值，資料函式庫會自動產生一個唯一的值。
3. 查詢結果：查詢結果顯示 order_number 已自動填入遞增的值，分別為 1、2 和 3。

手動插入代理主鍵的值與調整 IDENTITY 序列

在某些情況下，可能需要手動插入特定的主鍵值，並調整 IDENTITY 序列以避免衝突。

INSERT INTO surrogate_key_example 
OVERRIDING SYSTEM VALUE 
VALUES (4, 'Chicken Coop', '2021-09-03 10:33-07');

ALTER TABLE surrogate_key_example ALTER COLUMN order_number RESTART WITH 5;

INSERT INTO surrogate_key_example (product_name, order_time)
VALUES ('Aloe Plant', '2020-03-15 10:09-07');

內容解密：

1. 手動插入特定主鍵值：使用 OVERRIDING SYSTEM VALUE 語法，可以手動插入指定的 order_number 值（此例中為 4）。
2. 調整 IDENTITY 序列：在手動插入特定值後，需要使用 ALTER TABLE 陳述式調整 order_number 的 IDENTITY 序列起始值，以避免下次插入時產生重複值。
3. 繼續插入新資料：調整序列後，可以繼續正常插入新資料，而 order_number 將自動遞增至下一個可用值（此例中為 5）。