【Oracle入门到删库跑路-14】实战案例：电商系统数据库设计1.1 电商系统概述 1.1.1 业务需求分析电商系统

1.1 电商系统概述

1.1.1 业务需求分析

电商系统是典型的OLTP（在线事务处理）系统，具有以下特点：

高并发读写操作
复杂的业务逻辑
严格的事务一致性要求
多样化的数据类型（商品信息、订单、用户、支付等）

1.1.2 核心业务实体

用户管理：注册、登录、个人信息
商品管理：商品信息、库存、价格、分类
订单管理：下单、支付、发货、退货
支付管理：支付渠道、交易记录
物流管理：配送地址、物流跟踪

1.2 数据库设计原则

1.2.1 设计规范

-- 命名规范
-- 表名：全小写，单词间用下划线分隔
-- 字段名：全小写，单词间用下划线分隔
-- 主键：表名_id
-- 外键：关联表名_id
-- 索引：idx_表名_字段名

1.2.2 数据类型选择

-- 数值类型
NUMBER(10)     -- 整数
NUMBER(10,2)   -- 金额，保留两位小数
NUMBER(1,0)    -- 布尔值（0或1）

-- 字符类型
VARCHAR2(50)   -- 短文本
VARCHAR2(500)  -- 中等长度文本
CLOB           -- 长文本

-- 日期时间类型
DATE           -- 日期时间
TIMESTAMP      -- 高精度时间戳

1.3 核心表结构设计

1.3.1 用户表设计

-- 用户基本信息表
CREATE TABLE users (
  user_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username VARCHAR2(50) NOT NULL UNIQUE,
  email VARCHAR2(100) NOT NULL UNIQUE,
  password_hash VARCHAR2(255) NOT NULL,
  phone VARCHAR2(20),
  real_name VARCHAR2(50),
  gender CHAR(1) CHECK (gender IN ('M', 'F')),
  birthday DATE,
  status NUMBER(1,0) DEFAULT 1, -- 1:正常 0:禁用
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_phone ON users(phone);

1.3.2 商品表设计

-- 商品分类表
CREATE TABLE categories (
  category_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  category_name VARCHAR2(100) NOT NULL,
  parent_id NUMBER REFERENCES categories(category_id),
  sort_order NUMBER DEFAULT 0,
  is_active NUMBER(1,0) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 商品信息表
CREATE TABLE products (
  product_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  product_name VARCHAR2(200) NOT NULL,
  category_id NUMBER NOT NULL REFERENCES categories(category_id),
  brand VARCHAR2(100),
  description CLOB,
  price NUMBER(10,2) NOT NULL,
  stock_quantity NUMBER DEFAULT 0,
  weight NUMBER(8,2), -- 重量(kg)
  dimensions VARCHAR2(50), -- 尺寸
  status NUMBER(1,0) DEFAULT 1, -- 1:上架 0:下架
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 商品图片表
CREATE TABLE product_images (
  image_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  product_id NUMBER NOT NULL REFERENCES products(product_id),
  image_url VARCHAR2(500) NOT NULL,
  is_primary NUMBER(1,0) DEFAULT 0,
  sort_order NUMBER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

1.3.3 订单表设计

-- 订单主表
CREATE TABLE orders (
  order_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id NUMBER NOT NULL REFERENCES users(user_id),
  order_number VARCHAR2(50) NOT NULL UNIQUE,
  total_amount NUMBER(10,2) NOT NULL,
  discount_amount NUMBER(10,2) DEFAULT 0,
  shipping_fee NUMBER(10,2) DEFAULT 0,
  final_amount NUMBER(10,2) NOT NULL,
  status VARCHAR2(20) DEFAULT 'pending', -- pending,paid,shipped,delivered,cancelled
  payment_method VARCHAR2(20), -- alipay,wechat,bank
  shipping_address CLOB,
  remark CLOB,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_order_number ON orders(order_number);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- 订单明细表
CREATE TABLE order_items (
  item_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  order_id NUMBER NOT NULL REFERENCES orders(order_id),
  product_id NUMBER NOT NULL REFERENCES products(product_id),
  quantity NUMBER NOT NULL,
  unit_price NUMBER(10,2) NOT NULL,
  total_price NUMBER(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

1.4 性能优化设计

1.4.1 分区表设计

-- 按月分区的订单表
CREATE TABLE orders_partitioned (
  order_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
  user_id NUMBER NOT NULL,
  order_number VARCHAR2(50) NOT NULL,
  total_amount NUMBER(10,2) NOT NULL,
  status VARCHAR2(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY RANGE (created_at) (
  PARTITION p202401 VALUES LESS THAN (TIMESTAMP '2024-02-01'),
  PARTITION p202402 VALUES LESS THAN (TIMESTAMP '2024-03-01'),
  PARTITION p202403 VALUES LESS THAN (TIMESTAMP '2024-04-01')
);

-- 分区索引
CREATE INDEX idx_orders_part_user_id ON orders_partitioned(user_id) LOCAL;
CREATE INDEX idx_orders_part_status ON orders_partitioned(status) LOCAL;

1.4.2 物化视图优化

-- 商品销售统计物化视图
CREATE MATERIALIZED VIEW mv_product_sales_stats
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT 
  p.product_id,
  p.product_name,
  COUNT(oi.item_id) as sales_count,
  SUM(oi.total_price) as sales_amount,
  AVG(oi.unit_price) as avg_price
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name;

-- 创建索引
CREATE INDEX idx_mv_product_sales ON mv_product_sales_stats(sales_amount DESC);

1.5 实践练习

练习1：创建完整的电商数据库结构

根据上述设计创建所有表结构
添加适当的约束和索引
插入测试数据验证表结构

练习2：优化查询性能

分析常见查询的执行计划
创建适当的索引优化查询
使用分区表优化大数据量查询

练习3：实现业务逻辑

创建存储过程处理下单流程
实现库存扣减和回滚逻辑
设计订单状态变更的触发器

1.6 总结

电商系统的数据库设计需要充分考虑业务特点和性能要求。合理的表结构设计、适当的索引策略、分区表的使用以及物化视图的应用都是提升系统性能的关键因素。在实际项目中，还需要根据具体的业务场景和数据量进行针对性的优化调整。