Complete Project: E-commerce Database Design

1. Project Overview

Trong bài cuối cùng này, chúng ta sẽ áp dụng tất cả kiến thức đã học để thiết kế và implement một database hoàn chỉnh cho hệ thống E-commerce.

Business Requirements

Text

1📦 ShopOnline - Hệ thống bán hàng trực tuyến
2 
3Yêu cầu:
41. Quản lý sản phẩm theo danh mục
52. Quản lý khách hàng và địa chỉ giao hàng
63. Xử lý đơn hàng và thanh toán
74. Quản lý kho hàng
85. Báo cáo doanh thu
96. Hệ thống khuyến mãi

ShopOnline Database Modules

ShopOnline DB

Product Module

2. Entity-Relationship Diagram

Text

1┌─────────────────┐       ┌─────────────────┐
2│    Category     │       │     Product     │
3├─────────────────┤       ├─────────────────┤
4│ PK CategoryID   │───1:N─│ PK ProductID    │
5│    Name         │       │ FK CategoryID   │
6│    ParentID     │───┐   │    Name, SKU    │
7└─────────────────┘   │   │    Price        │
8         ▲            │   └────────┬────────┘
9         └────────────┘            │
10                                   │
11┌─────────────────┐                │
12│    Inventory    │◄───────────────┘
13├─────────────────┤
14│ PK ProductID    │
15│    StockQty     │
16│    ReorderLevel │
17└─────────────────┘
18 
19┌─────────────────┐       ┌─────────────────┐
20│    Customer     │───1:N─│    Address      │
21├─────────────────┤       ├─────────────────┤
22│ PK CustomerID   │       │ PK AddressID    │
23│    Email        │       │ FK CustomerID   │
24│    FirstName    │       │    AddressLine  │
25│    LastName     │       │    City         │
26└────────┬────────┘       └─────────────────┘
27         │
28         │ 1:N
29         ▼
30┌─────────────────┐       ┌─────────────────┐
31│     Order       │───1:N─│   OrderDetail   │
32├─────────────────┤       ├─────────────────┤
33│ PK OrderID      │       │ PK DetailID     │
34│ FK CustomerID   │       │ FK OrderID      │
35│ FK PromotionID  │       │ FK ProductID    │
36│    OrderDate    │       │    Quantity     │
37│    Status       │       │    UnitPrice    │
38└────────┬────────┘       └─────────────────┘
39         │
40         │ 1:1
41         ▼
42┌─────────────────┐       ┌─────────────────┐
43│    Payment      │       │   Promotion     │
44├─────────────────┤       ├─────────────────┤
45│ PK PaymentID    │       │ PK PromotionID  │
46│ FK OrderID      │       │    Code         │
47│    Amount       │       │    DiscountPct  │
48│    Method       │       │    StartDate    │
49│    Status       │       │    EndDate      │
50└─────────────────┘       └─────────────────┘

3. Database Implementation

3.1 Create Database & Schemas

SQL

1-- Create Database
2CREATE DATABASE ShopOnline;
3GO
4
5USE ShopOnline;
6GO
7
8-- Create Schemas for modularity
9CREATE SCHEMA Product;
10GO
11CREATE SCHEMA Customer;
12GO
13CREATE SCHEMA Sales;
14GO
15CREATE SCHEMA Promotion;
16GO
17CREATE SCHEMA Inventory;
18GO
19CREATE SCHEMA Reporting;
20GO

3.2 Product Module

SQL

