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
- 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 );
- Create the t_customers table.
- 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)
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot