[Part 4] Database: Relational #1

Relationship in database

ฐานข้อมูลส่วนใหญ่มักมีหลาย table และเชื่อมโยงกันด้วยความสัมพันธ์บางอย่าง ใน session นี้เราจะมาเรียนรู้วิธีการออกแบบ การเก็บข้อมูลหลาย table และการจัดการกับความสัมพันธ์ต่างๆ ระหว่าง table กัน

Relationshop in real world

Entity Relationship Diagram (ERD)

ER Diagram เป็น Diagram ที่จะช่วยอธิบายโครงสร้าง Database ของระบบต่างๆที่ออกแบบมา อธิบายความสัมพันธ์ (Relationship) ของแต่ละ Entity รวมถึง Attributes ของ Entity นั้นๆ ผลการออกแบบโดยใช้ E-R Model สามารถแสดงได้ด้วยการเขียนแผนภาพที่เรียกว่า Entity Relationship  Diagram(ERD)  ซึ่งถือว่าเป็นเครื่องมือที่ใช้อธิบายองค์ประกอบและข้อกำหนดของฐานข้อมูล ที่นักวิเคราะห์และออกแบบระบบใช้เป็นสื่อกลางในการสื่อสารระหว่างผู้ใช้และนักพัฒนาโปรแกรม  เนื่องจากมีสัญลักษณ์ที่สื่อความหมายให้เข้าใจได้ง่าย โดยจะมีองค์ประกอบหลักๆอยู่ 3 ส่วนคือ

• Entity (Table)
• Attribute (Column)
• Relationship

Step to define ERD

1. Identifying Entities: กำหนดตารางทั้งหมดของระบบ เช่น products, users, categories, orders
2. Defining Attributes: กำหนดค่าในแต่ละตารางว่าเก็บข้อมูลอะไรบ้าง เช่น products(title, description, price), users(id, name, age), roles(id, role_name)
3. Specifing Relationships: กำหนดความสัมพันธ์ระหว่าง entity เช่น
   1. users <-> purchase <-> products
   2. products <-> has <-> categories
4. Drawing Entities: วาด entity ลงใน digram โดยใช้สัญลักษณ์สี่เหลี่ยม
5. Adding Attributes: เติม Attribute ของแต่ละ entity โดยใช้สัญลักษณ์วงกลม
6. Connecting Entities: วาดความสัมพันธ์ทั้งหมด โดยใช้สัญลักษณ์สี่เหลี่ยมข้าวหลามตัด
7. Specifying Cardinality: กำหนดรูปแบบของความสัมพันธ์
   1. One-to-One: ความสัมพันธ์แบบ 1:1 หมายถึงข้อมูลใน entity หนึ่งมีความสัมพนธ์กับข้อมูล 1 ตัวในอีก entity หนึ่ง เช่น product 1 ชิ้น มีได้ 1 category
   2. One-to-Many: ความสัมพันธ์แบบ 1:N หมายถึงข้อมูลใน entity 1 จะเชื่อมโยงได้หลายข้อมูลได้อีก entity หนึ่ง เช่น users 1 คน สามารถสั่งซื้อ ออเดอร์ได้ หลาย ออเดอร์
   3. Many-to-One: ความสัมพันธ์แบบ N:1 หมายถึงหลายๆ ข้อมูลใน entity หนึ่งมีความสัมพนธ์กับข้อมูล 1 ตัวในอีก entity หนึ่ง (เป็นส่วนกลับของ 1:N ของอีก entity) เช่น ออเดอร์หลายออเดอร์ มีผู้ซื้อได้แค่คนเดียว
   4. Many-to-Many: ความสัมพันธ์แบบ M:N หมายถึง ข้อมูลใน entity หนึ่งสัมพันธ์กับข้อมูลในอีก enity ได้หลายตัว ในทางกลับกัน entity นั้นก็มีความสัมพันธ์กับ entity แรก หลายตัวเช่นกัน เช่น users 1 คนมีได้หลาย role ใน ขณะที่ 1 role ก็มี user หลายคนที่มีสิทธิ์ได้

ตัวอย่าง ER diagram สำหรับระบบสั่งซื้อสินค้าออนไลน์

1. define entities ทั้งหมดของระบบได้ดังนี้
   1. users: ผู้ใช้งานระบบ
   2. roles: สิทธิ์เข้าถึงการใช้งานระบบ
   3. orders: คำสั่งซื้อ
   4. products: สินค้า
   5. category: หมวดหมู่ของสินค้า
