364 lines
17 KiB
SQL
364 lines
17 KiB
SQL
|
-- Oracle 12c+ schema for an e-commerce and accounting workload.
|
||
|
|
-- Run as the schema owner in SQL*Plus or SQLcl. This script creates objects only.
|
||
|
|
-- The interval-partitioned tables create one-day partitions automatically as dates
|
||
|
|
-- are inserted beyond their initial boundary.
|
||
|
|
|
||
|
|
WHENEVER SQLERROR EXIT SQL.SQLCODE
|
||
|
|
SET DEFINE OFF
|
||
|
|
|
||
|
|
CREATE TABLE currencies (
|
||
|
|
currency_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
currency_code CHAR(3) NOT NULL,
|
||
|
|
currency_name VARCHAR2(60) NOT NULL,
|
||
|
|
currency_symbol VARCHAR2(8),
|
||
|
|
CONSTRAINT pk_currencies PRIMARY KEY (currency_id),
|
||
|
|
CONSTRAINT uq_currencies_code UNIQUE (currency_code)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE sales_channels (
|
||
|
|
channel_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
channel_name VARCHAR2(40) NOT NULL,
|
||
|
|
CONSTRAINT pk_sales_channels PRIMARY KEY (channel_id),
|
||
|
|
CONSTRAINT uq_sales_channels_name UNIQUE (channel_name)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE customers (
|
||
|
|
customer_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
email VARCHAR2(254) NOT NULL,
|
||
|
|
first_name VARCHAR2(60) NOT NULL,
|
||
|
|
last_name VARCHAR2(60) NOT NULL,
|
||
|
|
phone_number VARCHAR2(30),
|
||
|
|
customer_since DATE DEFAULT SYSDATE NOT NULL,
|
||
|
|
customer_status VARCHAR2(12) DEFAULT 'ACTIVE' NOT NULL,
|
||
|
|
CONSTRAINT pk_customers PRIMARY KEY (customer_id),
|
||
|
|
CONSTRAINT uq_customers_email UNIQUE (email),
|
||
|
|
CONSTRAINT ck_customers_status CHECK (customer_status IN ('ACTIVE', 'INACTIVE', 'BLOCKED'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE customer_addresses (
|
||
|
|
address_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
customer_id NUMBER NOT NULL,
|
||
|
|
address_type VARCHAR2(12) NOT NULL,
|
||
|
|
address_line1 VARCHAR2(120) NOT NULL,
|
||
|
|
address_line2 VARCHAR2(120),
|
||
|
|
city VARCHAR2(80) NOT NULL,
|
||
|
|
region VARCHAR2(80),
|
||
|
|
postal_code VARCHAR2(20),
|
||
|
|
country_code CHAR(2) NOT NULL,
|
||
|
|
is_default CHAR(1) DEFAULT 'N' NOT NULL,
|
||
|
|
CONSTRAINT pk_customer_addresses PRIMARY KEY (address_id),
|
||
|
|
CONSTRAINT fk_addresses_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
|
||
|
|
CONSTRAINT ck_addresses_type CHECK (address_type IN ('BILLING', 'SHIPPING', 'BOTH')),
|
||
|
|
CONSTRAINT ck_addresses_default CHECK (is_default IN ('Y', 'N'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE suppliers (
|
||
|
|
supplier_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
supplier_name VARCHAR2(120) NOT NULL,
|
||
|
|
email VARCHAR2(254),
|
||
|
|
country_code CHAR(2) NOT NULL,
|
||
|
|
supplier_status VARCHAR2(12) DEFAULT 'ACTIVE' NOT NULL,
|
||
|
|
CONSTRAINT pk_suppliers PRIMARY KEY (supplier_id),
|
||
|
|
CONSTRAINT uq_suppliers_name UNIQUE (supplier_name),
|
||
|
|
CONSTRAINT ck_suppliers_status CHECK (supplier_status IN ('ACTIVE', 'INACTIVE'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE product_categories (
|
||
|
|
category_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
parent_category_id NUMBER,
|
||
|
|
category_name VARCHAR2(100) NOT NULL,
|
||
|
|
CONSTRAINT pk_product_categories PRIMARY KEY (category_id),
|
||
|
|
CONSTRAINT fk_category_parent FOREIGN KEY (parent_category_id) REFERENCES product_categories (category_id),
|
||
|
|
CONSTRAINT uq_category_name UNIQUE (category_name)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE products (
|
||
|
|
product_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
category_id NUMBER NOT NULL,
|
||
|
|
supplier_id NUMBER NOT NULL,
|
||
|
|
sku VARCHAR2(40) NOT NULL,
|
||
|
|
product_name VARCHAR2(160) NOT NULL,
|
||
|
|
unit_price NUMBER(12,2) NOT NULL,
|
||
|
|
product_status VARCHAR2(12) DEFAULT 'ACTIVE' NOT NULL,
|
||
|
|
CONSTRAINT pk_products PRIMARY KEY (product_id),
|
||
|
|
CONSTRAINT uq_products_sku UNIQUE (sku),
|
||
|
|
CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES product_categories (category_id),
|
||
|
|
CONSTRAINT fk_products_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (supplier_id),
|
||
|
|
CONSTRAINT ck_products_price CHECK (unit_price >= 0),
|
||
|
|
CONSTRAINT ck_products_status CHECK (product_status IN ('ACTIVE', 'DISCONTINUED'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE warehouses (
|
||
|
|
warehouse_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
warehouse_name VARCHAR2(100) NOT NULL,
|
||
|
|
country_code CHAR(2) NOT NULL,
|
||
|
|
CONSTRAINT pk_warehouses PRIMARY KEY (warehouse_id),
|
||
|
|
CONSTRAINT uq_warehouses_name UNIQUE (warehouse_name)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE inventory_balances (
|
||
|
|
product_id NUMBER NOT NULL,
|
||
|
|
warehouse_id NUMBER NOT NULL,
|
||
|
|
quantity_on_hand NUMBER(12,2) DEFAULT 0 NOT NULL,
|
||
|
|
quantity_reserved NUMBER(12,2) DEFAULT 0 NOT NULL,
|
||
|
|
last_counted_at DATE,
|
||
|
|
CONSTRAINT pk_inventory_balances PRIMARY KEY (product_id, warehouse_id),
|
||
|
|
CONSTRAINT fk_invbal_product FOREIGN KEY (product_id) REFERENCES products (product_id),
|
||
|
|
CONSTRAINT fk_invbal_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (warehouse_id),
|
||
|
|
CONSTRAINT ck_invbal_on_hand CHECK (quantity_on_hand >= 0),
|
||
|
|
CONSTRAINT ck_invbal_reserved CHECK (quantity_reserved >= 0 AND quantity_reserved <= quantity_on_hand)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE sales_orders (
|
||
|
|
order_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
order_date DATE NOT NULL,
|
||
|
|
customer_id NUMBER NOT NULL,
|
||
|
|
channel_id NUMBER NOT NULL,
|
||
|
|
currency_id NUMBER NOT NULL,
|
||
|
|
billing_address_id NUMBER NOT NULL,
|
||
|
|
shipping_address_id NUMBER NOT NULL,
|
||
|
|
order_status VARCHAR2(16) DEFAULT 'PENDING' NOT NULL,
|
||
|
|
subtotal NUMBER(14,2) NOT NULL,
|
||
|
|
tax_amount NUMBER(14,2) DEFAULT 0 NOT NULL,
|
||
|
|
shipping_amount NUMBER(14,2) DEFAULT 0 NOT NULL,
|
||
|
|
CONSTRAINT pk_sales_orders PRIMARY KEY (order_id),
|
||
|
|
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
|
||
|
|
CONSTRAINT fk_orders_channel FOREIGN KEY (channel_id) REFERENCES sales_channels (channel_id),
|
||
|
|
CONSTRAINT fk_orders_currency FOREIGN KEY (currency_id) REFERENCES currencies (currency_id),
|
||
|
|
CONSTRAINT fk_orders_bill_addr FOREIGN KEY (billing_address_id) REFERENCES customer_addresses (address_id),
|
||
|
|
CONSTRAINT fk_orders_ship_addr FOREIGN KEY (shipping_address_id) REFERENCES customer_addresses (address_id),
|
||
|
|
CONSTRAINT ck_orders_status CHECK (order_status IN ('PENDING', 'PAID', 'FULFILLED', 'CANCELLED', 'REFUNDED')),
|
||
|
|
CONSTRAINT ck_orders_amounts CHECK (subtotal >= 0 AND tax_amount >= 0 AND shipping_amount >= 0)
|
||
|
|
)
|
||
|
|
PARTITION BY RANGE (order_date)
|
||
|
|
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
|
||
|
|
(
|
||
|
|
PARTITION p_orders_seed VALUES LESS THAN (DATE '2025-01-01')
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE sales_order_lines (
|
||
|
|
order_line_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
order_id NUMBER NOT NULL,
|
||
|
|
product_id NUMBER NOT NULL,
|
||
|
|
line_number NUMBER(4) NOT NULL,
|
||
|
|
quantity NUMBER(12,2) NOT NULL,
|
||
|
|
unit_price NUMBER(12,2) NOT NULL,
|
||
|
|
discount_amount NUMBER(12,2) DEFAULT 0 NOT NULL,
|
||
|
|
CONSTRAINT pk_sales_order_lines PRIMARY KEY (order_line_id),
|
||
|
|
CONSTRAINT fk_lines_order FOREIGN KEY (order_id) REFERENCES sales_orders (order_id),
|
||
|
|
CONSTRAINT fk_lines_product FOREIGN KEY (product_id) REFERENCES products (product_id),
|
||
|
|
CONSTRAINT uq_lines_order_num UNIQUE (order_id, line_number),
|
||
|
|
CONSTRAINT ck_lines_values CHECK (quantity > 0 AND unit_price >= 0 AND discount_amount >= 0)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE payments (
|
||
|
|
payment_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
payment_date DATE NOT NULL,
|
||
|
|
order_id NUMBER NOT NULL,
|
||
|
|
currency_id NUMBER NOT NULL,
|
||
|
|
payment_method VARCHAR2(16) NOT NULL,
|
||
|
|
payment_status VARCHAR2(16) NOT NULL,
|
||
|
|
amount NUMBER(14,2) NOT NULL,
|
||
|
|
provider_reference VARCHAR2(80),
|
||
|
|
CONSTRAINT pk_payments PRIMARY KEY (payment_id),
|
||
|
|
CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES sales_orders (order_id),
|
||
|
|
CONSTRAINT fk_payments_currency FOREIGN KEY (currency_id) REFERENCES currencies (currency_id),
|
||
|
|
CONSTRAINT ck_payments_method CHECK (payment_method IN ('CARD', 'BANK', 'WALLET', 'CASH')),
|
||
|
|
CONSTRAINT ck_payments_status CHECK (payment_status IN ('AUTHORIZED', 'CAPTURED', 'FAILED', 'REFUNDED')),
|
||
|
|
CONSTRAINT ck_payments_amount CHECK (amount >= 0)
|
||
|
|
)
|
||
|
|
PARTITION BY RANGE (payment_date)
|
||
|
|
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
|
||
|
|
(
|
||
|
|
PARTITION p_payments_seed VALUES LESS THAN (DATE '2025-01-01')
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE shipments (
|
||
|
|
shipment_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
shipped_at DATE NOT NULL,
|
||
|
|
order_id NUMBER NOT NULL,
|
||
|
|
warehouse_id NUMBER NOT NULL,
|
||
|
|
tracking_number VARCHAR2(80) NOT NULL,
|
||
|
|
carrier_name VARCHAR2(60) NOT NULL,
|
||
|
|
shipment_status VARCHAR2(16) NOT NULL,
|
||
|
|
CONSTRAINT pk_shipments PRIMARY KEY (shipment_id),
|
||
|
|
CONSTRAINT fk_shipments_order FOREIGN KEY (order_id) REFERENCES sales_orders (order_id),
|
||
|
|
CONSTRAINT fk_shipments_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (warehouse_id),
|
||
|
|
CONSTRAINT uq_shipments_tracking UNIQUE (tracking_number),
|
||
|
|
CONSTRAINT ck_shipments_status CHECK (shipment_status IN ('CREATED', 'IN_TRANSIT', 'DELIVERED', 'LOST', 'RETURNED'))
|
||
|
|
)
|
||
|
|
PARTITION BY RANGE (shipped_at)
|
||
|
|
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
|
||
|
|
(
|
||
|
|
PARTITION p_shipments_seed VALUES LESS THAN (DATE '2025-01-01')
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE shipment_items (
|
||
|
|
shipment_item_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
shipment_id NUMBER NOT NULL,
|
||
|
|
order_line_id NUMBER NOT NULL,
|
||
|
|
quantity NUMBER(12,2) NOT NULL,
|
||
|
|
CONSTRAINT pk_shipment_items PRIMARY KEY (shipment_item_id),
|
||
|
|
CONSTRAINT fk_shipitems_shipment FOREIGN KEY (shipment_id) REFERENCES shipments (shipment_id),
|
||
|
|
CONSTRAINT fk_shipitems_line FOREIGN KEY (order_line_id) REFERENCES sales_order_lines (order_line_id),
|
||
|
|
CONSTRAINT ck_shipitems_qty CHECK (quantity > 0)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE purchase_orders (
|
||
|
|
purchase_order_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
ordered_at DATE NOT NULL,
|
||
|
|
supplier_id NUMBER NOT NULL,
|
||
|
|
warehouse_id NUMBER NOT NULL,
|
||
|
|
currency_id NUMBER NOT NULL,
|
||
|
|
purchase_status VARCHAR2(16) NOT NULL,
|
||
|
|
CONSTRAINT pk_purchase_orders PRIMARY KEY (purchase_order_id),
|
||
|
|
CONSTRAINT fk_po_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (supplier_id),
|
||
|
|
CONSTRAINT fk_po_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (warehouse_id),
|
||
|
|
CONSTRAINT fk_po_currency FOREIGN KEY (currency_id) REFERENCES currencies (currency_id),
|
||
|
|
CONSTRAINT ck_po_status CHECK (purchase_status IN ('DRAFT', 'ORDERED', 'RECEIVED', 'CANCELLED'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE purchase_order_lines (
|
||
|
|
purchase_line_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
purchase_order_id NUMBER NOT NULL,
|
||
|
|
product_id NUMBER NOT NULL,
|
||
|
|
line_number NUMBER(4) NOT NULL,
|
||
|
|
quantity NUMBER(12,2) NOT NULL,
|
||
|
|
unit_cost NUMBER(12,2) NOT NULL,
|
||
|
|
CONSTRAINT pk_purchase_order_lines PRIMARY KEY (purchase_line_id),
|
||
|
|
CONSTRAINT fk_pol_order FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders (purchase_order_id),
|
||
|
|
CONSTRAINT fk_pol_product FOREIGN KEY (product_id) REFERENCES products (product_id),
|
||
|
|
CONSTRAINT uq_poline_order_num UNIQUE (purchase_order_id, line_number),
|
||
|
|
CONSTRAINT ck_poline_values CHECK (quantity > 0 AND unit_cost >= 0)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE inventory_movements (
|
||
|
|
movement_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
movement_at DATE NOT NULL,
|
||
|
|
product_id NUMBER NOT NULL,
|
||
|
|
warehouse_id NUMBER NOT NULL,
|
||
|
|
order_line_id NUMBER,
|
||
|
|
movement_type VARCHAR2(16) NOT NULL,
|
||
|
|
quantity_delta NUMBER(12,2) NOT NULL,
|
||
|
|
reference_note VARCHAR2(120),
|
||
|
|
CONSTRAINT pk_inventory_movements PRIMARY KEY (movement_id),
|
||
|
|
CONSTRAINT fk_movements_product FOREIGN KEY (product_id) REFERENCES products (product_id),
|
||
|
|
CONSTRAINT fk_movements_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (warehouse_id),
|
||
|
|
CONSTRAINT fk_movements_order_line FOREIGN KEY (order_line_id) REFERENCES sales_order_lines (order_line_id),
|
||
|
|
CONSTRAINT ck_movements_type CHECK (movement_type IN ('RECEIPT', 'SALE', 'RETURN', 'ADJUSTMENT')),
|
||
|
|
CONSTRAINT ck_movements_quantity CHECK (quantity_delta <> 0)
|
||
|
|
)
|
||
|
|
PARTITION BY RANGE (movement_at)
|
||
|
|
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
|
||
|
|
(
|
||
|
|
PARTITION p_movements_seed VALUES LESS THAN (DATE '2025-01-01')
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE gl_accounts (
|
||
|
|
account_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
account_code VARCHAR2(20) NOT NULL,
|
||
|
|
account_name VARCHAR2(100) NOT NULL,
|
||
|
|
account_type VARCHAR2(12) NOT NULL,
|
||
|
|
CONSTRAINT pk_gl_accounts PRIMARY KEY (account_id),
|
||
|
|
CONSTRAINT uq_gl_accounts_code UNIQUE (account_code),
|
||
|
|
CONSTRAINT ck_gl_accounts_type CHECK (account_type IN ('ASSET', 'LIABILITY', 'EQUITY', 'REVENUE', 'EXPENSE'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE journal_entries (
|
||
|
|
journal_entry_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
posted_at DATE NOT NULL,
|
||
|
|
currency_id NUMBER NOT NULL,
|
||
|
|
source_type VARCHAR2(20) NOT NULL,
|
||
|
|
source_id NUMBER,
|
||
|
|
entry_status VARCHAR2(12) DEFAULT 'POSTED' NOT NULL,
|
||
|
|
memo VARCHAR2(240) NOT NULL,
|
||
|
|
CONSTRAINT pk_journal_entries PRIMARY KEY (journal_entry_id),
|
||
|
|
CONSTRAINT fk_journal_currency FOREIGN KEY (currency_id) REFERENCES currencies (currency_id),
|
||
|
|
CONSTRAINT ck_journal_status CHECK (entry_status IN ('DRAFT', 'POSTED', 'VOID'))
|
||
|
|
)
|
||
|
|
PARTITION BY RANGE (posted_at)
|
||
|
|
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
|
||
|
|
(
|
||
|
|
PARTITION p_journal_seed VALUES LESS THAN (DATE '2025-01-01')
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE journal_lines (
|
||
|
|
journal_line_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
journal_entry_id NUMBER NOT NULL,
|
||
|
|
account_id NUMBER NOT NULL,
|
||
|
|
line_number NUMBER(4) NOT NULL,
|
||
|
|
debit_amount NUMBER(14,2) DEFAULT 0 NOT NULL,
|
||
|
|
credit_amount NUMBER(14,2) DEFAULT 0 NOT NULL,
|
||
|
|
line_memo VARCHAR2(160),
|
||
|
|
CONSTRAINT pk_journal_lines PRIMARY KEY (journal_line_id),
|
||
|
|
CONSTRAINT fk_jlines_entry FOREIGN KEY (journal_entry_id) REFERENCES journal_entries (journal_entry_id),
|
||
|
|
CONSTRAINT fk_jlines_account FOREIGN KEY (account_id) REFERENCES gl_accounts (account_id),
|
||
|
|
CONSTRAINT uq_jlines_entry_num UNIQUE (journal_entry_id, line_number),
|
||
|
|
CONSTRAINT ck_jlines_amount CHECK (
|
||
|
|
debit_amount >= 0 AND credit_amount >= 0 AND
|
||
|
|
((debit_amount > 0 AND credit_amount = 0) OR
|
||
|
|
(credit_amount > 0 AND debit_amount = 0))
|
||
|
|
)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE returns (
|
||
|
|
return_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
returned_at DATE NOT NULL,
|
||
|
|
order_line_id NUMBER NOT NULL,
|
||
|
|
customer_id NUMBER NOT NULL,
|
||
|
|
return_status VARCHAR2(16) NOT NULL,
|
||
|
|
quantity NUMBER(12,2) NOT NULL,
|
||
|
|
reason_code VARCHAR2(24) NOT NULL,
|
||
|
|
CONSTRAINT pk_returns PRIMARY KEY (return_id),
|
||
|
|
CONSTRAINT fk_returns_line FOREIGN KEY (order_line_id) REFERENCES sales_order_lines (order_line_id),
|
||
|
|
CONSTRAINT fk_returns_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
|
||
|
|
CONSTRAINT ck_returns_status CHECK (return_status IN ('REQUESTED', 'APPROVED', 'RECEIVED', 'REJECTED', 'REFUNDED')),
|
||
|
|
CONSTRAINT ck_returns_quantity CHECK (quantity > 0)
|
||
|
|
)
|
||
|
|
PARTITION BY RANGE (returned_at)
|
||
|
|
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
|
||
|
|
(
|
||
|
|
PARTITION p_returns_seed VALUES LESS THAN (DATE '2025-01-01')
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE audit_events (
|
||
|
|
event_id NUMBER GENERATED ALWAYS AS IDENTITY,
|
||
|
|
event_at DATE NOT NULL,
|
||
|
|
actor_customer_id NUMBER,
|
||
|
|
event_type VARCHAR2(30) NOT NULL,
|
||
|
|
source_ip VARCHAR2(45) NOT NULL,
|
||
|
|
details VARCHAR2(2000) NOT NULL,
|
||
|
|
CONSTRAINT pk_audit_events PRIMARY KEY (event_id),
|
||
|
|
CONSTRAINT fk_audit_customer FOREIGN KEY (actor_customer_id) REFERENCES customers (customer_id)
|
||
|
|
)
|
||
|
|
PARTITION BY RANGE (event_at)
|
||
|
|
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
|
||
|
|
(
|
||
|
|
PARTITION p_audit_seed VALUES LESS THAN (DATE '2025-01-01')
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX ix_addresses_customer ON customer_addresses (customer_id);
|
||
|
|
CREATE INDEX ix_products_category ON products (category_id);
|
||
|
|
CREATE INDEX ix_products_supplier ON products (supplier_id);
|
||
|
|
CREATE INDEX ix_invbal_warehouse ON inventory_balances (warehouse_id);
|
||
|
|
CREATE INDEX ix_orders_customer_day ON sales_orders (customer_id, order_date) LOCAL;
|
||
|
|
CREATE INDEX ix_orders_status_day ON sales_orders (order_status, order_date) LOCAL;
|
||
|
|
CREATE INDEX ix_lines_product ON sales_order_lines (product_id);
|
||
|
|
CREATE INDEX ix_payments_order_day ON payments (order_id, payment_date) LOCAL;
|
||
|
|
CREATE INDEX ix_payments_status_day ON payments (payment_status, payment_date) LOCAL;
|
||
|
|
CREATE INDEX ix_shipments_order_day ON shipments (order_id, shipped_at) LOCAL;
|
||
|
|
CREATE INDEX ix_shipments_warehouse ON shipments (warehouse_id);
|
||
|
|
CREATE INDEX ix_shipitems_line ON shipment_items (order_line_id);
|
||
|
|
CREATE INDEX ix_po_supplier ON purchase_orders (supplier_id);
|
||
|
|
CREATE INDEX ix_po_warehouse ON purchase_orders (warehouse_id);
|
||
|
|
CREATE INDEX ix_poline_product ON purchase_order_lines (product_id);
|
||
|
|
CREATE INDEX ix_movements_product_day ON inventory_movements (product_id, movement_at) LOCAL;
|
||
|
|
CREATE INDEX ix_movements_warehouse_day ON inventory_movements (warehouse_id, movement_at) LOCAL;
|
||
|
|
CREATE INDEX ix_journal_source_day ON journal_entries (source_type, posted_at) LOCAL;
|
||
|
|
CREATE INDEX ix_jlines_account ON journal_lines (account_id);
|
||
|
|
CREATE INDEX ix_returns_customer_day ON returns (customer_id, returned_at) LOCAL;
|
||
|
|
CREATE INDEX ix_returns_line ON returns (order_line_id);
|
||
|
|
CREATE INDEX ix_audit_customer_day ON audit_events (actor_customer_id, event_at) LOCAL;
|
||
|
|
CREATE INDEX ix_audit_type_day ON audit_events (event_type, event_at) LOCAL;
|
||
|
|
|
||
|
|
PROMPT Schema created: 22 tables, daily interval partitions, constraints, and indexes.
|