What is an example of a schema?

Let’s walk through an example of a database schema for a simple e-commerce application. This schema will include several tables, their relationships, and the constraints that ensure data integrity. I'll provide both a high-level overview and the corresponding SQL statements to create the schema.

Overview of the E-commerce Database Schema

The e-commerce schema consists of the following primary tables:

Customers: Stores information about customers.
Products: Stores details of products available for sale.
Orders: Records customer orders.
OrderDetails: Contains specific items within each order.
Categories: Organizes products into categories.
Suppliers: Information about product suppliers.
Reviews: Customer reviews for products.

Entity-Relationship Diagram (ERD) Representation

While I can't display an actual diagram here, I'll describe the relationships:

Customers can place multiple Orders (One-to-Many).
Orders contain multiple OrderDetails (One-to-Many).
Products can appear in multiple OrderDetails (Many-to-Many via OrderDetails).
Products belong to one Category (Many-to-One).
Products are supplied by one Supplier (Many-to-One).
Customers can write multiple Reviews for different Products (One-to-Many).

SQL Statements to Create the Schema

Here are the SQL CREATE TABLE statements that define this schema, including primary keys, foreign keys, and other constraints:

-- 1. Customers Table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    PhoneNumber VARCHAR(15),
    Address VARCHAR(255),
    City VARCHAR(50),
    State VARCHAR(50),
    ZipCode VARCHAR(10),
    Country VARCHAR(50),
    RegistrationDate DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. Categories Table
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY AUTO_INCREMENT,
    CategoryName VARCHAR(100) UNIQUE NOT NULL,
    Description TEXT
);

-- 3. Suppliers Table
CREATE TABLE Suppliers (
    SupplierID INT PRIMARY KEY AUTO_INCREMENT,
    SupplierName VARCHAR(100) UNIQUE NOT NULL,
    ContactName VARCHAR(100),
    ContactEmail VARCHAR(100),
    PhoneNumber VARCHAR(15),
    Address VARCHAR(255),
    City VARCHAR(50),
    State VARCHAR(50),
    ZipCode VARCHAR(10),
    Country VARCHAR(50)
);

-- 4. Products Table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(150) NOT NULL,
    Description TEXT,
    Price DECIMAL(10, 2) NOT NULL CHECK (Price >= 0),
    StockQuantity INT NOT NULL CHECK (StockQuantity >= 0),
    CategoryID INT,
    SupplierID INT,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) ON DELETE SET NULL,
    FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID) ON DELETE SET NULL
);

-- 5. Orders Table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY AUTO_INCREMENT,
    CustomerID INT NOT NULL,
    OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    TotalAmount DECIMAL(10, 2) NOT NULL CHECK (TotalAmount >= 0),
    Status VARCHAR(50) DEFAULT 'Pending',
    ShippingAddress VARCHAR(255),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);

-- 6. OrderDetails Table
CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY AUTO_INCREMENT,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity > 0),
    UnitPrice DECIMAL(10, 2) NOT NULL CHECK (UnitPrice >= 0),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE
);

-- 7. Reviews Table
CREATE TABLE Reviews (
    ReviewID INT PRIMARY KEY AUTO_INCREMENT,
    ProductID INT NOT NULL,
    CustomerID INT NOT NULL,
    Rating INT NOT NULL CHECK (Rating >= 1 AND Rating <= 5),
    Comment TEXT,
    ReviewDate DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);

Explanation of the Schema Components

