Database Design
Logical Schema Design
Transform your normalized data structure into a complete database schema with proper constraints, data types, and business rules
From Tables to Schema
Normalization gave us clean tables. But that's just the beginning. Logical schema design takes those tables and turns them into a real database blueprint with data types, constraints, and business rules.
Think of it like this: normalization designs the rooms in your house. Logical schema design picks the materials, adds the plumbing, and defines how everything connects. Without this step, your database will work — but it won't be reliable.
BookVault needs a schema that prevents bad data from entering, enforces business rules automatically, and handles edge cases gracefully. Here's how we build one.
1. Data Types
Choose precise storage for each column
2. Constraints
Add rules that prevent bad data
3. Relationships
Define how tables connect
4. Business Rules
Encode real-world requirements
Choosing Data Types
Every column needs a data type. And this choice matters more than most developers realize.
Pick VARCHAR(255) for everything? Your database gets bloated. Use TEXT for short fields? Queries slow down. Choose DECIMAL for prices but FLOAT for ratings? Now you're thinking like a database architect.
Common Mistake
Using VARCHAR(255) for everything because "255 is safe." This wastes storage and can impact performance. A book title rarely exceeds 200 characters, but a city name rarely exceeds 50. Size your columns based on real data.
Adding Constraints
Constraints are your database's immune system. They prevent bad data before it infects your system. No constraint means any garbage can get in.
BookVault needs constraints that mirror real business rules. Customers can't have blank emails. Book prices can't be negative. Order dates can't be in the future. Stock quantities can't be less than zero.
-- BookVault customers table with comprehensive constraints
-- Each constraint prevents a specific type of data corruption
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL CHECK (LENGTH(first_name) >= 2),
last_name VARCHAR(50) NOT NULL CHECK (LENGTH(last_name) >= 2),
email VARCHAR(254) NOT NULL UNIQUE CHECK (email LIKE '%@%.%'),
city VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Query OK, 0 rows affected (0.02 sec)
What just happened?
Each constraint serves a purpose: NOT NULL prevents empty data, UNIQUE prevents duplicate emails, and CHECK validates data format. Try this: attempt to insert a customer with email "invalid" — the database will reject it.
Here's the books table with business-critical constraints:
-- BookVault books table with price and inventory protection
-- Prevents negative prices and invalid stock levels
CREATE TABLE books (
book_id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200) NOT NULL CHECK (LENGTH(title) >= 1),
author_id INT NOT NULL,
category_id INT NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0.01),
stock_qty INT NOT NULL DEFAULT 0 CHECK (stock_qty >= 0),
published_year INT CHECK (published_year BETWEEN 1000 AND YEAR(CURDATE())),
FOREIGN KEY (author_id) REFERENCES authors(author_id),
FOREIGN KEY (category_id) REFERENCES categories(category_id)
);Query OK, 0 rows affected (0.03 sec)
Constraints prevent 80% of data corruption issues in production systems. The 10 minutes spent adding them saves hours of cleanup later.
Schema Relationships
Foreign keys create the connections between tables. But they do more than just link data — they maintain referential integrity automatically.
Think of foreign keys as database guardrails. You can't create an order for a customer who doesn't exist. You can't delete an author who still has books in the catalog. The database enforces these rules for you.
Now the orders table with complete relationship definitions:
-- BookVault orders table with foreign key relationships
-- Links customers to their orders with referential integrity
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
DEFAULT 'pending',
total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount >= 0),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);Query OK, 0 rows affected (0.04 sec)
What just happened?
ON DELETE RESTRICT prevents deleting a customer who has orders. ON UPDATE CASCADE automatically updates order records if a customer_id changes. The ENUM limits status to valid values only.
Referential Actions
When you delete or update a parent record, what happens to child records? You decide:
RESTRICT/CASCADE
RESTRICT prevents parent deletion if children exist. CASCADE updates children when parent changes. Safe and predictable.
SET NULL
Sets foreign key to NULL when parent is deleted. Use only when the relationship is truly optional.
Business Rule Constraints
Your database should understand your business. If BookVault never sells books for free, encode that rule. If orders require at least one item, make the database enforce it.
Business rules prevent logical inconsistencies. Data that's technically valid but business-wise nonsensical.
-- BookVault order_items with business rule constraints
-- Prevents zero quantities and ensures positive pricing
CREATE TABLE order_items (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
book_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0 AND quantity <= 50),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price > 0),
FOREIGN KEY (order_id) REFERENCES orders(order_id)
ON DELETE CASCADE,
FOREIGN KEY (book_id) REFERENCES books(book_id)
ON DELETE RESTRICT,
UNIQUE KEY unique_book_per_order (order_id, book_id)
);Query OK, 0 rows affected (0.05 sec)
What just happened?
The quantity check prevents ordering 0 books or unrealistic quantities (max 50). Unit price must be positive. The unique key prevents duplicate book entries in the same order — customers must update quantity instead of adding twice.
Complex Business Rules
Some rules span multiple columns or tables. BookVault might require that order total_amount matches the sum of all order_items. You can encode this with triggers or application-level validation.
-- BookVault categories table with hierarchical constraint
-- Prevents circular parent-child relationships
CREATE TABLE categories (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
parent_category_id INT NULL,
FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
ON DELETE SET NULL,
CHECK (category_id != parent_category_id)
);Query OK, 0 rows affected (0.03 sec)
Complete Schema Implementation
Here's the complete BookVault logical schema. Every table includes proper data types, constraints, and relationships. This schema prevents most common data problems automatically.
-- Complete BookVault logical schema
-- Production-ready with comprehensive constraints
-- Authors must exist before books reference them
CREATE TABLE authors (
author_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
nationality VARCHAR(50),
birth_year INT CHECK (birth_year BETWEEN 1000 AND YEAR(CURDATE()))
);
-- Categories support hierarchical structure
CREATE TABLE categories (
category_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
parent_category_id INT NULL,
FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
ON DELETE SET NULL,
CHECK (category_id != parent_category_id)
);
-- Test the schema with sample data
INSERT INTO authors (first_name, last_name, nationality)
VALUES ('Agatha', 'Christie', 'British');
INSERT INTO categories (name) VALUES ('Fiction');
SELECT author_id, CONCAT(first_name, ' ', last_name) as author_name
FROM authors;Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.02 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.01 sec) +-----------+----------------+ | author_id | author_name | +-----------+----------------+ | 1 | Agatha Christie| +-----------+----------------+ 1 row in set (0.00 sec)
The schema works! Authors and categories are ready. Now books can reference them safely. Each constraint prevents specific types of corruption, making the database self-protecting.
This logical schema becomes the blueprint for physical implementation. DBAs use it to create the actual database with proper indexing, storage optimization, and performance tuning.
Schema Design Reality
Perfect schemas don't exist. You'll discover missing constraints, need new data types, and adjust relationships as business requirements evolve. Design for change, document decisions, and version your schema like code.
Quiz
1. BookVault needs to store book prices. Which data type choice best prevents common pricing errors?
2. In the BookVault orders table, what should happen when trying to delete a customer who has existing orders?
3. BookVault wants to prevent customers from adding the same book twice to one order (they should update quantity instead). Which constraint accomplishes this?
Up Next
Physical Database Design
Transform your logical schema into an optimized physical database with indexing, partitioning, and performance considerations.