All notes
Course Notes

SQL, Keys, and Constraints in an E-commerce System

An e-commerce platform is a data-intensive system that requires efficient storage and management of customer, product, and transaction data. A well-designed relational database ensures data consistency, scalability, and integrity. **SQL (Structured Query Language)** plays a critical role in defining database structures and enforcing business rules through constraints and relationships

Written by Eric
#PostgreSQL

Selected Application: E-commerce Platform

An e-commerce platform is a data-intensive system that requires efficient storage and management of customer, product, and transaction data. A well-designed relational database ensures data consistency, scalability, and integrity. SQL (Structured Query Language) plays a critical role in defining database structures and enforcing business rules through constraints and relationships. SQL is considered a nonprocedural language that allows users to specify what data is needed while the database system determines how to retrieve it (Vidhya et al., 2016).

1. Relevant Relations

To support core business operations, the following relations are required:

Customers

Stores user information.

  • CustomerID (Primary Key)
  • FirstName
  • LastName
  • Email
  • Phone

Categories

Stores product classification.

  • CategoryID (Primary Key)
  • CategoryName

Products

Stores product details.

  • ProductID (Primary Key)
  • ProductName
  • Price
  • StockQuantity
  • CategoryID (Foreign Key)

Orders

Stores customer orders.

  • OrderID (Primary Key)
  • CustomerID (Foreign Key)
  • OrderDate
  • TotalAmount

OrderDetails (Junction Table)

Resolves the many-to-many relationship between Orders and Products.

  • OrderID (Foreign Key)
  • ProductID (Foreign Key)
  • Quantity
  • UnitPrice

This design ensures proper normalization and avoids redundancy.

2. Role of SQL in Database Implementation

SQL is essential for implementing and managing this database. Through Data Definition Language (DDL) statements, SQL enables the creation of tables, the definition of data types, and the enforcement of constraints such as primary keys and foreign keys (Vidhya et al., 2016).

For example, SQL allows the database administrator to:

  • Define relationships between tables (e.g., linking Orders to Customers)
  • Enforce referential integrity using foreign keys
  • Apply constraints to ensure valid data entry (e.g., positive pricing)

Additionally, SQL provides a standardized interface across different relational database systems such as MySQL, PostgreSQL, and Microsoft SQL Server, making it a reliable and portable solution for database development (Khan Academy, n.d.).

3. Constraints and Their Justification

Several constraints are applied to ensure data integrity:

Primary Key Constraint

  • Each table includes a primary key to uniquely identify records.
  • Ensures uniqueness and prevents duplicate entries.
  • Example: CustomerID, ProductID

Foreign Key Constraint

  • Used to maintain relationships between tables.
    • Orders.CustomerID → Customers.CustomerID
    • Products.CategoryID → Categories.CategoryID
    • OrderDetails → Orders & Products
  • Ensures that only valid references exist in the database.

NOT NULL Constraint

  • Applied to mandatory fields such as:
    • FirstName, LastName
    • ProductName
    • OrderDate
  • Prevents incomplete records.

CHECK Constraint

  • Used to enforce business rules:
    • Price > 0
    • StockQuantity >= 0
    • Quantity > 0
  • Ensures logical correctness of stored data.

UNIQUE Constraint

  • Applied to Email in the Customers table.
  • Prevents duplicate customer accounts.

4. DDL Statements

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Phone VARCHAR(20)
);

CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) CHECK (Price > 0),
    StockQuantity INT CHECK (StockQuantity >= 0),
    CategoryID INT,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE NOT NULL,
    TotalAmount DECIMAL(10,2),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT CHECK (Quantity > 0),
    UnitPrice DECIMAL(10,2) CHECK (UnitPrice > 0),
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Conclusion

SQL is a fundamental tool for managing relational databases, enabling the creation of structured schemas and the enforcement of data integrity through constraints. By applying appropriate keys and normalization techniques, the database design becomes scalable, efficient, and reliable. In the context of an e-commerce platform, such design decisions are crucial for handling complex relationships and ensuring accurate transaction processing.

Discussion Question

What problems could occur in the e-commerce database if foreign key constraints are not used between Orders and Customers?

References

  1. Khan Academy. (n.d.). Intro to SQL: Querying and managing. https://www.khanacademy.org/computing/computer-programming/sql
  2. Vidhya, V., Jeyaram, G., & Ishwarya, K. (2016). Database management systems. Alpha Science International.
Back to the notebook