數據庫表設計的 20 條黃金規則

在數據庫表設計中，雖然沒有固定的 20 個規定，但可以根據廣泛接受的數據庫設計原則和最佳實踐來總結出一些關鍵規定。以下是對這些規定的詳細解釋，並附上正反面例子進行對比說明：

1. 使用有意義的表名和列名

解釋：表名和列名應該清晰地描述它們所代表的數據或概念，使其他開發者能夠輕鬆理解表的用途。

正面例子：
表名：customer
列名：first_name, last_name, email_address

反面例子：
表名：table1
列名：col1, col2, col3

2. 使用單數形式命名錶

解釋：使用單數形式可以使表名更加簡潔，並且與面向對象編程中的類命名慣例保持一致。

正面例子：order, product, customer

反面例子：orders, products, customers

3. 避免在表名中使用空格或特殊字符

解釋：使用空格或特殊字符可能會導致在 SQL 查詢中需要額外的引號，增加出錯的可能性。

正面例子：order_details, product_category

反面例子：order details, product-category

4. 使用主鍵（Primary Key）

解釋：每個表都應該有一個唯一標識每條記錄的主鍵，通常是一個自增的整數字段。

正面例子：

CREATE TABLE customer (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

反面例子：

CREATE TABLE customer (
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

5. 正確使用數據類型

解釋：爲每個列選擇最合適的數據類型，既能確保數據的完整性，又能優化存儲空間和查詢性能。

正面例子：

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    created_at TIMESTAMP
);

反面例子：

CREATE TABLE product (
    product_id VARCHAR(50),
    product_name TEXT,
    price VARCHAR(20),
    created_at VARCHAR(50)
);

6. 使用外鍵（Foreign Key）維護引用完整性

解釋：外鍵用於在表之間建立關係，確保引用的數據始終有效，並防止孤立的記錄。

正面例子：

CREATE TABLE order (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

反面例子：

CREATE TABLE order (
    order_id INT PRIMARY KEY,
    customer_id INT
);

7. 規範化以減少數據冗餘

解釋：通過將數據分解到多個相關表中，可以減少冗餘並提高數據一致性。

正面例子：
將客戶地址信息單獨存儲在一個表中

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE address (
    address_id INT PRIMARY KEY,
    customer_id INT,
    street VARCHAR(100),
    city VARCHAR(50),
    country VARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

反面例子：
將所有信息存儲在一個表中

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    street VARCHAR(100),
    city VARCHAR(50),
    country VARCHAR(50)
);

8. 使用索引優化查詢性能

解釋：在經常用於搜索、排序或連接的列上創建索引可以顯著提高查詢性能。

正面例子：

CREATE INDEX idx_last_name ON customer(last_name);

反面例子：
不爲經常查詢的列創建索引，導致全表掃描。

9. 避免使用保留字作爲表名或列名

解釋：使用 SQL 保留字可能導致語法錯誤或需要特殊處理。

正面例子：user_account, item_order

反面例子：user, order

10. 使用一致的命名約定

解釋：在整個數據庫中保持一致的命名風格可以提高可讀性和可維護性。

正面例子：
全部使用小寫和下劃線：first_name, last_name, email_address

反面例子：
混合使用不同風格：firstName, LastName, Email_Address

11. 爲每個表添加創建和更新時間戳

解釋：這些時間戳字段有助於跟蹤記錄的創建和最後修改時間，對於審計和數據管理非常有用。

正面例子：

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

反面例子：
完全省略時間戳字段，或者手動更新時間字段，容易出錯或忘記更新。

12. 使用枚舉類型或查找表來限制可能的值

解釋：對於有限集合的值，使用枚舉類型或查找表可以確保數據的一致性和完整性。

正面例子：
使用枚舉類型：

CREATE TABLE order (
    order_id INT PRIMARY KEY,
    status ENUM('pending', 'processing', 'shipped', 'delivered')
);

或使用查找表：

CREATE TABLE order_status (
    status_id INT PRIMARY KEY,
    status_name VARCHAR(20)
);

CREATE TABLE order (
    order_id INT PRIMARY KEY,
    status_id INT,
    FOREIGN KEY (status_id) REFERENCES order_status(status_id)
);

反面例子：
使用普通的 VARCHAR 字段，允許任意值：

CREATE TABLE order (
    order_id INT PRIMARY KEY,
    status VARCHAR(20)
);

13. 避免過度規範化

解釋：雖然規範化可以減少數據冗餘，但過度規範化可能導致性能問題和複雜的查詢。在某些情況下，適度的非規範化是可以接受的。

正面例子：
在訂單表中保存訂單總額，而不是每次都從訂單明細中計算。

反面例子：
將每個屬性都拆分到單獨的表中，導致簡單查詢需要多次連接。

14. 使用適當的字符集和排序規則

解釋：選擇正確的字符集和排序規則可以確保正確處理多語言數據和排序。

正面例子：

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

反面例子：
使用默認的字符集和排序規則，可能導致某些語言的字符無法正確存儲或排序。

15. 爲大文本或二進制數據使用專門的數據類型

解釋：對於大型文本或二進制數據，使用專門的數據類型可以提高性能和管理效率。

正面例子：

CREATE TABLE document (
    document_id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    file_data MEDIUMBLOB
);

反面例子：
對所有數據都使用 VARCHAR 或 BLOB，不考慮數據的實際大小和用途。

16. 使用適當的約束（Constraints）

解釋：約束可以在數據庫級別強制執行業務規則，確保數據的完整性和一致性。

正面例子：

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0)
);

反面例子：
沒有使用約束，允許插入無效數據：

CREATE TABLE product (
    product_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    stock_quantity INT
);

17. 考慮使用存儲過程和觸發器

解釋：存儲過程和觸發器可以封裝複雜的業務邏輯，提高性能和一致性。

正面例子：
創建一個更新庫存的存儲過程：

DELIMITER //
CREATE PROCEDURE update_stock(IN product_id INT, IN quantity INT)
BEGIN
    UPDATE product
    SET stock_quantity = stock_quantity - quantity
    WHERE product_id = product_id;
END //
DELIMITER ;

反面例子：
在應用程序中實現所有業務邏輯，增加了出錯和不一致的風險。

18. 爲大型表考慮分區

解釋：對於非常大的表，使用分區可以提高查詢性能和管理效率。

正面例子：
按日期範圍分區的訂單表：

CREATE TABLE order (
    order_id INT,
    order_date DATE,
    customer_id INT,
    total DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

反面例子：
不分區的大型表可能導致查詢性能下降和管理困難。

19. 使用適當的命名前綴或後綴

解釋：使用前綴或後綴可以更清晰地表示表或列的用途或類型。

正面例子：

反面例子：
所有對象使用相同的命名方式，難以區分其類型或用途。

20. 記錄和維護數據庫設計文檔

解釋：雖然這不是直接的設計規則，但保持最新的數據庫設計文檔對於長期維護和團隊協作至關重要。

正面例子：

反面例子：
沒有文檔，依賴於開發人員的記憶或代碼註釋來理解數據庫結構。

這就是數據庫表設計的 20 條規定的詳細解釋和正反面例子。這些規定涵蓋了從命名約定到性能優化的各個方面，遵循這些規定可以幫助您創建一個結構良好、高效和易於維護的數據庫。記住，雖然這些是一般性的最佳實踐，但在特定情況下可能需要根據實際需求進行調整。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/ylIJyl1x8uePT0tbV0N06A

猜你喜歡