2. define attribute ทั้งหมดของแต่ละ entity
   1. users
      1. id: รหัสผู้ใช้งาน
      2. username: user login ระบบ
      3. password: รหัสเข้าใช้งาน
      4. name: ชื่อจริงผู้ใช้งาน
   2. orders
      1. id: รหัสสินค้า
      2. title: ชื่อสินค้า
      3. description: คำอธิบายสินค้า
      4. price: ราคาสินค้า
   3. products
      1. id: รหัสคำสั่งซื้อ
      2. status: สถานะคำสั่งซื้อ
      3. created_at: วันที่สั่งซื้อ
   4. categories
1. id: รหัสหมวดหมู่
2. title: ชื่อหมวดหมู่
3. exclude_vat: ได้รับการยกเว้นภาษีหรือไม่
3. define relationship
   1. users 1 คน สั่งซื้อได้หลาย ออเดอร์, orders 1 ออเดอร์ มีคนสั่งซื้อได้แค่ 1 คน
      1. users <-1-> purchase <-n-> orders
      2. orders <-1-> purchased <-1-> users
   2. orders 1 ออเดอร์ สามารถมีได้หลายสินค้า และ products 1 ชิ้น ถูกสั่งซื้อได้จากหลายออเดอร์
      1. orders <-1-> has <-n-> products
      2. products <-1-> purchased <-n-> orders
   3. products 1 ชิ้น เป็น 1 category, และ 1 category มีสินค้าที่สามารถอยู่ในหมวดหมู่นั้นได้หลายชิ้น
      1. products <-1-> has <-1-> categories
      2. categories <-1-> has <-n-> products
4. วาด diagram เริ่มจาก entity

5. เพิ่ม attribute ที่แต่ละ entity

6. เพิ่ม relationship

7. ลากเส้นเชื่อมโยงรูปแบบความสัมพันธ์

เท่านี้เราก็จะได้ ER diagram ไว้สำหรับ design database กันต่อแล้ว

Database schema diagram

เป็นแผนภาพที่ใช้แสดงตารางในฐานข้อมูลจริงๆ ซึ่งจะง่ายกับการนำไปใช้งานต่อมากกว่า

สิ่งที่เพิ่มเข้ามาตอนทำ schema

1. เราต้องรู Primary Key (PK): ของตาราง จะเป็น unique key ที่ใช้ค้นหาข้อมูล ต้องไม่ซ้ำกัน เช่น user_id, order_id
2. ต้องกำหนด Foreign Key (FK): เพิ่ม Key ที่ใช้เชื่อมความสัมพันธ์ระหว่าง table
3. กำหนดชนิดของข้อมูลให้ชัดเจนในขั้นตอนนี้ เช่น username ใช้ VARCHAR(50)

ซึ่งหากเราออกแบบด้วย ERD มาแล้ว ต้องทำการแปลงเป็น Database schema diagram และคำสั่ง SQL เพื่อนำไปใช้กับฐานข้อมูลของเราอีกทีโดยจะมีขั้นตอนดังนี้

1. แปลง entity ทั้งหมดเป็น table โดยจากตัวอย่างด้านบน จะได้ table ดังนี้
   1. users
   2. products
   3. orders
   4. categories
2. นำ attribute ของแต่ละ entity มาใส่ใน table เช่น
   1. users
      1. id
      2. username
      3. password
      4. name
3. กำหนด Foreign key
   1. ความสัมพันธ์แบบ 1-to-1 (1:1) → กำหนด Foreign Key (FK) ใน Table หนึ่งเพื่อเชื่อมโยงไปยังอีก Table
   2. ความสัมพันธ์แบบ 1-to-Many (1:N) → เพิ่ม Foreign Key (FK) ใน Table ฝั่ง "Many"
4. เพิ่ม Join table สำหรับความสัมพันธ์แบบ Many-to-Many (M:N)
5. เพิ่ม Constraints และ Normalization
   1. ใช้ NOT NULL สำหรับคอลัมน์ที่ต้องมีค่าตลอดเวลา
   2. ใช้ UNIQUE กับคอลัมน์ที่ไม่ควรซ้ำ เช่น Email
   3. ใช้ DEFAULT และ CHECK ถ้าจำเป็น
   4. ตรวจสอบ Normalization (1NF, 2NF, 3NF) เพื่อลดการทำซ้ำของข้อมูล ศึกษาเพิ่มเติม https://miwtoo.medium.com/การทำ-normalization-เพื่อปรับปรุง-schema-d0324b6c9556