1-- Category Table (with self-reference for hierarchy)
2CREATE TABLE Product.Category (
3    CategoryID INT IDENTITY(1,1),
4    CategoryName NVARCHAR(100) NOT NULL,
5    Description NVARCHAR(500),
6    ParentCategoryID INT,
7    ImageURL VARCHAR(500),
8    SortOrder INT DEFAULT 0,
9    IsActive BIT NOT NULL DEFAULT 1,
10    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
11    ModifiedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
12    
13    CONSTRAINT PK_Category PRIMARY KEY (CategoryID),
14    CONSTRAINT UQ_Category_Name UNIQUE (CategoryName),
15    CONSTRAINT FK_Category_Parent FOREIGN KEY (ParentCategoryID) 
16        REFERENCES Product.Category(CategoryID),
17    CONSTRAINT CK_Category_SortOrder CHECK (SortOrder >= 0)
18);
19GO
20
21-- Product Table
22CREATE TABLE Product.Product (
23    ProductID INT IDENTITY(1,1),
24    SKU VARCHAR(30) NOT NULL,
25    ProductName NVARCHAR(200) NOT NULL,
26    Description NVARCHAR(MAX),
27    CategoryID INT NOT NULL,
28    Brand NVARCHAR(100),
29    UnitPrice DECIMAL(19, 4) NOT NULL,
30    CostPrice DECIMAL(19, 4),
31    Weight DECIMAL(10, 3),
32    Dimensions NVARCHAR(50),  -- "LxWxH"
33    ImageURL VARCHAR(500),
34    IsActive BIT NOT NULL DEFAULT 1,
35    IsFeatured BIT NOT NULL DEFAULT 0,
36    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
37    ModifiedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
38    
39    CONSTRAINT PK_Product PRIMARY KEY (ProductID),
40    CONSTRAINT UQ_Product_SKU UNIQUE (SKU),
41    CONSTRAINT FK_Product_Category FOREIGN KEY (CategoryID) 
42        REFERENCES Product.Category(CategoryID),
43    CONSTRAINT CK_Product_UnitPrice CHECK (UnitPrice >= 0),
44    CONSTRAINT CK_Product_CostPrice CHECK (CostPrice IS NULL OR CostPrice >= 0)
45);
46GO
47
48-- Product Images (multiple per product)
49CREATE TABLE Product.ProductImage (
50    ImageID INT IDENTITY(1,1),
51    ProductID INT NOT NULL,
52    ImageURL VARCHAR(500) NOT NULL,
53    AltText NVARCHAR(200),
54    SortOrder INT DEFAULT 0,
55    IsPrimary BIT NOT NULL DEFAULT 0,
56    
57    CONSTRAINT PK_ProductImage PRIMARY KEY (ImageID),
58    CONSTRAINT FK_ProductImage_Product FOREIGN KEY (ProductID) 
59        REFERENCES Product.Product(ProductID) ON DELETE CASCADE
60);
61GO
62
63-- Create indexes
64CREATE INDEX IX_Product_CategoryID ON Product.Product(CategoryID);
65CREATE INDEX IX_Product_SKU ON Product.Product(SKU);
66CREATE INDEX IX_Product_IsActive ON Product.Product(IsActive) WHERE IsActive = 1;
67GO

3.3 Inventory Module

SQL

1-- Inventory Table
2CREATE TABLE Inventory.Stock (
3    ProductID INT NOT NULL,
4    WarehouseID INT NOT NULL DEFAULT 1,
5    QuantityOnHand INT NOT NULL DEFAULT 0,
6    QuantityReserved INT NOT NULL DEFAULT 0,
7    ReorderLevel INT NOT NULL DEFAULT 10,
8    ReorderQuantity INT NOT NULL DEFAULT 50,
9    LastRestockDate DATETIME2,
10    ModifiedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
11    
12    CONSTRAINT PK_Stock PRIMARY KEY (ProductID, WarehouseID),
13    CONSTRAINT FK_Stock_Product FOREIGN KEY (ProductID) 
14        REFERENCES Product.Product(ProductID),
15    CONSTRAINT CK_Stock_QuantityOnHand CHECK (QuantityOnHand >= 0),
16    CONSTRAINT CK_Stock_QuantityReserved CHECK (QuantityReserved >= 0)
17);
18GO
19
20-- Inventory Transactions (audit trail)
21CREATE TABLE Inventory.StockTransaction (
22    TransactionID INT IDENTITY(1,1),
23    ProductID INT NOT NULL,
24    TransactionType VARCHAR(20) NOT NULL,  -- 'IN', 'OUT', 'ADJUST', 'RESERVE', 'RELEASE'
25    Quantity INT NOT NULL,
26    ReferenceID INT,  -- OrderID or PurchaseOrderID
27    ReferenceType VARCHAR(20),  -- 'ORDER', 'PURCHASE', 'MANUAL'
28    Notes NVARCHAR(500),
29    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
30    CreatedBy NVARCHAR(100) DEFAULT SYSTEM_USER,
31    
32    CONSTRAINT PK_StockTransaction PRIMARY KEY (TransactionID),
33    CONSTRAINT FK_StockTrans_Product FOREIGN KEY (ProductID) 
34        REFERENCES Product.Product(ProductID),
35    CONSTRAINT CK_StockTrans_Type CHECK (TransactionType IN ('IN', 'OUT', 'ADJUST', 'RESERVE', 'RELEASE'))
36);
37GO
38
39CREATE INDEX IX_StockTrans_ProductID ON Inventory.StockTransaction(ProductID);
40CREATE INDEX IX_StockTrans_Date ON Inventory.StockTransaction(CreatedDate DESC);
41GO

3.4 Customer Module

SQL

