Updated on 2025-09-09 GMT+08:00

RDS for PostgreSQL Table Design

RDS for PostgreSQL excels at complex online transaction processing (OLTP) transactions and supports NoSQL (JSON, XML, or hstore) and geographic information system (GIS) data types. It has earned a reputation for reliability and data integrity, and is widely used for websites, location-based applications, and complex data object processing.

This topic uses an e-commerce platform as an example to describe how to design table structures, and how to create databases and tables and process application requests.

Scenarios

An e-commerce platform requires database table structures matching its core workloads (online shopping, order management, and product statistics analysis) to ensure the consistency and integrity of mission-critical business data and speed up transaction processing when there are many concurrent requests.

Table Design

  • User table (t_customers)

    This table stores user information. Each user has a record in the table, and each user has a unique user ID (cust_id).

    Table 1 User table

    Field Name

    Field Type

    Description

    cust_id

    SERIAL

    User ID, which is the primary key.

    cust_nickname

    VARCHAR(50)

    User nickname, with the NOT NULL constraint added.

    cust_gender

    VARCHAR(10)

    User gender.

    cust_birthday

    DATE

    User birthday.

    cust_address

    TEXT

    User shipping address, with the NOT NULL constraint added.

  • Product table (t_goods)

    This table stores product details. Each product has a record in the table, and each product has a unique product ID (item_id).

    Table 2 Product table

    Field Name

    Field Type

    Description

    item_id

    SERIAL

    Product ID, which is the primary key.

    item_name

    VARCHAR(100)

    Product name, with the NOT NULL constraint added.

    item_category

    VARCHAR(50)

    Product category, with the NOT NULL constraint added.

    item_desc

    TEXT

    Product description.

    item_price

    DECIMAL(10,2)

    Unit price, with the NOT NULL constraint added.

    stock_quantity

    INTEGER

    Number in stock, with the NOT NULL constraint added.

  • Order table (t_orders)

    This table stores order information, which is used to associate users and products. Each order has a record in this table, and each order has a unique order ID (trans_id).

    Table 3 Order table

    Field Name

    Field Type

    Description

    trans_id

    SERIAL

    Order ID, which is the primary key.

    cust_id

    INTEGER

    Order user ID, which is associated with the user ID (cust_id) in the user table (t_customers).

    item_id

    INTEGER

    Order product ID, which is associated with a product ID (item_id) in the product table (t_goods).

    purchase_quantity

    INTEGER

    Purchase quantity, with the NOT NULL constraint added.

    total_price

    DECIMAL(10,2)

    Product total price, with the NOT NULL constraint added.

    order_time

    TIMESTAMP

    Order generation time, with the DEFAULT constraint added.

    order_status

    VARCHAR(20)

    Order status, with the NOT NULL constraint added.

Creating Tables

  1. Create tables in a database.
    • Create the t_customers table.
      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
      );
    • Create the t_goods table.
      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
      );
    • Create the t_orders table.
      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. Assume that some products are available in the mall, users have registered accounts on the platform and purchased some products, and the data has been sent back to the system database. Insert the following test data into the database:
    -- User data
    INSERT INTO t_customers (cust_nickname, cust_gender, cust_birthday, cust_address) VALUES
      ('Rich Man', 'Female', '1995-08-12', 'Haidian District, Beijing'),
      ('Superman', 'Male', '1998-03-25', 'Pudong New Area, Shanghai');
    -- Product data
    INSERT INTO t_goods(item_name, item_category, item_desc, item_price, stock_quantity) VALUES
      ('Smartphone X', 'Electronics', 'Latest Smartphone', 5999.00, 22),
      ('Wireless Headset Pro', 'Electronics', 'Hot Products', 1299.00, 200),
    ('Cotton T-shirt', 'Clothing', 'Pure Cotton', 199.00, 300);
    -- Order data
    INSERT INTO t_orders(cust_id, item_id, purchase_quantity, total_price, order_status) VALUES
      (1, 1, 1, 5999.00, 'Paid'),
      (1, 2, 2, 2598.00, 'Shipped'),
      (2, 3, 5, 995.00, 'Completed');

Examples

  • User Superman queries all of his orders (cust_id is 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;

    The command output is as follows:

    item_name | purchase_quantity | total_price |         order_time         | order_status
    -----------+-------------------+-------------+----------------------------+--------------
     Cotton T-shirt   |                 5 |      995.00 | 2025-07-31 15:04:03.593379 | Completed
    (1 row)
  • The platform collects statistics on the sales volume of each product.
    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;

    The command output is as follows:

    item_name  | total_sold
    -------------+------------
     Cotton T-shirt     |          5
     Wireless headset Pro |          2
    Smartphone X   |          1
    (3 rows)
  • Query electronics whose stock quantity is less than 50.
    SELECT item_name, stock_quantity 
    FROM t_goods
    WHERE stock_quantity < 50 
    AND item_category = 'Electronics';

    The command output is as follows:

    item_name | stock_quantity
    -----------+----------------
     Smartphone X |             22
    (1 row)