1Ví dụ trong AdventureWorks:
2- Customer (Khách hàng)
3- Product (Sản phẩm)
4- Order (Đơn hàng)
5- Employee (Nhân viên)

1Customer Entity:
2- CustomerID (Primary Key)
3- FirstName
4- LastName
5- Email
6- Phone
7- DateOfBirth

1-- Option 1: FK in either table
2CREATE TABLE Employee (
3    EmployeeID INT PRIMARY KEY,
4    Name NVARCHAR(100),
5    OfficeID INT UNIQUE,  -- UNIQUE ensures 1:1
6    FOREIGN KEY (OfficeID) REFERENCES Office(OfficeID)
7);
8
9-- Option 2: Same PK in both tables
10CREATE TABLE EmployeeDetails (
11    EmployeeID INT PRIMARY KEY,  -- Same as Employee.EmployeeID
12    SSN VARCHAR(20),
13    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
14);

1-- FK ở phía "Many" (Order)
2CREATE TABLE Orders (
3    OrderID INT PRIMARY KEY,
4    CustomerID INT,
5    OrderDate DATE,
6    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
7);

1-- Xem relationship Customer -> SalesOrderHeader
2SELECT 
3    c.CustomerID,
4    COUNT(soh.SalesOrderID) AS OrderCount
5FROM Sales.Customer c
6LEFT JOIN Sales.SalesOrderHeader soh 
7    ON c.CustomerID = soh.CustomerID
8GROUP BY c.CustomerID
9ORDER BY OrderCount DESC;

1-- Cần Junction Table (Bridge Table)
2CREATE TABLE Student (
3    StudentID INT PRIMARY KEY,
4    Name NVARCHAR(100)
5);
6
7CREATE TABLE Course (
8    CourseID INT PRIMARY KEY,
9    CourseName NVARCHAR(100)
10);
11
12-- Junction Table
13CREATE TABLE Enrollment (
14    StudentID INT,
15    CourseID INT,
16    EnrollDate DATE,
17    Grade CHAR(2),
18    PRIMARY KEY (StudentID, CourseID),
19    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
20    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
21);

1-- Product <-> SalesOrder là M:N thông qua SalesOrderDetail
2SELECT 
3    p.ProductID,
4    p.Name AS ProductName,
5    COUNT(DISTINCT sod.SalesOrderID) AS OrderCount
6FROM Production.Product p
7JOIN Sales.SalesOrderDetail sod ON p.ProductID = sod.ProductID
8GROUP BY p.ProductID, p.Name
9ORDER BY OrderCount DESC;

1┌─────────┐                    ┌─────────┐
2    │Customer │───<Places>────────│ Order   │
3    └─────────┘     1        N    └─────────┘
4         │
5    ┌────┴────┐
6    │CustomerID│ (Key attribute - underlined)
7    │Name      │
8    │Email     │
9    └──────────┘

1┌─────────────┐              ┌─────────────┐
2    │  Customer   │              │    Order    │
3    ├─────────────┤              ├─────────────┤
4    │ CustomerID  │──────┤├──────│ OrderID     │
5    │ Name        │      │       │ CustomerID  │
6    │ Email       │      │       │ OrderDate   │
7    └─────────────┘      │       └─────────────┘
8                         │
9    ──┤├──  One and only one
10    ──┤<──  One or many
11    ──○┤──  Zero or one
12    ──○<──  Zero or many

1┌─────────────────┐         ┌─────────────────┐
2    │    Customer     │         │      Order      │
3    ├─────────────────┤         ├─────────────────┤
4    │ +CustomerID: PK │ 1     * │ +OrderID: PK    │
5    │ +Name: string   ├─────────┤ +CustomerID: FK │
6    │ +Email: string  │         │ +OrderDate: date│
7    └─────────────────┘         └─────────────────┘

1-- Xem structure của Sales module
2SELECT 
3    t.name AS TableName,
4    c.name AS ColumnName,
5    ty.name AS DataType,
6    c.is_nullable,
7    CASE WHEN pk.column_id IS NOT NULL THEN 'PK' ELSE '' END AS IsPK,
8    CASE WHEN fk.parent_column_id IS NOT NULL THEN 'FK' ELSE '' END AS IsFK
9FROM sys.tables t
10JOIN sys.columns c ON t.object_id = c.object_id
11JOIN sys.types ty ON c.user_type_id = ty.user_type_id
12LEFT JOIN (
13    SELECT ic.object_id, ic.column_id
14    FROM sys.index_columns ic
15    JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
16    WHERE i.is_primary_key = 1
17) pk ON t.object_id = pk.object_id AND c.column_id = pk.column_id
18LEFT JOIN sys.foreign_key_columns fk ON t.object_id = fk.parent_object_id AND c.column_id = fk.parent_column_id
19WHERE SCHEMA_NAME(t.schema_id) = 'Sales'
20ORDER BY t.name, c.column_id;

11. **Đặt tên rõ ràng**
2   - Entity: Danh từ số ít (Customer, không phải Customers)
3   - Relationship: Động từ (Places, Contains, Manages)
4 
52. **Xác định PK cho mọi entity**
6   - Mỗi entity PHẢI có Primary Key
7   - PK nên là single column khi có thể
8 
93. **Định nghĩa cardinality rõ ràng**
10   - 1:1, 1:N, hay M:N?
11   - Optional hay Mandatory?
12 
134. **Giữ ERD đơn giản**
14   - Chia thành nhiều diagrams nếu cần
15   - Focus vào một module/area
16 
175. **Review với stakeholders**
18   - Verify business rules
19   - Ensure completeness

11. **Đừng vẽ quá chi tiết ban đầu**
2   - Bắt đầu từ high-level
3   - Thêm chi tiết dần dần
4 
52. **Đừng bỏ qua M:N relationships**
6   - Luôn cần junction table
7   - Có thể có attributes riêng
8 
93. **Đừng quên derived attributes**
10   - Age từ DateOfBirth
11   - TotalAmount từ calculations
12 
134. **Đừng design trong isolation**
14   - Involve business users
15   - Validate assumptions

1-- 1. Trong SSMS, right-click database
2-- 2. Chọn "Database Diagrams"
3-- 3. Create New Diagram
4-- 4. Add tables và SSMS tự vẽ relationships

1// Paste vào dbdiagram.io
2Table Customer {
3  CustomerID int [pk]
4  FirstName varchar
5  LastName varchar
6  Email varchar
7}
8 
9Table Order {
10  OrderID int [pk]
11  CustomerID int [ref: > Customer.CustomerID]
12  OrderDate date
13  TotalAmount decimal
14}
15 
16Table OrderDetail {
17  OrderID int [pk, ref: > Order.OrderID]
18  ProductID int [pk, ref: > Product.ProductID]
19  Quantity int
20  UnitPrice decimal
21}
22 
23Table Product {
24  ProductID int [pk]
25  ProductName varchar
26  CategoryID int
27  UnitPrice decimal
28}