1-- Customer Table
2CREATE TABLE Customer.Customer (
3    CustomerID INT IDENTITY(1,1),
4    Email VARCHAR(100) NOT NULL,
5    PasswordHash VARBINARY(256) NOT NULL,
6    Salt VARBINARY(32) NOT NULL,
7    FirstName NVARCHAR(50) NOT NULL,
8    LastName NVARCHAR(50) NOT NULL,
9    Phone VARCHAR(20),
10    DateOfBirth DATE,
11    Gender CHAR(1),
12    IsEmailVerified BIT NOT NULL DEFAULT 0,
13    IsActive BIT NOT NULL DEFAULT 1,
14    LoyaltyPoints INT NOT NULL DEFAULT 0,
15    LastLoginDate DATETIME2,
16    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
17    ModifiedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
18    
19    CONSTRAINT PK_Customer PRIMARY KEY (CustomerID),
20    CONSTRAINT UQ_Customer_Email UNIQUE (Email),
21    CONSTRAINT CK_Customer_Gender CHECK (Gender IN ('M', 'F', 'O')),
22    CONSTRAINT CK_Customer_LoyaltyPoints CHECK (LoyaltyPoints >= 0)
23);
24GO
25
26-- Customer Address
27CREATE TABLE Customer.Address (
28    AddressID INT IDENTITY(1,1),
29    CustomerID INT NOT NULL,
30    AddressType VARCHAR(20) NOT NULL DEFAULT 'Shipping',
31    RecipientName NVARCHAR(100) NOT NULL,
32    Phone VARCHAR(20) NOT NULL,
33    AddressLine1 NVARCHAR(200) NOT NULL,
34    AddressLine2 NVARCHAR(200),
35    Ward NVARCHAR(100),
36    District NVARCHAR(100) NOT NULL,
37    City NVARCHAR(100) NOT NULL,
38    PostalCode VARCHAR(10),
39    Country NVARCHAR(50) NOT NULL DEFAULT N'Việt Nam',
40    IsDefault BIT NOT NULL DEFAULT 0,
41    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
42    
43    CONSTRAINT PK_Address PRIMARY KEY (AddressID),
44    CONSTRAINT FK_Address_Customer FOREIGN KEY (CustomerID) 
45        REFERENCES Customer.Customer(CustomerID) ON DELETE CASCADE,
46    CONSTRAINT CK_Address_Type CHECK (AddressType IN ('Shipping', 'Billing'))
47);
48GO
49
50-- Wishlist
51CREATE TABLE Customer.Wishlist (
52    CustomerID INT NOT NULL,
53    ProductID INT NOT NULL,
54    AddedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
55    
56    CONSTRAINT PK_Wishlist PRIMARY KEY (CustomerID, ProductID),
57    CONSTRAINT FK_Wishlist_Customer FOREIGN KEY (CustomerID) 
58        REFERENCES Customer.Customer(CustomerID) ON DELETE CASCADE,
59    CONSTRAINT FK_Wishlist_Product FOREIGN KEY (ProductID) 
60        REFERENCES Product.Product(ProductID) ON DELETE CASCADE
61);
62GO
63
64CREATE INDEX IX_Customer_Email ON Customer.Customer(Email);
65CREATE INDEX IX_Address_CustomerID ON Customer.Address(CustomerID);
66GO

3.5 Promotion Module

SQL

1-- Promotion Table
2CREATE TABLE Promotion.Promotion (
3    PromotionID INT IDENTITY(1,1),
4    PromotionName NVARCHAR(100) NOT NULL,
5    Description NVARCHAR(500),
6    DiscountType VARCHAR(20) NOT NULL,  -- 'PERCENT', 'FIXED'
7    DiscountValue DECIMAL(19, 4) NOT NULL,
8    MinOrderValue DECIMAL(19, 4),
9    MaxDiscountAmount DECIMAL(19, 4),
10    StartDate DATETIME2 NOT NULL,
11    EndDate DATETIME2 NOT NULL,
12    UsageLimit INT,
13    UsageCount INT NOT NULL DEFAULT 0,
14    IsActive BIT NOT NULL DEFAULT 1,
15    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
16    
17    CONSTRAINT PK_Promotion PRIMARY KEY (PromotionID),
18    CONSTRAINT CK_Promotion_DiscountType CHECK (DiscountType IN ('PERCENT', 'FIXED')),
19    CONSTRAINT CK_Promotion_DiscountValue CHECK (DiscountValue > 0),
20    CONSTRAINT CK_Promotion_Dates CHECK (EndDate > StartDate)
21);
22GO
23
24-- Coupon Code (unique codes for promotions)
25CREATE TABLE Promotion.Coupon (
26    CouponID INT IDENTITY(1,1),
27    PromotionID INT NOT NULL,
28    CouponCode VARCHAR(20) NOT NULL,
29    IsUsed BIT NOT NULL DEFAULT 0,
30    UsedByCustomerID INT,
31    UsedDate DATETIME2,
32    
33    CONSTRAINT PK_Coupon PRIMARY KEY (CouponID),
34    CONSTRAINT UQ_Coupon_Code UNIQUE (CouponCode),
35    CONSTRAINT FK_Coupon_Promotion FOREIGN KEY (PromotionID) 
36        REFERENCES Promotion.Promotion(PromotionID),
37    CONSTRAINT FK_Coupon_Customer FOREIGN KEY (UsedByCustomerID) 
38        REFERENCES Customer.Customer(CustomerID)
39);
40GO
41
42CREATE INDEX IX_Coupon_Code ON Promotion.Coupon(CouponCode);
43GO