จากตัวอย่าง ERD ที่เราสร้างขึ้นจะแปลงเป็น Schema ได้ดังนี้

1. นำ entity และ attribute ทั้งหมดมาวาดเป็นตาราง

2. กำหนด foreign key ของ relation ที่เป็น 1:1 และ 1:n กรณีของ erd ที่เรา design จะมี

1. user -> orders: เป็น 1:1 ต้องเพิ่ม Foreign key ที่ table orders
2. category -> products เป็น 1:n ต้องเพิ่ม foreign key ที่ table products

3. สร้าง Join table สำหรับความสัมพันธ์แบบ M:N กรณีของ erd ที่เรา design จะมี
   1. orders -> products: จะสร้าง join table ที่เก็บความสัมพันธ์ตรงกลาง โดยมี foreign key ของแต่ละ table เก็บใน table นี้คือ
      1. order -> order_id
      2. product -> product_id

4. เพิ่ม constraint, normalization: ยังไม่เพิ่มในกรณีนี้ (เพื่อความเข้าใจง่าย)
5. จากนั้นเขียนคำสั่ง SQL create table จาก diagram ที่เรา design จะได้คำสั่งดังนี้

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DOUBLE NOT NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE order_products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

6. นำคำสั่งไปรันใน SQL database จะได้ตาราง table ตามที่เรา design ไว้

Exercise

ออกแบบตารางสำหรับเก็บข้อมูลระบบลงทะเบียนเรียนคอร์สโดยมี ข้อมูลดังนี้

1. users: ผู้ใช้งานระบบ (ลูกค้า)
2. courses: คอร์สเรียนออนไลน์
3. course_type: ประเภทของคอร์ส เช่น คอร์สออนไลน์, คอร์สตัวต่อตัว
4. enroll: คอร์สที่ลูกค้าลงทะเบียนเรียน

โดยข้อมูลที่ design ให้รองรับการใช้งานดังนี้

1. ลูกค้าลงทะเบียนเข้าใช้งานระบบผ่าน username, password
2. แอดมินสามารถเพิ่มคอร์สเรียนออนไลน์ได้โดยมี field ข้อมูลดังนี้
   1. course_name: ชื่อคอร์ส (ห้ามสร้างซ้ำกัน)
   2. course_type: ประเภทของคอร์ส (1 คอร์สมีได้ 1 ประเภท)
   3. price: ราคาคอร์ส
   4. status: สถานะของคอร์ส (เปิดให้เรียน/ปิดรับลงทะเบียน)
3. ลูกค้าสามารถลงทะเบียนเรียน (enroll) กี่คอร์สก็ได้

จากโจทย์ต้องการผลลัพธ์ดังนี้

1. ER diagram
2. Database schema diagram
3. SQL command สำหรับ create table

Reference

How to Draw Entity Relationship Diagrams (ERDs) - GeeksforGeeks

A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

GeeksforGeeksGeeksforGeeks

Relationships in SQL - One-to-One, One-to-Many, Many-to-Many. - GeeksforGeeks

A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

GeeksforGeeksGeeksforGeeks

Free Entity-Relationship Diagram Template | Gliffy by Perforce

Get your ERD started with a free entity-relationship diagram template. This entity-relationship template will give you a head-start.

Perforce

ER-Diagram คืออะไร? - Clicknext Community

ถ้าพูดถึงการทำ Web Application ในปัจจุบันแล้ว ไม่ว่าจะเป็นระบบใหญ่ ปานกลาง หรือแม้กระทั่งระบบเล็กๆ ทุกระบบล้วนมี Database ที่ไว้เก็บข้อมูลกันทั้งนั้น แต่ไม่ใช่ว่าอยู่ๆจะได้ Database นี้มาง่ายๆนะครับ จะต้องมีการเก็บความต้องการของผู้ใช้งานแล้วนำมาออกมาแบบมันอีกทีนึง ความยากง่ายก็แล้วแต่ระบบเลย ซึ่งสิ่งที่จะมาพูดถึงก็เรื่องการออกแบบ Database นี้ละครับ สำหรับวันนี้จะพามาให้รู้จัก Diagram…

Clicknext CommunityRee