Database Design | History Table | Order Status History

• Bảng “Hóa Đơn” chỉ lưu trạng thái mới nhất (VD “shiped”), không ghi lại lịch sử thay đổi trạng thái (VD: “open” >>> “paid” >>> “shiped”). Nếu bạn muốn phân tích thời gian giữa các giai đoạn (VD: trung bình mất bao lâu để giao hàng sau khi thanh toán) sẽ không được vì không có đủ dữ liệu.
• Bảng “Lịch sử trạng thái đơn hàng” được thêm vào để giải quyết vấn đề này bằng cách lưu mọi thay đổi trạng thái kèm thời gian, giúp bạn hiểu rõ hơn về hiệu suất xử lý đơn hàng.

• hist_id: Khóa chính, tự động tăng (IDENTITY), giúp mỗi bản ghi lịch sử có mã định danh riêng.
• order_id: Khóa ngoại liên kết với bảng “Đơn hàng”, chỉ ra lịch sử thuộc về đơn hàng nào.
• status_id: Xác định các trạng thái cụ thể (như “open”, “paid”, “processing”, “shipped”).
• timestamp: Thời điểm thay đổi trạng thái, là cột quan trọng nhất để tính toán thời gian giữa các giai đoạn.

create table status_type (
status_id int primary key,
name varchar(50)
);

create table orders (
    order_id int primary key,
    cust_id int,
    date datetime,
    status_id int,
    foreign key (status_id) references status_type(status_id)
);

create table status_history (
    hist_id int identity(1,1) primary key,
    order_id int not null,
    status_id int not null,
    timestamp datetime not null,
    foreign key (order_id) references orders(order_id) on delete cascade,
    foreign key (status_id) references status_type(status_id),
    index idx_order (order_id)
);

Một trigger được cài đặt trên bảng “orders”. Mỗi khi trạng thái đơn hàng được thêm mới hoặc cập nhật, trigger sẽ tự động chèn một bản ghi mới vào bảng “status_history”.

create trigger trg_status_history
on orders
after insert, update
as
begin
    insert into status_history (order_id, status_id, timestamp)
    select 
        i.order_id,
        i.status_id,
        getdate()
    from inserted i
    left join deleted d on i.order_id = d.order_id
    where i.status_id is not null
    and (d.order_id is null or i.status_id != d.status_id)
    and not exists (
        select 1 
        from status_history h 
        where h.order_id = i.order_id 
        and h.status_id = i.status_id 
        and h.timestamp = getdate()
    );
end;

insert into status_type (status_id, name) values
(1, 'open'),
(2, 'paid'),
(3, 'processing'),
(4, 'shipped');

insert into orders (order_id, cust_id, date, status_id) values
(1001, 1, getdate(), 1), 
(1002, 2, getdate(), 1); 

update orders set status_id = 2 where order_id = 1001;
update orders set status_id = 3 where order_id = 1001;
update orders set status_id = 4 where order_id = 1001;
update orders set status_id = 2 where order_id = 1002;
update orders set status_id = 3 where order_id = 1002;

• Phân tích thời gian
  • Tính được thời gian trung bình giữa các trạng thái. Dữ liệu thời gian giúp đo lường hiệu quả quy trình.
• Tìm điểm nghẽn và phát hiện bất thường
  • Nếu trạng thái “processing” thường mất 5 giờ trong khi các giai đoạn khác chỉ 1 giờ, doanh nghiệp có thể cải thiện khâu xử lý (ví dụ: tăng nhân lực).
  • Tìm đơn hàng bị kẹt quá lâu.
• Báo cáo lịch sử và xu hướng
  • Hỗ trợ lập kế hoạch dài hạn dựa trên dữ liệu quá khứ.

• Thời gian trung bình giữa tất cả cặp trạng thái

with cte1 as (
    select 
        sh1.order_id,
        st1.name as from_status,
        st2.name as to_status,
        datediff(minute, sh1.timestamp, sh2.timestamp) as duration_minutes
    from status_history sh1
    join status_history sh2 on sh1.order_id = sh2.order_id
    join status_type st1 on sh1.status_id = st1.status_id
    join status_type st2 on sh2.status_id = st2.status_id
    where sh2.timestamp > sh1.timestamp
)
select 
    from_status,
    to_status,
    avg(duration_minutes) as average_minutes
from cte1
group by from_status, to_status
having count(*) > 0
order by from_status, to_status;

• Đếm số lần xuất hiện của mỗi lộ trình trạng thái

with cte1 as (
    select 
        sh.order_id,
        string_agg(st.name, ' -> ') within group (order by sh.timestamp) as status_path
    from status_history sh
    join status_type st on sh.status_id = st.status_id
    group by sh.order_id
)
select 
    status_path,
    count(*) as path_count
from cte1
group by status_path
order by path_count desc;

• Phát hiện đơn hàng bị kẹt quá lâu

with cte1 as (
    select 
        sh.order_id,
        st.name as status_name,
        datediff(minute, 
            sh.timestamp, 
            lead(sh.timestamp, 1, getdate()) over (partition by sh.order_id order by sh.timestamp)) as duration_minutes
    from status_history sh
    join status_type st on sh.status_id = st.status_id
)
select 
    order_id,
    status_name,
    duration_minutes
from cte1
where duration_minutes > 20
order by duration_minutes desc;