3.6 Sales Module

SQL

1-- Order Table
2CREATE TABLE Sales.[Order] (
3    OrderID INT IDENTITY(1,1),
4    OrderNumber VARCHAR(20) NOT NULL,
5    CustomerID INT NOT NULL,
6    OrderDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
7    Status VARCHAR(20) NOT NULL DEFAULT 'Pending',
8    
9    -- Shipping Info
10    ShippingAddressID INT,
11    ShippingMethod VARCHAR(50),
12    ShippingFee DECIMAL(19, 4) NOT NULL DEFAULT 0,
13    EstimatedDeliveryDate DATE,
14    ActualDeliveryDate DATE,
15    
16    -- Billing Info
17    BillingAddressID INT,
18    
19    -- Amounts
20    SubTotal DECIMAL(19, 4) NOT NULL DEFAULT 0,
21    DiscountAmount DECIMAL(19, 4) NOT NULL DEFAULT 0,
22    TaxAmount DECIMAL(19, 4) NOT NULL DEFAULT 0,
23    TotalAmount AS (SubTotal - DiscountAmount + TaxAmount + ShippingFee) PERSISTED,
24    
25    -- Promotion
26    PromotionID INT,
27    CouponCode VARCHAR(20),
28    
29    -- Notes
30    CustomerNotes NVARCHAR(500),
31    InternalNotes NVARCHAR(500),
32    
33    -- Audit
34    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
35    ModifiedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
36    
37    CONSTRAINT PK_Order PRIMARY KEY (OrderID),
38    CONSTRAINT UQ_Order_Number UNIQUE (OrderNumber),
39    CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerID) 
40        REFERENCES Customer.Customer(CustomerID),
41    CONSTRAINT FK_Order_ShippingAddress FOREIGN KEY (ShippingAddressID) 
42        REFERENCES Customer.Address(AddressID),
43    CONSTRAINT FK_Order_BillingAddress FOREIGN KEY (BillingAddressID) 
44        REFERENCES Customer.Address(AddressID),
45    CONSTRAINT FK_Order_Promotion FOREIGN KEY (PromotionID) 
46        REFERENCES Promotion.Promotion(PromotionID),
47    CONSTRAINT CK_Order_Status CHECK (Status IN (
48        'Pending', 'Confirmed', 'Processing', 'Shipped', 
49        'Delivered', 'Cancelled', 'Refunded', 'OnHold'
50    ))
51);
52GO
53
54-- Order Detail Table
55CREATE TABLE Sales.OrderDetail (
56    OrderDetailID INT IDENTITY(1,1),
57    OrderID INT NOT NULL,
58    ProductID INT NOT NULL,
59    ProductName NVARCHAR(200) NOT NULL,  -- Snapshot at order time
60    SKU VARCHAR(30) NOT NULL,             -- Snapshot
61    Quantity INT NOT NULL,
62    UnitPrice DECIMAL(19, 4) NOT NULL,
63    Discount DECIMAL(5, 2) NOT NULL DEFAULT 0,
64    LineTotal AS (Quantity * UnitPrice * (1 - Discount)) PERSISTED,
65    
66    CONSTRAINT PK_OrderDetail PRIMARY KEY (OrderDetailID),
67    CONSTRAINT FK_OrderDetail_Order FOREIGN KEY (OrderID) 
68        REFERENCES Sales.[Order](OrderID) ON DELETE CASCADE,
69    CONSTRAINT FK_OrderDetail_Product FOREIGN KEY (ProductID) 
70        REFERENCES Product.Product(ProductID),
71    CONSTRAINT CK_OrderDetail_Quantity CHECK (Quantity > 0),
72    CONSTRAINT CK_OrderDetail_Discount CHECK (Discount >= 0 AND Discount <= 1)
73);
74GO
75
76-- Payment Table
77CREATE TABLE Sales.Payment (
78    PaymentID INT IDENTITY(1,1),
79    OrderID INT NOT NULL,
80    PaymentMethod VARCHAR(30) NOT NULL,  -- 'COD', 'CREDIT_CARD', 'BANK_TRANSFER', 'EWALLET'
81    Amount DECIMAL(19, 4) NOT NULL,
82    Currency VARCHAR(3) NOT NULL DEFAULT 'VND',
83    Status VARCHAR(20) NOT NULL DEFAULT 'Pending',
84    TransactionID VARCHAR(100),  -- From payment gateway
85    PaymentDate DATETIME2,
86    Notes NVARCHAR(500),
87    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
88    
89    CONSTRAINT PK_Payment PRIMARY KEY (PaymentID),
90    CONSTRAINT FK_Payment_Order FOREIGN KEY (OrderID) 
91        REFERENCES Sales.[Order](OrderID),
92    CONSTRAINT CK_Payment_Method CHECK (PaymentMethod IN ('COD', 'CREDIT_CARD', 'BANK_TRANSFER', 'EWALLET', 'MOMO', 'VNPAY', 'ZALOPAY')),
93    CONSTRAINT CK_Payment_Status CHECK (Status IN ('Pending', 'Completed', 'Failed', 'Refunded'))
94);
95GO
96
97-- Order Status History
98CREATE TABLE Sales.OrderStatusHistory (
99    HistoryID INT IDENTITY(1,1),
100    OrderID INT NOT NULL,
101    Status VARCHAR(20) NOT NULL,
102    Notes NVARCHAR(500),
103    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
104    CreatedBy NVARCHAR(100) DEFAULT SYSTEM_USER,
105    
106    CONSTRAINT PK_OrderStatusHistory PRIMARY KEY (HistoryID),
107    CONSTRAINT FK_OrderStatusHistory_Order FOREIGN KEY (OrderID) 
108        REFERENCES Sales.[Order](OrderID) ON DELETE CASCADE
109);
110GO
111
112-- Indexes
113CREATE INDEX IX_Order_CustomerID ON Sales.[Order](CustomerID);
114CREATE INDEX IX_Order_Status ON Sales.[Order](Status);
115CREATE INDEX IX_Order_OrderDate ON Sales.[Order](OrderDate DESC);
116CREATE INDEX IX_OrderDetail_OrderID ON Sales.OrderDetail(OrderID);
117CREATE INDEX IX_OrderDetail_ProductID ON Sales.OrderDetail(ProductID);
118GO