Customers Table:
- CustomerID: Unique identifier for each customer.
- FirstName, LastName, Email, etc.: Customer details.
- Email is marked as UNIQUE to prevent duplicate registrations.
- RegistrationDate defaults to the current timestamp when a new customer is added.
Categories Table:
- CategoryID: Unique identifier for each category.
- CategoryName: Name of the category, unique to avoid duplicates.
- Description: Brief description of the category.
Suppliers Table:
- SupplierID: Unique identifier for each supplier.
- SupplierName: Name of the supplier, marked as UNIQUE.
- Contact Information: Details for contacting the supplier.
Products Table:
- ProductID: Unique identifier for each product.
- ProductName, Description, Price, StockQuantity: Product details.
- CategoryID: Foreign key linking to the Categories table.
- SupplierID: Foreign key linking to the Suppliers table.
- Price and StockQuantity have CHECK constraints to ensure they are non-negative.
Orders Table:
- OrderID: Unique identifier for each order.
- CustomerID: Foreign key linking to the Customers table.
- OrderDate: Timestamp of when the order was placed.
- TotalAmount: Total cost of the order, with a CHECK to ensure it's non-negative.
- Status: Current status of the order (e.g., Pending, Shipped, Delivered).
OrderDetails Table:
- OrderDetailID: Unique identifier for each order detail record.
- OrderID: Foreign key linking to the Orders table.
- ProductID: Foreign key linking to the Products table.
- Quantity: Number of units ordered, must be greater than zero.
- UnitPrice: Price per unit at the time of order.
Reviews Table:
- ReviewID: Unique identifier for each review.
- ProductID: Foreign key linking to the Products table.
- CustomerID: Foreign key linking to the Customers table.
- Rating: Customer rating between 1 and 5.
- Comment: Optional text review.
- ReviewDate: Timestamp of when the review was submitted.

Relationships Between Tables

Customers and Orders:
- One-to-Many: A single customer can place multiple orders.
- Foreign Key: Orders.CustomerID references Customers.CustomerID.
Orders and OrderDetails:
- One-to-Many: Each order can have multiple order details (items).
- Foreign Key: OrderDetails.OrderID references Orders.OrderID.
Products and OrderDetails:
- Many-to-Many: Products can appear in multiple order details, and each order detail can include multiple products.
- Handled Via: OrderDetails table.
Categories and Products:
- One-to-Many: Each category can include multiple products.
- Foreign Key: Products.CategoryID references Categories.CategoryID.
Suppliers and Products:
- One-to-Many: Each supplier can supply multiple products.
- Foreign Key: Products.SupplierID references Suppliers.SupplierID.
Customers and Reviews:
- One-to-Many: A customer can write multiple reviews.
- Foreign Key: Reviews.CustomerID references Customers.CustomerID.
Products and Reviews:
- One-to-Many: A product can have multiple reviews.
- Foreign Key: Reviews.ProductID references Products.ProductID.

Benefits of This Schema Design

Data Integrity: Foreign keys and constraints ensure that relationships between tables remain consistent.
Scalability: The schema can easily accommodate additional features, such as adding a Discounts table or expanding customer information.
Efficiency: Proper indexing (not shown in the CREATE TABLE statements but typically added) can improve query performance.
Clarity: Organized structure makes it easier for developers and database administrators to understand and manage the data.

Example Usage Scenarios

Adding a New Customer:

INSERT INTO Customers (FirstName, LastName, Email, PhoneNumber, Address, City, State, ZipCode, Country)
VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '123-456-7890', '123 Maple Street', 'Springfield', 'IL', '62704', 'USA');

Placing an Order:

-- Create a new order
INSERT INTO Orders (CustomerID, TotalAmount, Status, ShippingAddress)
VALUES (1, 299.99, 'Pending', '123 Maple Street, Springfield, IL, 62704, USA');

-- Add order details
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice)
VALUES (1, 101, 2, 99.99),
       (1, 102, 1, 99.99);

Writing a Review:

INSERT INTO Reviews (ProductID, CustomerID, Rating, Comment)
VALUES (101, 1, 5, 'Excellent product! Highly recommended.');

Conclusion

This example schema provides a solid foundation for an e-commerce application, ensuring that all essential data elements are accounted for and properly related. By following this structured approach, you can design databases that are robust, scalable, and efficient, meeting the needs of both the application and its users.