基于RDS for PostgreSQL的表设计入门

场景说明

某电商平台针对其核心业务场景（用户在线购物、订单管理与商品统计分析）进行数据库表结构设计，以保障关键业务数据的一致性与完整性，同时提升在高并发场景下的事务处理效率。

表设计

• 用户表(t_customers)

  该表存储用户信息，每个用户在该表中有一条记录，并且有唯一的用户ID(cust_id)。

  表1 用户表

  字段名	字段类型	字段说明
  cust_id	SERIAL	用户ID，主键。
  cust_nickname	VARCHAR(50)	用户昵称，添加非空约束。
  cust_gender	VARCHAR(10)	用户性别。
  cust_birthday	DATE	用户生日。
  cust_address	TEXT	用户收货地址，添加非空约束。

• 商品表(t_goods)

  该表存储商品信息，每件商品在该表中有一条记录，并且有唯一的商品ID(item_id)。

  表2 商品表

  字段名	字段类型	字段说明
  item_id	SERIAL	商品ID，主键。
  item_name	VARCHAR(100)	商品名称，添加非空约束。
  item_category	VARCHAR(50)	商品类别，添加非空约束。
  item_desc	TEXT	商品描述。
  item_price	DECIMAL(10,2)	商品单价，添加非空约束。
  stock_quantity	INTEGER	库存数量，添加非空约束。

• 订单表(t_orders)

  该表存储订单信息，用于关联用户和商品。每个订单在该表中有一条记录，并且有唯一的订单ID(trans_id)。

  表3 订单表

  字段名	字段类型	字段说明
  trans_id	SERIAL	订单ID，主键。
  cust_id	INTEGER	订单用户ID，关联用户表(t_customers)中的用户ID(cust_id)。
  item_id	INTEGER	订单商品ID，关联商品表(t_goods)中的商品ID(item_id)。
  purchase_quantity	INTEGER	商品购买数量，添加非空约束。
  total_price	DECIMAL(10,2)	商品总价，添加非空约束。
  order_time	TIMESTAMP	订单生成时间，添加DEFAULT约束。
  order_status	VARCHAR(20)	订单状态，添加非空约束。

创建表

1. 在数据库中创建表。
   • 创建用户表(t_customers)。

     CREATE TABLE t_customers (
       cust_id SERIAL PRIMARY KEY,
       cust_nickname VARCHAR(50) NOT NULL,
       cust_gender VARCHAR(10),
       cust_birthday DATE,
       cust_address TEXT NOT NULL
     );

   • 创建商品表(t_goods)。

     CREATE TABLE t_goods(
       item_id SERIAL PRIMARY KEY,
       item_name VARCHAR(100) NOT NULL,
       item_category VARCHAR(50) NOT NULL,
       item_desc TEXT,
       item_price DECIMAL(10,2) NOT NULL,
       stock_quantity INTEGER NOT NULL
     );

   • 创建订单表(t_orders)。

     CREATE TABLE t_orders (
       trans_id SERIAL PRIMARY KEY,
       cust_id INTEGER REFERENCES t_customers(cust_id),
       item_id INTEGER REFERENCES t_goods(item_id),
       purchase_quantity INTEGER NOT NULL,
       total_price DECIMAL(10,2) NOT NULL,
       order_time TIMESTAMP NOT NULL DEFAULT NOW(),
       order_status VARCHAR(20) NOT NULL
     );

2. 假设商城上架了一些商品，用户已经在平台上注册了账号并且购买了部分商品，相关数据已传回系统数据库。在数据库中模拟插入如下测试数据：

   -- 用户数据
   INSERT INTO t_customers (cust_nickname, cust_gender, cust_birthday, cust_address) VALUES
     ('Rich Man', 'Female', '1995-08-12', '北京市海淀区'),
     ('Superman', 'Male', '1998-03-25', '上海市浦东新区');
   -- 商品数据
   INSERT INTO t_goods(item_name, item_category, item_desc, item_price, stock_quantity) VALUES
     ('智能手机X', '电子产品', '最新款智能手机', 5999.00, 22),
     ('无线耳机Pro', '电子产品', '爆款产品', 1299.00, 200),
     ('棉质T恤', '服装', '纯棉材质', 199.00, 300);
   -- 订单数据
   INSERT INTO t_orders(cust_id, item_id, purchase_quantity, total_price, order_status) VALUES
     (1, 1, 1, 5999.00, '已支付'),
     (1, 2, 2, 2598.00, '已发货'),
     (2, 3, 5, 995.00, '已完成');

场景示例

• Superman用户查询自己的所有订单（cust_id为2）。

  SELECT m.item_name, t.purchase_quantity, t.total_price, t.order_time, t.order_status
  FROM t_customers c
  JOIN t_orders t ON c.cust_id = t.cust_id
  JOIN t_goods m ON t.item_id = m.item_id
  WHERE c.cust_id = 2;

  返回结果如下：

  item_name | purchase_quantity | total_price |         order_time         | order_status
  -----------+-------------------+-------------+----------------------------+--------------
   棉质T恤   |                 5 |      995.00 | 2025-07-31 15:04:03.593379 | 已完成
  (1 row)

• 平台统计各商品销量。

  SELECT m.item_name, SUM(t.purchase_quantity) AS total_sold
  FROM t_goods m
  LEFT JOIN t_orders t ON m.item_id = t.item_id
  GROUP BY m.item_name
  ORDER BY total_sold DESC;

  返回结果如下：

  item_name  | total_sold
  -------------+------------
   棉质T恤     |          5
   无线耳机Pro |          2
   智能手机X   |          1
  (3 rows)

• 查询库存数量低于50件的电子产品。

  SELECT item_name, stock_quantity 
  FROM t_goods
  WHERE stock_quantity < 50 
  AND item_category = '电子产品';

  返回结果如下：

  item_name | stock_quantity
  -----------+----------------
   智能手机X |             22
  (1 row)