4. Stored Procedures

4.1 Create Order Procedure

SQL

1CREATE OR ALTER PROCEDURE Sales.usp_CreateOrder
2    @CustomerID INT,
3    @ShippingAddressID INT,
4    @ShippingMethod VARCHAR(50),
5    @ShippingFee DECIMAL(19,4),
6    @CouponCode VARCHAR(20) = NULL,
7    @CustomerNotes NVARCHAR(500) = NULL,
8    @OrderItems Sales.OrderItemType READONLY,  -- Table-valued parameter
9    @NewOrderID INT OUTPUT
10AS
11BEGIN
12    SET NOCOUNT ON;
13    SET XACT_ABORT ON;
14    
15    DECLARE @OrderNumber VARCHAR(20);
16    DECLARE @SubTotal DECIMAL(19,4) = 0;
17    DECLARE @DiscountAmount DECIMAL(19,4) = 0;
18    DECLARE @PromotionID INT = NULL;
19    
20    BEGIN TRY
21        BEGIN TRANSACTION;
22        
23        -- 1. Validate customer
24        IF NOT EXISTS (SELECT 1 FROM Customer.Customer WHERE CustomerID = @CustomerID AND IsActive = 1)
25        BEGIN
26            RAISERROR(N'Customer not found or inactive', 16, 1);
27        END
28        
29        -- 2. Validate address
30        IF NOT EXISTS (SELECT 1 FROM Customer.Address WHERE AddressID = @ShippingAddressID AND CustomerID = @CustomerID)
31        BEGIN
32            RAISERROR(N'Invalid shipping address', 16, 1);
33        END
34        
35        -- 3. Validate products and stock
36        IF EXISTS (
37            SELECT 1 FROM @OrderItems oi
38            LEFT JOIN Product.Product p ON oi.ProductID = p.ProductID
39            LEFT JOIN Inventory.Stock s ON p.ProductID = s.ProductID
40            WHERE p.ProductID IS NULL 
41               OR p.IsActive = 0
42               OR s.QuantityOnHand - s.QuantityReserved < oi.Quantity
43        )
44        BEGIN
45            RAISERROR(N'Some products are unavailable or out of stock', 16, 1);
46        END
47        
48        -- 4. Calculate subtotal
49        SELECT @SubTotal = SUM(oi.Quantity * p.UnitPrice)
50        FROM @OrderItems oi
51        JOIN Product.Product p ON oi.ProductID = p.ProductID;
52        
53        -- 5. Validate and apply coupon
54        IF @CouponCode IS NOT NULL
55        BEGIN
56            SELECT @PromotionID = p.PromotionID,
57                   @DiscountAmount = CASE 
58                       WHEN p.DiscountType = 'PERCENT' 
59                       THEN LEAST(@SubTotal * p.DiscountValue / 100, ISNULL(p.MaxDiscountAmount, @SubTotal))
60                       ELSE LEAST(p.DiscountValue, @SubTotal)
61                   END
62            FROM Promotion.Coupon c
63            JOIN Promotion.Promotion p ON c.PromotionID = p.PromotionID
64            WHERE c.CouponCode = @CouponCode
65              AND c.IsUsed = 0
66              AND p.IsActive = 1
67              AND SYSDATETIME() BETWEEN p.StartDate AND p.EndDate
68              AND (p.UsageLimit IS NULL OR p.UsageCount < p.UsageLimit)
69              AND (p.MinOrderValue IS NULL OR @SubTotal >= p.MinOrderValue);
70            
71            IF @PromotionID IS NULL AND @CouponCode IS NOT NULL
72            BEGIN
73                RAISERROR(N'Invalid or expired coupon code', 16, 1);
74            END
75        END
76        
77        -- 6. Generate order number
78        SET @OrderNumber = 'ORD-' + FORMAT(SYSDATETIME(), 'yyyyMMddHHmmss') + '-' + RIGHT('000' + CAST(@CustomerID AS VARCHAR), 4);
79        
80        -- 7. Create order
81        INSERT INTO Sales.[Order] (
82            OrderNumber, CustomerID, ShippingAddressID, ShippingMethod, 
83            ShippingFee, SubTotal, DiscountAmount, PromotionID, CouponCode, CustomerNotes
84        )
85        VALUES (
86            @OrderNumber, @CustomerID, @ShippingAddressID, @ShippingMethod,
87            @ShippingFee, @SubTotal, @DiscountAmount, @PromotionID, @CouponCode, @CustomerNotes
88        );
89        
90        SET @NewOrderID = SCOPE_IDENTITY();
91        
92        -- 8. Create order details
93        INSERT INTO Sales.OrderDetail (OrderID, ProductID, ProductName, SKU, Quantity, UnitPrice)
94        SELECT 
95            @NewOrderID,
96            p.ProductID,
97            p.ProductName,
98            p.SKU,
99            oi.Quantity,
100            p.UnitPrice
101        FROM @OrderItems oi
102        JOIN Product.Product p ON oi.ProductID = p.ProductID;
103        
104        -- 9. Reserve inventory
105        UPDATE s
106        SET s.QuantityReserved = s.QuantityReserved + oi.Quantity,
107            s.ModifiedDate = SYSDATETIME()
108        FROM Inventory.Stock s
109        JOIN @OrderItems oi ON s.ProductID = oi.ProductID;
110        
111        -- 10. Log inventory transactions
112        INSERT INTO Inventory.StockTransaction (ProductID, TransactionType, Quantity, ReferenceID, ReferenceType)
113        SELECT ProductID, 'RESERVE', Quantity, @NewOrderID, 'ORDER'
114        FROM @OrderItems;
115        
116        -- 11. Mark coupon as used
117        IF @CouponCode IS NOT NULL
118        BEGIN
119            UPDATE Promotion.Coupon
120            SET IsUsed = 1, UsedByCustomerID = @CustomerID, UsedDate = SYSDATETIME()
121            WHERE CouponCode = @CouponCode;
122            
123            UPDATE Promotion.Promotion
124            SET UsageCount = UsageCount + 1
125            WHERE PromotionID = @PromotionID;
126        END
127        
128        -- 12. Log status history
129        INSERT INTO Sales.OrderStatusHistory (OrderID, Status, Notes)
130        VALUES (@NewOrderID, 'Pending', N'Order created');
131        
132        COMMIT TRANSACTION;
133        
134    END TRY
135    BEGIN CATCH
136        IF @@TRANCOUNT > 0
137            ROLLBACK TRANSACTION;
138        
139        SET @NewOrderID = 0;
140        THROW;
141    END CATCH
142END;
143GO
144
145-- Create table type for order items
146CREATE TYPE Sales.OrderItemType AS TABLE (
147    ProductID INT NOT NULL,
148    Quantity INT NOT NULL
149);
150GO

