จัดโครงสร้างสคีมาของคุณจาก 1NF ถึง 3NF เพื่อกำจัดความผิดปกติ และรู้ว่าเมื่อใดควรดีนอร์มัลไลซ์อย่างรู้เท่าทัน
เปิดบทเรียนนี้ใน Kodokonสคีมาที่ไม่ดีจะสร้างต้นทุนให้คุณไปอีกหลายปี นอร์มัลไลเซชัน มุ่งเป้าไปที่สิ่งเดียว คือให้แต่ละข้อเท็จจริงถูกเก็บเพียง ครั้งเดียว เพื่อกำจัดความผิดปกติสามอย่าง ได้แก่ การอัปเดต (ต้องแก้อีเมลหนึ่งอันในสิบที่), การแทรก (ไม่สามารถเพิ่มสินค้าโดยไม่มีคำสั่งซื้อได้) และ การลบ (การลบคำสั่งซื้ออันสุดท้ายทำให้ข้อมูลลูกค้าหายไป) ลองดูสคีมาที่จงใจทำให้พังนี้
CREATE TABLE bad_orders (
id INTEGER PRIMARY KEY,
customer_name TEXT,
customer_email TEXT,
product_names TEXT,
total REAL
);
INSERT INTO bad_orders
(customer_name, customer_email,
product_names, total)
VALUES
('Alice', 'alice@mail.com',
'keyboard,mouse', 74.0),
('Alice', 'alice@mail.co',
'monitor', 199.0);รูปแบบบรรทัดฐานสามอันแรกในทางปฏิบัติ 1NF: ค่าต้องเป็นอะตอม - product_names = 'keyboard,mouse' ละเมิดข้อนี้ ทำให้ WHERE หรือ JOIN ใด ๆ กับสินค้าทำได้ยาก 2NF (คีย์ประกอบ): ทุกคอลัมน์ต้องขึ้นอยู่กับคีย์ ทั้งหมด - การเก็บราคาตามแคตตาล็อกของสินค้าไว้ในรายการคำสั่งซื้อที่ระบุด้วย (order, product) ละเมิดข้อนี้ เพราะราคาขึ้นอยู่กับสินค้าเท่านั้น 3NF: ห้ามคอลัมน์ที่ไม่ใช่คีย์ขึ้นอยู่กับคอลัมน์ที่ไม่ใช่คีย์อีกตัว - customer_email ซึ่งขึ้นอยู่กับ customer_name ละเมิดข้อนี้ จึงเกิดข้อมูลซ้ำ alice@mail.co ขึ้นมา นี่คือสคีมาที่แก้ไขแล้ว
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL
);
CREATE TABLE purchases (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES customers(id)
);
CREATE TABLE purchase_items (
purchase_id INTEGER NOT NULL
REFERENCES purchases(id),
product_id INTEGER NOT NULL
REFERENCES products(id),
quantity INTEGER NOT NULL,
PRIMARY KEY (purchase_id, product_id)
);PRAGMA foreign_keys = ON;
INSERT INTO customers (name, email)
VALUES ('Alice', 'alice@mail.com');
INSERT INTO products (name, price)
VALUES ('keyboard', 49.0), ('mouse', 25.0);
INSERT INTO purchases (customer_id) VALUES (1);
INSERT INTO purchase_items
(purchase_id, product_id, quantity)
VALUES (1, 1, 1), (1, 2, 1);
SELECT c.name, p.name AS product,
i.quantity, p.price
FROM purchase_items AS i
JOIN purchases AS pu ON pu.id = i.purchase_id
JOIN customers AS c ON c.id = pu.customer_id
JOIN products AS p ON p.id = i.product_id;แล้วเมื่อใดที่คุณควร ดีนอร์มัลไลซ์? เมื่อการอ่านกลายเป็นคอขวดที่วัดได้ เช่น การ join ห้าตารางบนหน้าจอสำคัญ, ค่าการรวมกลุ่มที่ถูกคำนวณใหม่ทุกครั้งที่เรนเดอร์ รูปแบบที่ซื่อตรงมีดังนี้: คอลัมน์แคช (purchases.total ที่ถูกตรึงไว้ ณ เวลาที่สั่งซื้อ - และทำเช่นนั้นได้อย่างชอบธรรม เพราะราคาที่จ่ายเป็นข้อเท็จจริงเชิงประวัติศาสตร์ ต่างจากราคาตามแคตตาล็อก), ตัวนับที่ดูแลโดยทริกเกอร์ หรือตารางรายงานที่สร้างใหม่เป็นชุด (batch) ราคาที่ต้องจ่ายคือ สำเนาแต่ละชุดอาจคลาดเคลื่อนได้ และตอนนี้เป็นโค้ดของคุณ ไม่ใช่เอนจิน ที่รับประกันความคงเส้นคงวา