Database Modeling Examples: From Concept to Implementation

Let me walk you through several practical examples that demonstrate key database modeling concepts in action.

Example 1: E-Commerce System

This is a classic example that illustrates multiple modeling concepts.

Conceptual Model

We need to track: Customers, Products, Orders, and Order Items.

Logical Model

Customers Table

customer_id (Primary Key)
first_name
last_name
email
phone
registration_date

Products Table

product_id (Primary Key)
product_name
description
price
stock_quantity
category

Orders Table

order_id (Primary Key)
customer_id (Foreign Key → Customers)
order_date
total_amount
status

Order_Items Table (Junction table for Many-to-Many)

order_item_id (Primary Key)
order_id (Foreign Key → Orders)
product_id (Foreign Key → Products)
quantity
unit_price

Relationships:

One Customer can place Many Orders (One-to-Many)
One Order can contain Many Products, and One Product can be in Many Orders (Many-to-Many, resolved through Order_Items)

Physical Model (SQL)

sql

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0),
    category VARCHAR(50),
    INDEX idx_category (category)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
    INDEX idx_customer (customer_id),
    INDEX idx_order_date (order_date)
);

CREATE TABLE Order_Items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES Products(product_id),
    INDEX idx_order (order_id)
);

Example 2: University Course Registration

This demonstrates complex Many-to-Many relationships and additional constraints.

Entities:

Students
Courses
Instructors
Enrollments (Junction table)
Departments

Physical Model (SQL)

sql

CREATE TABLE Departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL UNIQUE,
    building VARCHAR(50)
);

CREATE TABLE Instructors (
    instructor_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    department_id INT,
    hire_date DATE,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

CREATE TABLE Students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    enrollment_date DATE,
    major VARCHAR(100)
);

CREATE TABLE Courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_code VARCHAR(10) UNIQUE NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    credits INT CHECK (credits BETWEEN 1 AND 6),
    department_id INT,
    instructor_id INT,
    max_enrollment INT DEFAULT 30,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id),
    FOREIGN KEY (instructor_id) REFERENCES Instructors(instructor_id)
);

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade VARCHAR(2),
    semester VARCHAR(20),
    year INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES Courses(course_id),
    UNIQUE KEY unique_enrollment (student_id, course_id, semester, year)
);

Example 3: Blog Platform

This shows hierarchical relationships and self-referencing tables.

sql

CREATE TABLE Users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    bio TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Posts (
    post_id INT PRIMARY KEY AUTO_INCREMENT,
    author_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    published_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'draft',
    view_count INT DEFAULT 0,
    FOREIGN KEY (author_id) REFERENCES Users(user_id),
    INDEX idx_author (author_id),
    INDEX idx_published (published_date)
);

CREATE TABLE Categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE Post_Categories (
    post_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (post_id, category_id),
    FOREIGN KEY (post_id) REFERENCES Posts(post_id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES Categories(category_id)
);

<em>-- Self-referencing table for threaded comments</em>
CREATE TABLE Comments (
    comment_id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    user_id INT NOT NULL,
    parent_comment_id INT NULL,  <em>-- Self-reference for nested comments</em>
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES Posts(post_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (parent_comment_id) REFERENCES Comments(comment_id) ON DELETE CASCADE,
    INDEX idx_post (post_id),
    INDEX idx_parent (parent_comment_id)
);

Example 4: Normalization in Action

Let’s see how normalization improves a poorly designed table.

Before Normalization (Poor Design):

sql

<em>-- This violates multiple normal forms</em>
CREATE TABLE Orders_Bad_Design (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_email VARCHAR(100),
    customer_phone VARCHAR(20),
    product1_name VARCHAR(100),
    product1_price DECIMAL(10,2),
    product2_name VARCHAR(100),
    product2_price DECIMAL(10,2),
    product3_name VARCHAR(100),
    product3_price DECIMAL(10,2)
);

Problems:

Repeating groups (product1, product2, product3)
Customer data duplicated for every order
Limited to only 3 products per order
Difficult to query and maintain

After Normalization (Good Design):

We’ve already seen this in Example 1 with separate Customers, Orders, Products, and Order_Items tables. This normalized design:

Eliminates redundancy
Allows unlimited products per order
Makes updates easier (change customer email in one place)
Improves data integrity

Example 5: Denormalization for Performance

Sometimes we intentionally denormalize for read-heavy applications.

sql

<em>-- Normalized approach (requires JOIN)</em>
CREATE TABLE Authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(100),
    bio TEXT
);

CREATE TABLE Books (
    book_id INT PRIMARY KEY,
    author_id INT,
    title VARCHAR(200),
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

<em>-- Denormalized approach (for performance)</em>
CREATE TABLE Books_Denormalized (
    book_id INT PRIMARY KEY,
    author_id INT,
    author_name VARCHAR(100),  <em>-- Redundant data</em>
    title VARCHAR(200),
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

The denormalized version stores author_name directly in Books, avoiding a JOIN when displaying book lists. The trade-off: if an author changes their name, you must update multiple records.

Example 6: One-to-One Relationship

User authentication with separate profile information.

sql

CREATE TABLE User_Accounts (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL
);

<em>-- One-to-One relationship</em>
CREATE TABLE User_Profiles (
    profile_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE NOT NULL,  <em>-- UNIQUE enforces one-to-one</em>
    full_name VARCHAR(100),
    date_of_birth DATE,
    profile_picture_url VARCHAR(255),
    address TEXT,
    FOREIGN KEY (user_id) REFERENCES User_Accounts(user_id) ON DELETE CASCADE
);

This separates frequently accessed authentication data from less frequently accessed profile data, improving performance.

These examples demonstrate how database modeling concepts apply to real-world scenarios. Each design decision involves trade-offs between normalization, performance, and maintainability based on your specific requirements.

Adman Analytics

recent posts

about