4.2 Update Order Status Procedure

SQL

1CREATE OR ALTER PROCEDURE Sales.usp_UpdateOrderStatus
2    @OrderID INT,
3    @NewStatus VARCHAR(20),
4    @Notes NVARCHAR(500) = NULL
5AS
6BEGIN
7    SET NOCOUNT ON;
8    SET XACT_ABORT ON;
9    
10    DECLARE @CurrentStatus VARCHAR(20);
11    
12    BEGIN TRY
13        BEGIN TRANSACTION;
14        
15        -- Get current status
16        SELECT @CurrentStatus = Status
17        FROM Sales.[Order]
18        WHERE OrderID = @OrderID;
19        
20        IF @CurrentStatus IS NULL
21        BEGIN
22            RAISERROR(N'Order not found', 16, 1);
23        END
24        
25        -- Validate status transition
26        IF @CurrentStatus = 'Delivered' AND @NewStatus NOT IN ('Refunded')
27        BEGIN
28            RAISERROR(N'Cannot change status of delivered order', 16, 1);
29        END
30        
31        IF @CurrentStatus = 'Cancelled'
32        BEGIN
33            RAISERROR(N'Cannot change status of cancelled order', 16, 1);
34        END
35        
36        -- Update order status
37        UPDATE Sales.[Order]
38        SET Status = @NewStatus,
39            ModifiedDate = SYSDATETIME(),
40            ActualDeliveryDate = CASE WHEN @NewStatus = 'Delivered' THEN CAST(SYSDATETIME() AS DATE) ELSE ActualDeliveryDate END
41        WHERE OrderID = @OrderID;
42        
43        -- Handle inventory based on status
44        IF @NewStatus = 'Shipped'
45        BEGIN
46            -- Deduct from inventory (reserved -> out)
47            UPDATE s
48            SET s.QuantityOnHand = s.QuantityOnHand - od.Quantity,
49                s.QuantityReserved = s.QuantityReserved - od.Quantity,
50                s.ModifiedDate = SYSDATETIME()
51            FROM Inventory.Stock s
52            JOIN Sales.OrderDetail od ON s.ProductID = od.ProductID
53            WHERE od.OrderID = @OrderID;
54            
55            -- Log transaction
56            INSERT INTO Inventory.StockTransaction (ProductID, TransactionType, Quantity, ReferenceID, ReferenceType, Notes)
57            SELECT ProductID, 'OUT', Quantity, @OrderID, 'ORDER', 'Order shipped'
58            FROM Sales.OrderDetail WHERE OrderID = @OrderID;
59        END
60        
61        IF @NewStatus = 'Cancelled'
62        BEGIN
63            -- Release reserved inventory
64            UPDATE s
65            SET s.QuantityReserved = s.QuantityReserved - od.Quantity,
66                s.ModifiedDate = SYSDATETIME()
67            FROM Inventory.Stock s
68            JOIN Sales.OrderDetail od ON s.ProductID = od.ProductID
69            WHERE od.OrderID = @OrderID;
70            
71            -- Log transaction
72            INSERT INTO Inventory.StockTransaction (ProductID, TransactionType, Quantity, ReferenceID, ReferenceType, Notes)
73            SELECT ProductID, 'RELEASE', Quantity, @OrderID, 'ORDER', 'Order cancelled'
74            FROM Sales.OrderDetail WHERE OrderID = @OrderID;
75        END
76        
77        -- Log status change
78        INSERT INTO Sales.OrderStatusHistory (OrderID, Status, Notes)
79        VALUES (@OrderID, @NewStatus, @Notes);
80        
81        COMMIT TRANSACTION;
82        
83    END TRY
84    BEGIN CATCH
85        IF @@TRANCOUNT > 0
86            ROLLBACK TRANSACTION;
87        THROW;
88    END CATCH
89END;
90GO

