พื้นฐานการออกแบบฐานข้อมูล | Database Design 101 😎💯

Main ideas / lessons (Database Design 101)

• Database design starts from system needs
  • Perform requirement analysis: determine what data the system must store.
  • Think in terms of entities (things to store) before tables/columns.

Step-by-step workflow (core methodology)

1. Analyze system requirements
   • Identify what data must be collected.
   • Example used throughout: an online bookstore needs customers, books, orders, etc.
2. Define entities and their attributes
   • Entity → table
   • Attribute → field/column
   • Example attributes:
     • Customer: customer ID, name, email address
     • Book: book ID, title, author, price
     • Order: order ID, order date
   • Define Primary Keys (PKs):
     • customer ID is PK for Customer table
     • book ID is PK for Book table
     • order ID is PK for Orders table
3. Draw an ER diagram
   • Use ER diagrams to visualize the structure and relationships between tables.
   • Tool mentioned: draw.io
4. Define table structures (logical design → relational tables)
   • Convert ER diagram into table definitions.
   • Include primary keys and foreign keys.
5. Normalize to prevent problems (data duplication / redundancy)
   • Normalization targets:
     • Avoid repeated/duplicated data (e.g., don’t store book titles directly inside the order table).
     • Reduce complexity and improve correctness.
     • Improve flexibility for future changes.
6. Implement using SQL
   • Create tables and constraints in SQL so the design can be used in real systems.

---

Table relationships: the 3 main types (with rules)

1) One-to-One (1:1)

• Meaning: one row in Table A relates to only one row in Table B.
• Use case: separating detailed/important info.
  • Example: a customer has one profile entry.

2) One-to-Many (1:N) — most common

• Meaning: one row in Table A can relate to multiple rows in Table B.
• Mechanism: Table B includes a foreign key referencing Table A’s primary key.
  • Example: one customer can place multiple orders.

3) Many-to-Many (N:M)

• Meaning: multiple rows in Table A relate to multiple rows in Table B.
• Key rule: requires a central “junction/bridge” table (also called an associative entity).
  • Example in the bookstore:
    • One order can contain multiple books.
    • One book can appear in multiple orders.
  • Junction table example: order_detail
    • Stores combinations of:
      • order ID (FK to Orders)
      • book ID (FK to Books)
    • Also includes additional attributes like:
      • quantity

---

Why the junction table is necessary (explicit points)

1. Support multiple items per order
   • Without a junction table, each row might only represent a single item; you need multiple rows to represent multiple books in one order.
2. Prevent duplicate data
   • Avoid storing repeated lists like Book1ID, Book2ID, Book3ID... in the order table.
   • This addresses normalization principles.
3. Store extra order-specific details per item
   • Example: quantity per book within a specific order.
   • Future additions (e.g., per-book discounts at purchase time).
4. Enable future expansion without major table restructuring
   • E.g., promotions tied to certain books can be handled by extending data in the design (including the junction table as needed).

---

Keys: Primary Key and Foreign Key

Primary Key (PK)

• A column or set of columns that uniquely identifies each row.
• Must be unique and not empty.
• Example: customer ID uniquely identifies a customer.

Foreign Key (FK)

• A column used to link one table to another.
• References the primary key of the related table.
• Example: orders.customer ID is an FK pointing to customer.customer ID to indicate the order belongs to a customer.

---

Normalization basics (as taught: 1NF, 2NF, 3NF)

• 1NF (First Normal Form)
  • Data must be atomic:
    • Do not store multiple values in a single field.
• 2NF (Second Normal Form)
  • Non-key data must depend entirely on the primary key.
• 3NF (Third Normal Form)
  • Non-key data must not depend on other non-key fields—dependency should be only on the primary key.

Overall goal emphasized:

• Reduce redundancy
• Prevent unnecessary duplication
• Improve maintainability/clarity

---

Final “basic principles” recap (as a checklist)

1. Start with requirements (what the system must store)
2. Define entities/tables for the main objects (e.g., Customer, Book, Order)
3. Add attributes/columns and relationships
4. Draw an ER diagram to visualize structure and relationships
5. Convert to tables, defining PKs and FKs
6. Normalize to avoid duplicate/redundant data
7. Create the real database using SQL

---

Speakers / sources featured

• Speaker: The YouTube presenter (unnamed in the subtitles)
• Tools / sources mentioned:
  • draw.io
  • SQL (Structured Query Language)
• Promotional/source mentions (membership/service):
  • “Jan Ohm” / “Janom” (mentioned as the creator/brand in the closing)