Entity-Relationship Diagram (ERD)

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-- 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