5. Views for Reporting

SQL

1-- Customer Order Summary
2CREATE OR ALTER VIEW Reporting.vw_CustomerOrderSummary
3AS
4SELECT 
5    c.CustomerID,
6    c.FirstName + ' ' + c.LastName AS CustomerName,
7    c.Email,
8    COUNT(DISTINCT o.OrderID) AS TotalOrders,
9    SUM(o.TotalAmount) AS LifetimeValue,
10    AVG(o.TotalAmount) AS AvgOrderValue,
11    MIN(o.OrderDate) AS FirstOrderDate,
12    MAX(o.OrderDate) AS LastOrderDate,
13    DATEDIFF(DAY, MAX(o.OrderDate), GETDATE()) AS DaysSinceLastOrder
14FROM Customer.Customer c
15LEFT JOIN Sales.[Order] o ON c.CustomerID = o.CustomerID AND o.Status NOT IN ('Cancelled', 'Refunded')
16GROUP BY c.CustomerID, c.FirstName, c.LastName, c.Email;
17GO
18
19-- Product Sales Summary
20CREATE OR ALTER VIEW Reporting.vw_ProductSalesSummary
21AS
22SELECT 
23    p.ProductID,
24    p.SKU,
25    p.ProductName,
26    cat.CategoryName,
27    p.UnitPrice AS CurrentPrice,
28    s.QuantityOnHand AS Stock,
29    ISNULL(SUM(od.Quantity), 0) AS TotalUnitsSold,
30    ISNULL(SUM(od.LineTotal), 0) AS TotalRevenue,
31    ISNULL(COUNT(DISTINCT o.OrderID), 0) AS OrderCount
32FROM Product.Product p
33JOIN Product.Category cat ON p.CategoryID = cat.CategoryID
34LEFT JOIN Inventory.Stock s ON p.ProductID = s.ProductID
35LEFT JOIN Sales.OrderDetail od ON p.ProductID = od.ProductID
36LEFT JOIN Sales.[Order] o ON od.OrderID = o.OrderID AND o.Status NOT IN ('Cancelled', 'Refunded')
37GROUP BY p.ProductID, p.SKU, p.ProductName, cat.CategoryName, p.UnitPrice, s.QuantityOnHand;
38GO
39
40-- Daily Sales Summary
41CREATE OR ALTER VIEW Reporting.vw_DailySalesSummary
42AS
43SELECT 
44    CAST(o.OrderDate AS DATE) AS SalesDate,
45    COUNT(DISTINCT o.OrderID) AS OrderCount,
46    COUNT(DISTINCT o.CustomerID) AS UniqueCustomers,
47    SUM(od.Quantity) AS TotalUnits,
48    SUM(o.SubTotal) AS GrossSales,
49    SUM(o.DiscountAmount) AS TotalDiscounts,
50    SUM(o.TotalAmount) AS NetSales
51FROM Sales.[Order] o
52JOIN Sales.OrderDetail od ON o.OrderID = od.OrderID
53WHERE o.Status NOT IN ('Cancelled', 'Refunded')
54GROUP BY CAST(o.OrderDate AS DATE);
55GO
56
57-- Low Stock Alert
58CREATE OR ALTER VIEW Inventory.vw_LowStockAlert
59AS
60SELECT 
61    p.ProductID,
62    p.SKU,
63    p.ProductName,
64    s.QuantityOnHand,
65    s.QuantityReserved,
66    s.QuantityOnHand - s.QuantityReserved AS AvailableStock,
67    s.ReorderLevel,
68    s.ReorderQuantity,
69    CASE 
70        WHEN s.QuantityOnHand - s.QuantityReserved <= 0 THEN 'Out of Stock'
71        WHEN s.QuantityOnHand - s.QuantityReserved <= s.ReorderLevel THEN 'Low Stock'
72        ELSE 'In Stock'
73    END AS StockStatus
74FROM Product.Product p
75JOIN Inventory.Stock s ON p.ProductID = s.ProductID
76WHERE p.IsActive = 1
77  AND s.QuantityOnHand - s.QuantityReserved <= s.ReorderLevel;
78GO

