Tôi sẽ tạo cho anh bộ script mẫu tương thích PostgreSQL 12 cho đúng 6 database đang có:
-
billing -
crm -
erp -
marketing -
postgres -
sale
Tôi làm theo hướng BSS telco thực tế, có:
-
bảng mẫu,
-
khóa chính/khóa ngoại,
-
index cơ bản,
-
dữ liệu mẫu để insert ngay.
Lưu ý rất quan trọng: script bên dưới có DROP TABLE IF EXISTS để dễ chạy lại nhiều lần, nên chỉ chạy ở môi trường lab/dev/UAT, không chạy thẳng production.
1) Cách chạy
Cách 1: dùng psql chạy 1 file tổng
Lưu toàn bộ nội dung dưới đây thành file:
bss_sample_loader.sql
Rồi chạy:
psql -U postgres -f bss_sample_loader.sql
Cách 2: dùng pgAdmin
-
Mỗi lần connect vào từng database
-
Copy đúng phần script của database đó
-
Chạy riêng
2) Script tổng mẫu cho PostgreSQL 12
\set ON_ERROR_STOP on
/******************************************************************
* BSS SAMPLE DATASET FOR POSTGRESQL 12
* Databases:
* - crm
* - sale
* - billing
* - marketing
* - erp
* - postgres
******************************************************************/
/******************************************************************
* 1. CRM DATABASE
******************************************************************/
\connect crm
BEGIN;
DROP TABLE IF EXISTS interaction_log CASCADE;
DROP TABLE IF EXISTS subscriber CASCADE;
DROP TABLE IF EXISTS customer_address CASCADE;
DROP TABLE IF EXISTS customer CASCADE;
CREATE TABLE customer (
customer_id SERIAL PRIMARY KEY,
customer_code VARCHAR(20) UNIQUE NOT NULL,
full_name VARCHAR(200) NOT NULL,
customer_type VARCHAR(20) NOT NULL CHECK (customer_type IN ('INDIVIDUAL','BUSINESS')),
segment VARCHAR(50),
national_id VARCHAR(30),
date_of_birth DATE,
gender VARCHAR(10),
phone VARCHAR(20) NOT NULL,
email VARCHAR(200),
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
created_at TIMESTAMP NOT NULL DEFAULT now()
);
CREATE TABLE customer_address (
address_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customer(customer_id) ON DELETE CASCADE,
address_type VARCHAR(20) NOT NULL,
province VARCHAR(100) NOT NULL,
district VARCHAR(100) NOT NULL,
ward VARCHAR(100),
detail_address VARCHAR(255),
is_default BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE TABLE subscriber (
subscriber_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customer(customer_id) ON DELETE CASCADE,
msisdn VARCHAR(20) UNIQUE NOT NULL,
account_no VARCHAR(30) UNIQUE NOT NULL,
service_type VARCHAR(30) NOT NULL,
activation_date DATE NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE interaction_log (
interaction_id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customer(customer_id) ON DELETE CASCADE,
channel VARCHAR(30) NOT NULL,
subject VARCHAR(200) NOT NULL,
interaction_time TIMESTAMP NOT NULL,
result VARCHAR(50) NOT NULL,
note TEXT
);
CREATE INDEX idx_customer_phone ON customer(phone);
CREATE INDEX idx_subscriber_msisdn ON subscriber(msisdn);
CREATE INDEX idx_interaction_time ON interaction_log(interaction_time);
INSERT INTO customer (customer_code, full_name, customer_type, segment, national_id, date_of_birth, gender, phone, email, status)
VALUES
('CUST0001', 'Nguyen Van An', 'INDIVIDUAL', 'GOLD', '001090123456', '1990-05-10', 'MALE', '0901000001', 'an.nguyen@example.com', 'ACTIVE'),
('CUST0002', 'Tran Thi Bich', 'INDIVIDUAL', 'SILVER', '001090223456', '1993-08-15', 'FEMALE', '0901000002', 'bich.tran@example.com', 'ACTIVE'),
('CUST0003', 'Le Hoang Minh', 'INDIVIDUAL', 'PREMIUM', '001090323456', '1988-11-22', 'MALE', '0901000003', 'minh.le@example.com', 'ACTIVE'),
('CUST0004', 'ABC Trading Co.', 'BUSINESS', 'ENTERPRISE','0101234567', NULL, NULL, '0901000004', 'contact@abctrading.vn', 'ACTIVE'),
('CUST0005', 'XYZ Logistics JSC', 'BUSINESS', 'VIP', '0107654321', NULL, NULL, '0901000005', 'ops@xyzlogistics.vn', 'ACTIVE');
INSERT INTO customer_address (customer_id, address_type, province, district, ward, detail_address, is_default)
SELECT customer_id, 'HOME', 'Ha Noi', 'Cau Giay', 'Dich Vong', '12 Nguyen Phong Sac', TRUE
FROM customer WHERE customer_code = 'CUST0001';
INSERT INTO customer_address (customer_id, address_type, province, district, ward, detail_address, is_default)
SELECT customer_id, 'HOME', 'Ha Noi', 'Thanh Xuan', 'Nhan Chinh', '88 Le Van Luong', TRUE
FROM customer WHERE customer_code = 'CUST0002';
INSERT INTO customer_address (customer_id, address_type, province, district, ward, detail_address, is_default)
SELECT customer_id, 'HOME', 'Da Nang', 'Hai Chau', 'Thach Thang', '20 Bach Dang', TRUE
FROM customer WHERE customer_code = 'CUST0003';
INSERT INTO customer_address (customer_id, address_type, province, district, ward, detail_address, is_default)
SELECT customer_id, 'OFFICE', 'Ho Chi Minh', 'District 1', 'Ben Nghe', '150 Le Loi', TRUE
FROM customer WHERE customer_code = 'CUST0004';
INSERT INTO customer_address (customer_id, address_type, province, district, ward, detail_address, is_default)
SELECT customer_id, 'OFFICE', 'Hai Phong', 'Ngo Quyen', 'May To', '45 Lach Tray', TRUE
FROM customer WHERE customer_code = 'CUST0005';
INSERT INTO subscriber (customer_id, msisdn, account_no, service_type, activation_date, status)
SELECT customer_id, '0901000001', 'ACC0001', 'POSTPAID_MOBILE', '2025-01-15', 'ACTIVE'
FROM customer WHERE customer_code = 'CUST0001';
INSERT INTO subscriber (customer_id, msisdn, account_no, service_type, activation_date, status)
SELECT customer_id, '0901000002', 'ACC0002', 'POSTPAID_MOBILE', '2025-02-20', 'ACTIVE'
FROM customer WHERE customer_code = 'CUST0002';
INSERT INTO subscriber (customer_id, msisdn, account_no, service_type, activation_date, status)
SELECT customer_id, '0901000003', 'ACC0003', 'PREPAID_MOBILE', '2025-03-05', 'ACTIVE'
FROM customer WHERE customer_code = 'CUST0003';
INSERT INTO subscriber (customer_id, msisdn, account_no, service_type, activation_date, status)
SELECT customer_id, '0901000004', 'ACC0004', 'ENTERPRISE_MOBILE', '2024-12-01', 'ACTIVE'
FROM customer WHERE customer_code = 'CUST0004';
INSERT INTO subscriber (customer_id, msisdn, account_no, service_type, activation_date, status)
SELECT customer_id, '0901000005', 'ACC0005', 'ENTERPRISE_DATA', '2024-11-10', 'ACTIVE'
FROM customer WHERE customer_code = 'CUST0005';
INSERT INTO interaction_log (customer_id, channel, subject, interaction_time, result, note)
SELECT customer_id, 'CALL_CENTER', 'Change tariff plan', '2026-03-10 09:15:00', 'COMPLETED', 'Customer requested upgrade to larger data package'
FROM customer WHERE customer_code = 'CUST0001';
INSERT INTO interaction_log (customer_id, channel, subject, interaction_time, result, note)
SELECT customer_id, 'SHOP', 'SIM replacement', '2026-03-11 14:30:00', 'COMPLETED', 'Lost SIM and completed SIM swap at branch'
FROM customer WHERE customer_code = 'CUST0002';
INSERT INTO interaction_log (customer_id, channel, subject, interaction_time, result, note)
SELECT customer_id, 'APP', 'Top-up failed', '2026-03-12 20:05:00', 'PENDING', 'Investigating payment gateway timeout'
FROM customer WHERE customer_code = 'CUST0003';
INSERT INTO interaction_log (customer_id, channel, subject, interaction_time, result, note)
SELECT customer_id, 'ACCOUNT_MANAGER', 'Enterprise bundle renewal', '2026-03-13 10:00:00', 'COMPLETED', 'Annual contract renewed'
FROM customer WHERE customer_code = 'CUST0004';
INSERT INTO interaction_log (customer_id, channel, subject, interaction_time, result, note)
SELECT customer_id, 'EMAIL', 'Billing dispute', '2026-03-13 16:40:00', 'IN_PROGRESS', 'Customer requested invoice clarification'
FROM customer WHERE customer_code = 'CUST0005';
COMMIT;
/******************************************************************
* 2. SALE DATABASE
******************************************************************/
\connect sale
BEGIN;
DROP TABLE IF EXISTS sales_order_item CASCADE;
DROP TABLE IF EXISTS sales_order CASCADE;
DROP TABLE IF EXISTS product CASCADE;
DROP TABLE IF EXISTS shop CASCADE;
CREATE TABLE shop (
shop_id SERIAL PRIMARY KEY,
shop_code VARCHAR(20) UNIQUE NOT NULL,
shop_name VARCHAR(200) NOT NULL,
province VARCHAR(100) NOT NULL,
channel_type VARCHAR(30) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE product (
product_id SERIAL PRIMARY KEY,
product_code VARCHAR(30) UNIQUE NOT NULL,
product_name VARCHAR(200) NOT NULL,
category VARCHAR(50) NOT NULL,
price NUMERIC(18,2) NOT NULL CHECK (price >= 0),
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE sales_order (
order_id SERIAL PRIMARY KEY,
order_no VARCHAR(30) UNIQUE NOT NULL,
customer_code VARCHAR(20) NOT NULL,
shop_id INT NOT NULL REFERENCES shop(shop_id),
order_date TIMESTAMP NOT NULL,
order_status VARCHAR(20) NOT NULL,
total_amount NUMERIC(18,2) NOT NULL CHECK (total_amount >= 0)
);
CREATE TABLE sales_order_item (
order_item_id SERIAL PRIMARY KEY,
order_id INT NOT NULL REFERENCES sales_order(order_id) ON DELETE CASCADE,
product_id INT NOT NULL REFERENCES product(product_id),
quantity INT NOT NULL CHECK (quantity > 0),
unit_price NUMERIC(18,2) NOT NULL CHECK (unit_price >= 0),
line_amount NUMERIC(18,2) NOT NULL CHECK (line_amount >= 0)
);
CREATE INDEX idx_sales_order_customer_code ON sales_order(customer_code);
CREATE INDEX idx_sales_order_order_date ON sales_order(order_date);
INSERT INTO shop (shop_code, shop_name, province, channel_type, status)
VALUES
('SHOP_HN01', 'Cua hang Cau Giay', 'Ha Noi', 'DIRECT', 'ACTIVE'),
('SHOP_DN01', 'Cua hang Hai Chau', 'Da Nang', 'DIRECT', 'ACTIVE'),
('SHOP_HCM01', 'Cua hang District 1', 'Ho Chi Minh', 'DIRECT', 'ACTIVE');
INSERT INTO product (product_code, product_name, category, price, status)
VALUES
('SIM_PREPAID', 'SIM tra truoc 4G', 'SIM', 50000, 'ACTIVE'),
('SIM_POSTPAID', 'SIM tra sau 5G', 'SIM', 80000, 'ACTIVE'),
('PACK_4G_30G', 'Goi data 30GB/thang', 'PACKAGE', 150000, 'ACTIVE'),
('FIBER_100M', 'Internet co dinh 100Mbps', 'FIBER', 250000, 'ACTIVE'),
('CLOUD_POS', 'Cloud POS cho doanh nghiep','DIGITAL', 3500000, 'ACTIVE');
INSERT INTO sales_order (order_no, customer_code, shop_id, order_date, order_status, total_amount)
SELECT 'SO20260301001', 'CUST0001', shop_id, '2026-03-01 09:00:00', 'COMPLETED', 230000
FROM shop WHERE shop_code = 'SHOP_HN01';
INSERT INTO sales_order (order_no, customer_code, shop_id, order_date, order_status, total_amount)
SELECT 'SO20260302001', 'CUST0002', shop_id, '2026-03-02 10:15:00', 'COMPLETED', 250000
FROM shop WHERE shop_code = 'SHOP_HN01';
INSERT INTO sales_order (order_no, customer_code, shop_id, order_date, order_status, total_amount)
SELECT 'SO20260303001', 'CUST0004', shop_id, '2026-03-03 15:20:00', 'COMPLETED', 3500000
FROM shop WHERE shop_code = 'SHOP_HCM01';
INSERT INTO sales_order (order_no, customer_code, shop_id, order_date, order_status, total_amount)
SELECT 'SO20260304001', 'CUST0003', shop_id, '2026-03-04 11:45:00', 'NEW', 100000
FROM shop WHERE shop_code = 'SHOP_DN01';
INSERT INTO sales_order_item (order_id, product_id, quantity, unit_price, line_amount)
SELECT so.order_id, p.product_id, 1, 80000, 80000
FROM sales_order so, product p
WHERE so.order_no = 'SO20260301001' AND p.product_code = 'SIM_POSTPAID';
INSERT INTO sales_order_item (order_id, product_id, quantity, unit_price, line_amount)
SELECT so.order_id, p.product_id, 1, 150000, 150000
FROM sales_order so, product p
WHERE so.order_no = 'SO20260301001' AND p.product_code = 'PACK_4G_30G';
INSERT INTO sales_order_item (order_id, product_id, quantity, unit_price, line_amount)
SELECT so.order_id, p.product_id, 1, 250000, 250000
FROM sales_order so, product p
WHERE so.order_no = 'SO20260302001' AND p.product_code = 'FIBER_100M';
INSERT INTO sales_order_item (order_id, product_id, quantity, unit_price, line_amount)
SELECT so.order_id, p.product_id, 1, 3500000, 3500000
FROM sales_order so, product p
WHERE so.order_no = 'SO20260303001' AND p.product_code = 'CLOUD_POS';
INSERT INTO sales_order_item (order_id, product_id, quantity, unit_price, line_amount)
SELECT so.order_id, p.product_id, 2, 50000, 100000
FROM sales_order so, product p
WHERE so.order_no = 'SO20260304001' AND p.product_code = 'SIM_PREPAID';
COMMIT;
/******************************************************************
* 3. BILLING DATABASE
******************************************************************/
\connect billing
BEGIN;
DROP TABLE IF EXISTS payment CASCADE;
DROP TABLE IF EXISTS invoice CASCADE;
DROP TABLE IF EXISTS usage_cdr CASCADE;
DROP TABLE IF EXISTS billing_account CASCADE;
DROP TABLE IF EXISTS tariff_plan CASCADE;
CREATE TABLE tariff_plan (
plan_id SERIAL PRIMARY KEY,
plan_code VARCHAR(30) UNIQUE NOT NULL,
plan_name VARCHAR(200) NOT NULL,
monthly_fee NUMERIC(18,2) NOT NULL CHECK (monthly_fee >= 0),
data_quota_gb NUMERIC(10,2) DEFAULT 0,
voice_minutes INT DEFAULT 0,
sms_count INT DEFAULT 0,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE billing_account (
billing_account_id SERIAL PRIMARY KEY,
account_no VARCHAR(30) UNIQUE NOT NULL,
customer_code VARCHAR(20) NOT NULL,
msisdn VARCHAR(20) UNIQUE NOT NULL,
plan_id INT NOT NULL REFERENCES tariff_plan(plan_id),
billing_cycle_day INT NOT NULL CHECK (billing_cycle_day BETWEEN 1 AND 28),
account_status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
credit_limit NUMERIC(18,2) NOT NULL DEFAULT 0,
current_balance NUMERIC(18,2) NOT NULL DEFAULT 0
);
CREATE TABLE invoice (
invoice_id SERIAL PRIMARY KEY,
invoice_no VARCHAR(30) UNIQUE NOT NULL,
billing_account_id INT NOT NULL REFERENCES billing_account(billing_account_id),
invoice_month DATE NOT NULL,
amount_before_tax NUMERIC(18,2) NOT NULL CHECK (amount_before_tax >= 0),
tax_amount NUMERIC(18,2) NOT NULL CHECK (tax_amount >= 0),
total_amount NUMERIC(18,2) NOT NULL CHECK (total_amount >= 0),
due_date DATE NOT NULL,
payment_status VARCHAR(20) NOT NULL
);
CREATE TABLE payment (
payment_id SERIAL PRIMARY KEY,
payment_ref VARCHAR(30) UNIQUE NOT NULL,
invoice_id INT NOT NULL REFERENCES invoice(invoice_id),
payment_date TIMESTAMP NOT NULL,
amount NUMERIC(18,2) NOT NULL CHECK (amount >= 0),
channel VARCHAR(30) NOT NULL,
payment_status VARCHAR(20) NOT NULL
);
CREATE TABLE usage_cdr (
cdr_id BIGSERIAL PRIMARY KEY,
msisdn VARCHAR(20) NOT NULL,
usage_type VARCHAR(20) NOT NULL,
event_time TIMESTAMP NOT NULL,
duration_sec INT DEFAULT 0,
data_mb NUMERIC(12,2) DEFAULT 0,
charge_amount NUMERIC(18,2) NOT NULL DEFAULT 0,
source_system VARCHAR(50) NOT NULL
);
CREATE INDEX idx_billing_account_customer_code ON billing_account(customer_code);
CREATE INDEX idx_invoice_month ON invoice(invoice_month);
CREATE INDEX idx_usage_cdr_msisdn_event_time ON usage_cdr(msisdn, event_time);
INSERT INTO tariff_plan (plan_code, plan_name, monthly_fee, data_quota_gb, voice_minutes, sms_count, status)
VALUES
('BASIC_4G', 'Goi cuoc Basic 4G', 100000, 15, 100, 50, 'ACTIVE'),
('PLUS_5G', 'Goi cuoc Plus 5G', 150000, 30, 200, 100, 'ACTIVE'),
('FIBER_HOME_100', 'Internet gia dinh 100Mbps', 250000, 0, 0, 0, 'ACTIVE'),
('ENT_DATA_500', 'Goi doanh nghiep 500GB', 500000, 500,1000, 0, 'ACTIVE');
INSERT INTO billing_account (account_no, customer_code, msisdn, plan_id, billing_cycle_day, account_status, credit_limit, current_balance)
SELECT 'BA0001', 'CUST0001', '0901000001', plan_id, 5, 'ACTIVE', 500000, 0
FROM tariff_plan WHERE plan_code = 'PLUS_5G';
INSERT INTO billing_account (account_no, customer_code, msisdn, plan_id, billing_cycle_day, account_status, credit_limit, current_balance)
SELECT 'BA0002', 'CUST0002', '0901000002', plan_id, 10, 'ACTIVE', 300000, 25000
FROM tariff_plan WHERE plan_code = 'BASIC_4G';
INSERT INTO billing_account (account_no, customer_code, msisdn, plan_id, billing_cycle_day, account_status, credit_limit, current_balance)
SELECT 'BA0003', 'CUST0003', '0901000003', plan_id, 15, 'ACTIVE', 100000, 10000
FROM tariff_plan WHERE plan_code = 'BASIC_4G';
INSERT INTO billing_account (account_no, customer_code, msisdn, plan_id, billing_cycle_day, account_status, credit_limit, current_balance)
SELECT 'BA0004', 'CUST0004', '0901000004', plan_id, 20, 'ACTIVE', 5000000, 0
FROM tariff_plan WHERE plan_code = 'ENT_DATA_500';
INSERT INTO billing_account (account_no, customer_code, msisdn, plan_id, billing_cycle_day, account_status, credit_limit, current_balance)
SELECT 'BA0005', 'CUST0005', '0901000005', plan_id, 25, 'ACTIVE', 8000000, 150000
FROM tariff_plan WHERE plan_code = 'ENT_DATA_500';
INSERT INTO invoice (invoice_no, billing_account_id, invoice_month, amount_before_tax, tax_amount, total_amount, due_date, payment_status)
SELECT 'INV2026030001', billing_account_id, DATE '2026-03-01', 180000, 18000, 198000, DATE '2026-03-20', 'PAID'
FROM billing_account WHERE account_no = 'BA0001';
INSERT INTO invoice (invoice_no, billing_account_id, invoice_month, amount_before_tax, tax_amount, total_amount, due_date, payment_status)
SELECT 'INV2026030002', billing_account_id, DATE '2026-03-01', 120000, 12000, 132000, DATE '2026-03-20', 'PENDING'
FROM billing_account WHERE account_no = 'BA0002';
INSERT INTO invoice (invoice_no, billing_account_id, invoice_month, amount_before_tax, tax_amount, total_amount, due_date, payment_status)
SELECT 'INV2026030003', billing_account_id, DATE '2026-03-01', 95000, 9500, 104500, DATE '2026-03-20', 'PAID'
FROM billing_account WHERE account_no = 'BA0003';
INSERT INTO invoice (invoice_no, billing_account_id, invoice_month, amount_before_tax, tax_amount, total_amount, due_date, payment_status)
SELECT 'INV2026030004', billing_account_id, DATE '2026-03-01', 600000, 60000, 660000, DATE '2026-03-25', 'PAID'
FROM billing_account WHERE account_no = 'BA0004';
INSERT INTO invoice (invoice_no, billing_account_id, invoice_month, amount_before_tax, tax_amount, total_amount, due_date, payment_status)
SELECT 'INV2026030005', billing_account_id, DATE '2026-03-01', 720000, 72000, 792000, DATE '2026-03-25', 'PARTIAL'
FROM billing_account WHERE account_no = 'BA0005';
INSERT INTO payment (payment_ref, invoice_id, payment_date, amount, channel, payment_status)
SELECT 'PAY2026030001', invoice_id, '2026-03-12 09:30:00', 198000, 'MOMO', 'SUCCESS'
FROM invoice WHERE invoice_no = 'INV2026030001';
INSERT INTO payment (payment_ref, invoice_id, payment_date, amount, channel, payment_status)
SELECT 'PAY2026030003', invoice_id, '2026-03-14 15:10:00', 104500, 'BANKING', 'SUCCESS'
FROM invoice WHERE invoice_no = 'INV2026030003';
INSERT INTO payment (payment_ref, invoice_id, payment_date, amount, channel, payment_status)
SELECT 'PAY2026030004', invoice_id, '2026-03-16 08:45:00', 660000, 'AUTO_DEBIT', 'SUCCESS'
FROM invoice WHERE invoice_no = 'INV2026030004';
INSERT INTO payment (payment_ref, invoice_id, payment_date, amount, channel, payment_status)
SELECT 'PAY2026030005', invoice_id, '2026-03-18 11:00:00', 500000, 'BANKING', 'SUCCESS'
FROM invoice WHERE invoice_no = 'INV2026030005';
INSERT INTO usage_cdr (msisdn, usage_type, event_time, duration_sec, data_mb, charge_amount, source_system)
VALUES
('0901000001', 'VOICE', '2026-03-10 08:10:00', 180, 0, 3000, 'OCS'),
('0901000001', 'DATA', '2026-03-10 12:15:00', 0, 1024, 12000, 'PGW'),
('0901000002', 'DATA', '2026-03-11 20:45:00', 0, 512, 8000, 'PGW'),
('0901000003', 'SMS', '2026-03-12 09:05:00', 0, 0, 500, 'SMSC'),
('0901000004', 'DATA', '2026-03-12 17:30:00', 0, 20480,150000,'PGW'),
('0901000005', 'VOICE', '2026-03-13 14:00:00', 600, 0, 10000, 'OCS');
COMMIT;
/******************************************************************
* 4. MARKETING DATABASE
******************************************************************/
\connect marketing
BEGIN;
DROP TABLE IF EXISTS campaign_response CASCADE;
DROP TABLE IF EXISTS campaign_target CASCADE;
DROP TABLE IF EXISTS campaign CASCADE;
DROP TABLE IF EXISTS segment CASCADE;
CREATE TABLE segment (
segment_id SERIAL PRIMARY KEY,
segment_code VARCHAR(30) UNIQUE NOT NULL,
segment_name VARCHAR(200) NOT NULL,
criteria_desc TEXT
);
CREATE TABLE campaign (
campaign_id SERIAL PRIMARY KEY,
campaign_code VARCHAR(30) UNIQUE NOT NULL,
campaign_name VARCHAR(200) NOT NULL,
campaign_type VARCHAR(30) NOT NULL,
segment_id INT REFERENCES segment(segment_id),
start_date DATE NOT NULL,
end_date DATE NOT NULL,
budget NUMERIC(18,2) NOT NULL CHECK (budget >= 0),
status VARCHAR(20) NOT NULL
);
CREATE TABLE campaign_target (
target_id SERIAL PRIMARY KEY,
campaign_id INT NOT NULL REFERENCES campaign(campaign_id) ON DELETE CASCADE,
customer_code VARCHAR(20) NOT NULL,
msisdn VARCHAR(20) NOT NULL,
offer_code VARCHAR(30) NOT NULL,
channel VARCHAR(30) NOT NULL,
target_status VARCHAR(20) NOT NULL
);
CREATE TABLE campaign_response (
response_id SERIAL PRIMARY KEY,
target_id INT NOT NULL REFERENCES campaign_target(target_id) ON DELETE CASCADE,
response_time TIMESTAMP NOT NULL,
response_type VARCHAR(30) NOT NULL,
converted_order_no VARCHAR(30),
notes TEXT
);
CREATE INDEX idx_campaign_target_customer_code ON campaign_target(customer_code);
CREATE INDEX idx_campaign_dates ON campaign(start_date, end_date);
INSERT INTO segment (segment_code, segment_name, criteria_desc)
VALUES
('SEG_YOUTH', 'Khach hang tre uu tien data', 'Age < 30, monthly data usage > 10GB'),
('SEG_FAMILY', 'Ho gia dinh su dung internet co dinh', 'Fiber/home internet customers'),
('SEG_ENTERPRISE', 'Khach hang doanh nghiep', 'Business and enterprise accounts');
INSERT INTO campaign (campaign_code, campaign_name, campaign_type, segment_id, start_date, end_date, budget, status)
SELECT 'CMP_5G_MAR', 'Chien dich nang cap 5G thang 3', 'UPSELL', segment_id, '2026-03-01', '2026-03-31', 100000000, 'RUNNING'
FROM segment WHERE segment_code = 'SEG_YOUTH';
INSERT INTO campaign (campaign_code, campaign_name, campaign_type, segment_id, start_date, end_date, budget, status)
SELECT 'CMP_FIBER_Q2', 'Chien dich internet gia dinh quy 2', 'CROSS_SELL', segment_id, '2026-04-01', '2026-06-30', 200000000, 'PLANNED'
FROM segment WHERE segment_code = 'SEG_FAMILY';
INSERT INTO campaign (campaign_code, campaign_name, campaign_type, segment_id, start_date, end_date, budget, status)
SELECT 'CMP_BIZ_BUNDLE', 'Goi tong the doanh nghiep', 'RETENTION', segment_id, '2026-03-05', '2026-04-30', 300000000, 'RUNNING'
FROM segment WHERE segment_code = 'SEG_ENTERPRISE';
INSERT INTO campaign_target (campaign_id, customer_code, msisdn, offer_code, channel, target_status)
SELECT campaign_id, 'CUST0001', '0901000001', 'OFFER_5G_30G', 'SMS', 'SENT'
FROM campaign WHERE campaign_code = 'CMP_5G_MAR';
INSERT INTO campaign_target (campaign_id, customer_code, msisdn, offer_code, channel, target_status)
SELECT campaign_id, 'CUST0002', '0901000002', 'OFFER_5G_20G', 'APP_PUSH', 'SENT'
FROM campaign WHERE campaign_code = 'CMP_5G_MAR';
INSERT INTO campaign_target (campaign_id, customer_code, msisdn, offer_code, channel, target_status)
SELECT campaign_id, 'CUST0004', '0901000004', 'OFFER_BIZ_COMBO', 'EMAIL', 'SENT'
FROM campaign WHERE campaign_code = 'CMP_BIZ_BUNDLE';
INSERT INTO campaign_target (campaign_id, customer_code, msisdn, offer_code, channel, target_status)
SELECT campaign_id, 'CUST0005', '0901000005', 'OFFER_BIZ_VIP', 'ACCOUNT_MANAGER', 'CONTACTED'
FROM campaign WHERE campaign_code = 'CMP_BIZ_BUNDLE';
INSERT INTO campaign_response (target_id, response_time, response_type, converted_order_no, notes)
SELECT target_id, '2026-03-10 10:20:00', 'ACCEPTED', 'SO20260301001', 'Customer accepted offer via SMS short code'
FROM campaign_target
WHERE customer_code = 'CUST0001' AND offer_code = 'OFFER_5G_30G';
INSERT INTO campaign_response (target_id, response_time, response_type, converted_order_no, notes)
SELECT target_id, '2026-03-12 16:00:00', 'REJECTED', NULL, 'No demand for upgrade this month'
FROM campaign_target
WHERE customer_code = 'CUST0002' AND offer_code = 'OFFER_5G_20G';
INSERT INTO campaign_response (target_id, response_time, response_type, converted_order_no, notes)
SELECT target_id, '2026-03-15 09:45:00', 'INTERESTED', NULL, 'Requested quotation for multi-site bundle'
FROM campaign_target
WHERE customer_code = 'CUST0004' AND offer_code = 'OFFER_BIZ_COMBO';
COMMIT;
/******************************************************************
* 5. ERP DATABASE
******************************************************************/
\connect erp
BEGIN;
DROP TABLE IF EXISTS asset CASCADE;
DROP TABLE IF EXISTS purchase_order CASCADE;
DROP TABLE IF EXISTS vendor CASCADE;
DROP TABLE IF EXISTS employee CASCADE;
DROP TABLE IF EXISTS department CASCADE;
CREATE TABLE department (
dept_id SERIAL PRIMARY KEY,
dept_code VARCHAR(20) UNIQUE NOT NULL,
dept_name VARCHAR(200) NOT NULL,
cost_center VARCHAR(30) NOT NULL
);
CREATE TABLE employee (
emp_id SERIAL PRIMARY KEY,
emp_code VARCHAR(20) UNIQUE NOT NULL,
full_name VARCHAR(200) NOT NULL,
dept_id INT NOT NULL REFERENCES department(dept_id),
title VARCHAR(100) NOT NULL,
hire_date DATE NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE vendor (
vendor_id SERIAL PRIMARY KEY,
vendor_code VARCHAR(20) UNIQUE NOT NULL,
vendor_name VARCHAR(200) NOT NULL,
tax_code VARCHAR(30),
contact_phone VARCHAR(20),
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'
);
CREATE TABLE purchase_order (
po_id SERIAL PRIMARY KEY,
po_no VARCHAR(30) UNIQUE NOT NULL,
vendor_id INT NOT NULL REFERENCES vendor(vendor_id),
requester_emp_id INT NOT NULL REFERENCES employee(emp_id),
po_date DATE NOT NULL,
po_status VARCHAR(20) NOT NULL,
total_amount NUMERIC(18,2) NOT NULL CHECK (total_amount >= 0)
);
CREATE TABLE asset (
asset_id SERIAL PRIMARY KEY,
asset_code VARCHAR(30) UNIQUE NOT NULL,
asset_name VARCHAR(200) NOT NULL,
category VARCHAR(50) NOT NULL,
purchase_date DATE NOT NULL,
po_id INT REFERENCES purchase_order(po_id),
assigned_emp_id INT REFERENCES employee(emp_id),
status VARCHAR(20) NOT NULL
);
CREATE INDEX idx_employee_dept_id ON employee(dept_id);
CREATE INDEX idx_po_date ON purchase_order(po_date);
INSERT INTO department (dept_code, dept_name, cost_center)
VALUES
('D_IT', 'Phong CNTT', 'CC1001'),
('D_FIN', 'Phong Tai chinh', 'CC1002'),
('D_SALES', 'Phong Kinh doanh', 'CC1003');
INSERT INTO employee (emp_code, full_name, dept_id, title, hire_date, status)
SELECT 'EMP0001', 'Pham Quang Huy', dept_id, 'DBA Manager', '2020-01-10', 'ACTIVE'
FROM department WHERE dept_code = 'D_IT';
INSERT INTO employee (emp_code, full_name, dept_id, title, hire_date, status)
SELECT 'EMP0002', 'Do Thi Mai', dept_id, 'Finance Specialist', '2021-03-15', 'ACTIVE'
FROM department WHERE dept_code = 'D_FIN';
INSERT INTO employee (emp_code, full_name, dept_id, title, hire_date, status)
SELECT 'EMP0003', 'Vu Duc Long', dept_id, 'Sales Executive', '2022-07-01', 'ACTIVE'
FROM department WHERE dept_code = 'D_SALES';
INSERT INTO employee (emp_code, full_name, dept_id, title, hire_date, status)
SELECT 'EMP0004', 'Nguyen Thanh Son', dept_id, 'System Engineer', '2023-04-20', 'ACTIVE'
FROM department WHERE dept_code = 'D_IT';
INSERT INTO vendor (vendor_code, vendor_name, tax_code, contact_phone, status)
VALUES
('VEND001', 'Tech Solution Vietnam', '0101111111', '02435556666', 'ACTIVE'),
('VEND002', 'Global Network JSC', '0102222222', '02837778888', 'ACTIVE'),
('VEND003', 'Enterprise Devices Co', '0103333333', '02363888999', 'ACTIVE');
INSERT INTO purchase_order (po_no, vendor_id, requester_emp_id, po_date, po_status, total_amount)
SELECT 'PO202603001', v.vendor_id, e.emp_id, '2026-03-01', 'APPROVED', 120000000
FROM vendor v, employee e
WHERE v.vendor_code = 'VEND001' AND e.emp_code = 'EMP0001';
INSERT INTO purchase_order (po_no, vendor_id, requester_emp_id, po_date, po_status, total_amount)
SELECT 'PO202603002', v.vendor_id, e.emp_id, '2026-03-05', 'APPROVED', 80000000
FROM vendor v, employee e
WHERE v.vendor_code = 'VEND002' AND e.emp_code = 'EMP0004';
INSERT INTO purchase_order (po_no, vendor_id, requester_emp_id, po_date, po_status, total_amount)
SELECT 'PO202603003', v.vendor_id, e.emp_id, '2026-03-08', 'DRAFT', 45000000
FROM vendor v, employee e
WHERE v.vendor_code = 'VEND003' AND e.emp_code = 'EMP0003';
INSERT INTO asset (asset_code, asset_name, category, purchase_date, po_id, assigned_emp_id, status)
SELECT 'ASSET_DB01', 'PostgreSQL Server 01', 'SERVER', '2026-03-02', p.po_id, e.emp_id, 'IN_USE'
FROM purchase_order p, employee e
WHERE p.po_no = 'PO202603001' AND e.emp_code = 'EMP0001';
INSERT INTO asset (asset_code, asset_name, category, purchase_date, po_id, assigned_emp_id, status)
SELECT 'ASSET_DB02', 'PostgreSQL Server 02', 'SERVER', '2026-03-02', p.po_id, e.emp_id, 'IN_USE'
FROM purchase_order p, employee e
WHERE p.po_no = 'PO202603001' AND e.emp_code = 'EMP0004';
INSERT INTO asset (asset_code, asset_name, category, purchase_date, po_id, assigned_emp_id, status)
SELECT 'ASSET_SW01', 'SAN Switch 01', 'NETWORK', '2026-03-06', p.po_id, e.emp_id, 'IN_USE'
FROM purchase_order p, employee e
WHERE p.po_no = 'PO202603002' AND e.emp_code = 'EMP0004';
INSERT INTO asset (asset_code, asset_name, category, purchase_date, po_id, assigned_emp_id, status)
SELECT 'ASSET_LAP01', 'Laptop Sales Lead', 'END_USER_DEVICE', '2026-03-09', p.po_id, e.emp_id, 'ALLOCATED'
FROM purchase_order p, employee e
WHERE p.po_no = 'PO202603003' AND e.emp_code = 'EMP0003';
COMMIT;
/******************************************************************
* 6. POSTGRES DATABASE (OPS / ADMIN SAMPLE)
******************************************************************/
\connect postgres
BEGIN;
DROP TABLE IF EXISTS alert_event CASCADE;
DROP TABLE IF EXISTS backup_history CASCADE;
DROP TABLE IF EXISTS job_run_log CASCADE;
DROP TABLE IF EXISTS system_parameter CASCADE;
CREATE TABLE system_parameter (
param_id SERIAL PRIMARY KEY,
param_key VARCHAR(100) UNIQUE NOT NULL,
param_value VARCHAR(255) NOT NULL,
description VARCHAR(255)
);
CREATE TABLE job_run_log (
job_id SERIAL PRIMARY KEY,
job_name VARCHAR(100) NOT NULL,
run_time TIMESTAMP NOT NULL,
run_status VARCHAR(20) NOT NULL,
duration_sec INT NOT NULL DEFAULT 0,
details TEXT
);
CREATE TABLE backup_history (
backup_id SERIAL PRIMARY KEY,
backup_type VARCHAR(30) NOT NULL,
backup_start TIMESTAMP NOT NULL,
backup_end TIMESTAMP NOT NULL,
backup_status VARCHAR(20) NOT NULL,
backup_size_mb NUMERIC(18,2) NOT NULL,
storage_location VARCHAR(255) NOT NULL
);
CREATE TABLE alert_event (
alert_id SERIAL PRIMARY KEY,
alert_level VARCHAR(20) NOT NULL,
source_component VARCHAR(100) NOT NULL,
alert_time TIMESTAMP NOT NULL,
alert_message TEXT NOT NULL,
resolved_flag BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE INDEX idx_job_run_log_time ON job_run_log(run_time);
CREATE INDEX idx_alert_event_time ON alert_event(alert_time);
INSERT INTO system_parameter (param_key, param_value, description)
VALUES
('env', 'UAT', 'Environment name'),
('backup_retention_days', '7', 'Backup retention policy'),
('wal_archive_enabled', 'true', 'Archive WAL setting'),
('monitoring_enabled', 'true', 'Prometheus/Grafana integration'),
('bss_cluster_name', 'pg-bss-cluster-01', 'Logical cluster name');
INSERT INTO job_run_log (job_name, run_time, run_status, duration_sec, details)
VALUES
('daily_billing_extract', '2026-03-13 01:00:00', 'SUCCESS', 320, 'Exported 125000 billing rows'),
('crm_sync_to_dwh', '2026-03-13 02:00:00', 'SUCCESS', 540, 'Synchronized CRM customer delta'),
('cdr_rating_job', '2026-03-13 03:00:00', 'FAILED', 120, 'Timeout when connecting to rating engine'),
('backup_verify_job', '2026-03-13 04:30:00', 'SUCCESS', 180, 'Checksum validation completed'),
('marketing_target_load', '2026-03-13 05:15:00', 'SUCCESS', 210, 'Inserted 10000 target rows');
INSERT INTO backup_history (backup_type, backup_start, backup_end, backup_status, backup_size_mb, storage_location)
VALUES
('FULL', '2026-03-10 00:00:00', '2026-03-10 00:45:00', 'SUCCESS', 20480, '/backup/full/20260310'),
('INCREMENTAL', '2026-03-11 00:00:00', '2026-03-11 00:12:00', 'SUCCESS', 2048, '/backup/incr/20260311'),
('INCREMENTAL', '2026-03-12 00:00:00', '2026-03-12 00:15:00', 'SUCCESS', 2300, '/backup/incr/20260312');
INSERT INTO alert_event (alert_level, source_component, alert_time, alert_message, resolved_flag)
VALUES
('WARNING', 'postgresql', '2026-03-13 09:00:00', 'Replication lag reached 25 seconds on standby node', TRUE),
('CRITICAL', 'backup', '2026-03-13 10:30:00', 'Backup storage usage exceeded 85%', FALSE),
('INFO', 'monitoring','2026-03-13 11:00:00', 'New exporter registered successfully', TRUE);
COMMIT;
3) Cách kiểm tra sau khi insert
Sau khi chạy xong, anh có thể kiểm tra nhanh:
CRM
\c crm
SELECT * FROM customer;
SELECT * FROM subscriber;
SELECT * FROM interaction_log ORDER BY interaction_time DESC;
SALE
\c sale
SELECT * FROM sales_order;
SELECT * FROM sales_order_item;
BILLING
\c billing
SELECT * FROM billing_account;
SELECT * FROM invoice;
SELECT * FROM payment;
SELECT * FROM usage_cdr ORDER BY event_time DESC;
MARKETING
\c marketing
SELECT * FROM campaign;
SELECT * FROM campaign_target;
SELECT * FROM campaign_response;
ERP
\c erp
SELECT * FROM employee;
SELECT * FROM purchase_order;
SELECT * FROM asset;
POSTGRES
\c postgres
SELECT * FROM system_parameter;
SELECT * FROM job_run_log ORDER BY run_time DESC;
SELECT * FROM backup_history;
SELECT * FROM alert_event;
TƯ VẤN: Click Here hoặc Hotline/Zalo 090.29.12.888
=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội
=============================
cơ sở dữ liệu, cơ sở dữ liệu quốc gia, database, AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c/21c/23c/23ai, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, docker, k8s, micro service, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
