Database Schema for XYZ Store E-Commerce Portal

1. Customer Table

Column Name Data Type Constraints Description

customer_id INT PRIMARY Unique ID for
KEY,AUTO_IN each customer
CREMENT
first_name VARCHAR(50) NOT NULL Customer's first
name
last_name VARCHAR(50) NOT NULL Customer's last
name
email VARCHAR(100 UNIQUE, NOT Customer's
) NULL email address
phone_number VARCHAR(15) NOT NULL Customer's
phone number
shipping_addre VARCHAR(100 NOT NULL Shipping
ss ) address
password_hash VARCHAR(255 NOT NULL Hashed
) password for
security
created_at DATETIME DEFAULT Date and time
CURRENT_TI the account
MESTAMP was created
2. Product Table

Column Name Data Type Constraints Description

product_id INT PRIMARY Unique ID for
KEY,AUTO_iNC each product
REMENT
product_name VARCHAR(100 NOT NULL Name of the
product
description VARCHAR(50) Detailed
description of
the product
price DECIMAL(10,2) NOT NULL Price of the
Product
stock INT NOT NULL Quantity
available in
stock
category VARCHAR(50) Category of the
product
created_by INT FOREIGN KEY Admin who
REFERENCES added the
Admin(admin_id) product
image_url VARCHAR(255) URL for product
image
3. Order Table

Column Name Data Type Constraints Description

order_id INT PRIMARY KEY, Unique ID for
AUTO_INCREM each order
ENT
customer_id INT FOREIGN KEY Customer
REFERENCES placing the order
Customer(custo
mer_id)
order_date DATETIME DEFAULT Date and time
CURRENT_TIM the order was
ESTAMP placed
total_amount DECIMAL(10,2) NOT NULL Total amount for
the order
payment_metho VARCHAR(50) NOT NULL Method of
d payment
shipping_addres VARCHAR(50) NOT NULL Shipping
s address for the
order
order_status VARCHAR(20) DEFAULT Status of the
‘Pending’ order
4. OrderItem Table

Column Name Data Type Constraints Description

order_item_id INT PRIMARY KEY, Unique ID for
AUTO_INCREM each order item
EN
order_id INT FOREIGN KEY ID of the
REFERENCES associated order
Order(order_id
product_id INT FOREIGN KEY ID of the product
REFERENCES
Product(product
_id)
quantity INT NOT NULL Quantity of the
product ordered
price INT NOT NULL Price of the
product at the
time of order
5. Payment Table

Column Name Data Type Constraints Description

payment_id INT PRIMARY KEY, Unique ID for
AUTO_INCREM each payment
EN
order_id INT FOREIGN KEY Associated order
REFERENCES ID
Order(order_id
payment_date DATETIME DEFAULT Date and time of
CURRENT_TIM the payment
ESTAMP
amount_paid DECIMAL(10,2 NOT NULL Amount Paid
payment_status VARCHAR(20) DEFAULT Payment status
‘Pending’ (e.g., Success,
Failed)
6. Shipping Table

Column Name Data Type Constraints Description

shipping_id INT PRIMARY KEY, Unique ID for
AUTO_INCREMEN each shipment
T
order_id INT FOREIGN KEY Associated
REFERENCES order ID
Order(order_id)
shipping_date DATETIME Date the order
was shipped
tracking_numbe VARCHAR(50 UNIQUE Tracking
r ) number for
shipment
delivery_date DATETIME Estimated
delivery date
shipping_status VARCHAR(20 DEFAULT 'In Current status of
) Transit' the shipment
7. Return Table

Column Name Data Type Constraints Description

return_id INT PRIMARY KEY, Unique ID for
AUTO_INCREMEN each return
T request
order_item_id INT FOREIGN KEY Item being
REFERENCES returned
OrderItem(order_ite
m_id)
return_date DATETIME DEFAULT Date the return
CURRENT_TIMES was initiated
TAMP
return_status VARCHAR(20) DEFAULT 'Pending' Status of the
return
refund_amount DECIMAL(10,2 Amount
) refunded
8. Feedback Table

Column Name Data Type Constraints Description

feedback_id INT PRIMARY KEY, Unique ID for
AUTO_INCREME each feedback
NT

order_id INT FOREIGN KEY Associated order

REFERENCES ID
Order(order_id)

product_id INT FOREIGN KEY Product being

REFERENCES reviewed
Product(product_i
d)

rating INT CHECK (rating Rating provided

BETWEEN 1 AND by the customer
5)

comment VARCHAR DEFAULT Customer's review

CURRENT_TIME comment
STAMP

feedback_date DATETIME DEFAULT Date feedback

CURRENT_TIME was provided
STAMP
Input and Output Interfaces
1. Customer Registration and Login Interface

● Input: Customer details including first_

name,last_name,email,phone_number,shipping_address, and
password
● Output: Confirmation message for account creation or login, error
messages for missing/invalid details.

2. Product Browsing and Search Interface

● Input: Search keywords, filters (e.g., category, price range).

● Output: List of products matching the criteria, with details like
product_name,price, and stock.

3. Shopping Cart Interface

● Input: Product selections and quantities.

● Output: Updated shopping cart with selected items and subtotal.
4. Checkout Interface

● Input: Customer shipping details, payment method selection.

● Output: Order summary with total amount, estimated delivery, and a
confirmation prompt.

5. Order Tracking Interface

● Input: Order ID or tracking number.

● Output: Current order status, shipment status, and estimated delivery
date.
6. Returns and Refunds Interface

● Input: Return request details (e.g order_item_id and reason for

return).
● Output: Confirmation of return request initiation and estimated refund
amount.

7. Feedback and Support Interface

● Input: Customer feedback (e.g., rating, comment) and support

queries.
● Output: Confirmation of feedback submission or support ticket
creation, estimated response time.