6. Security Setup

SQL

1-- Create roles
2CREATE ROLE CustomerService;
3CREATE ROLE SalesManager;
4CREATE ROLE InventoryManager;
5CREATE ROLE ReportViewer;
6
7-- CustomerService permissions
8GRANT SELECT ON SCHEMA::Customer TO CustomerService;
9GRANT SELECT ON SCHEMA::Sales TO CustomerService;
10GRANT UPDATE ON Customer.Customer TO CustomerService;
11GRANT EXECUTE ON Sales.usp_UpdateOrderStatus TO CustomerService;
12
13-- SalesManager permissions
14GRANT SELECT, INSERT, UPDATE ON SCHEMA::Sales TO SalesManager;
15GRANT SELECT ON SCHEMA::Customer TO SalesManager;
16GRANT SELECT ON SCHEMA::Product TO SalesManager;
17GRANT SELECT ON SCHEMA::Promotion TO SalesManager;
18GRANT EXECUTE ON SCHEMA::Sales TO SalesManager;
19
20-- InventoryManager permissions
21GRANT SELECT, INSERT, UPDATE ON SCHEMA::Inventory TO InventoryManager;
22GRANT SELECT, UPDATE ON Product.Product TO InventoryManager;
23
24-- ReportViewer permissions
25GRANT SELECT ON SCHEMA::Reporting TO ReportViewer;
26GO

7. Summary & Next Steps

What We Built

markdown

1✅ 4 Schemas: Product, Customer, Sales, Inventory, Promotion, Reporting
2✅ 15+ Tables with proper relationships
3✅ Indexes for performance
4✅ Stored procedures for business logic
5✅ Views for reporting
6✅ Security roles and permissions

What You Learned

Database Design Journey Complete!

ERD

Normalization

DDL & Tables

Stored Proc

Indexing

Performance

Security

Project

Recommended Next Steps

Practice với AdventureWorks - Explore thêm các tables và relationships
Build your own project - Apply kiến thức vào project cá nhân
Learn T-SQL Advanced - CTEs, Window Functions, Pivoting
Explore SQL Server Features - Partitioning, Replication, Always On
Cloud Databases - Azure SQL, AWS RDS

🎉 Chúc mừng!

Bạn đã hoàn thành khóa học Thiết kế Cơ sở dữ liệu với SQL Server!

Với kiến thức này, bạn có thể:

Thiết kế database cho các ứng dụng thực tế
Viết efficient queries và stored procedures
Optimize performance với indexes
Implement security best practices

Happy Coding! 🚀