Relational Database Schema for an Online Shopping System

The relational database schema for an online shopping system consists of several tables that interact with each other to manage customers, products, orders, and the details of each order. Below is a description of each table along with its attributes and relationships.

1. Customer Table

This table stores information about each customer who places an order.

Column Name Data Type Constraints
CustomerID INT PRIMARY KEY, AUTO_INCREMENT
FirstName VARCHAR(50) NOT NULL
LastName VARCHAR(50) NOT NULL
Email VARCHAR(100) NOT NULL, UNIQUE
Phone VARCHAR(15)
Address VARCHAR(255) NOT NULL
City VARCHAR(50) NOT NULL
State VARCHAR(50) NOT NULL
ZipCode VARCHAR(10) NOT NULL

2. Category Table

This table categorizes products for easier organization and searchability.

Column Name Data Type Constraints
CategoryID INT PRIMARY KEY, AUTO_INCREMENT
CategoryName VARCHAR(50) NOT NULL, UNIQUE
Description TEXT

3. Product Table

This table lists all products available for purchase.

Column Name Data Type Constraints
ProductID INT PRIMARY KEY, AUTO_INCREMENT
ProductName VARCHAR(100) NOT NULL
Price DECIMAL(10,2) NOT NULL
StockQuantity INT NOT NULL
CategoryID INT FOREIGN KEY REFERENCES Category(CategoryID)

4. Order Table

This table records each order placed by customers.

Column Name Data Type Constraints
OrderID INT PRIMARY KEY, AUTO_INCREMENT
CustomerID INT FOREIGN KEY REFERENCES Customer(CustomerID)
OrderDate DATETIME NOT NULL
TotalAmount DECIMAL(10,2) NOT NULL

5. Order Detail Table

This table captures the relationship between orders and products, detailing which products are included in each order along with their quantities.

Column Name Data Type Constraints
OrderDetailID INT PRIMARY KEY, AUTO_INCREMENT
OrderID INT FOREIGN KEY REFERENCES Order(OrderID)
ProductID INT FOREIGN KEY REFERENCES Product(ProductID)
Quantity INT NOT NULL
PriceAtPurchase DECIMAL(10,2) NOT NULL

Relationships Between Tables

1. Customer to Order:

– A customer can place multiple orders (One-to-Many).
– Customer.CustomerID → Order.CustomerID.

2. Category to Product:

– A category can contain multiple products (One-to-Many).
– Category.CategoryID → Product.CategoryID.

3. Order to Order Detail:

– An order can have multiple products (One-to-Many).
– Order.OrderID → Order Detail.OrderID.

4. Product to Order Detail:

– A product can appear in multiple orders (One-to-Many).
– Product.ProductID → Order Detail.ProductID.

Summary

This relational database schema effectively organizes the components of an online shopping system, allowing for efficient management of customers, products, orders, and their details. By establishing clear relationships between the tables, it facilitates data integrity and supports complex queries necessary for the functioning of an online shopping platform. This structure ensures that the system can handle multiple transactions, maintain product organization, and provide a seamless experience for customers.