Skip to content

Putting It All Together

Build a complete schema from scratch with a real-world scenario.

You have mastered the school database. Now let us apply everything you have learned to a fresh scenario: a library. You will design a complete database schema, create tables with constraints, insert data, and run complex queries using joins, aggregation, and performance analysis.

Definition

A database schema is the structural design of a database: the tables, columns, data types, and relationships between tables. A well-designed schema reduces redundancy and improves data integrity.

Required step: This stage creates a fresh library database. No prior setup is needed.

Data type choice:DECIMAL vs FLOAT

When choosing numeric types, precision matters. DECIMAL stores exact values. The number you store is the number you get back. FLOAT stores approximate values. It can introduce small rounding errors due to how computers represent floating-point numbers. For money, scores, or any value where exact precision matters, always use DECIMAL.

Step 1: Design the Schema

Before writing SQL, identify the entities and relationships. A library has books, members, and loans (which connect books to members).

Create the database and tables

CREATE DATABASE IF NOT EXISTS library;
USE library;

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author VARCHAR(100) NOT NULL,
  isbn VARCHAR(13) NOT NULL UNIQUE,
  category VARCHAR(50) NOT NULL DEFAULT 'General',
  copies_available INT NOT NULL DEFAULT 1
);

CREATE TABLE members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE loans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  book_id INT NOT NULL,
  member_id INT NOT NULL,
  loan_date DATE NOT NULL,
  return_date DATE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (book_id) REFERENCES books(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (member_id) REFERENCES members(id)
    ON DELETE CASCADE ON UPDATE CASCADE
);

Notice the constraints: NOT NULL for required fields, UNIQUE for isbn and email, DEFAULT for category and copies_available, FOREIGN KEY for relationships. DATETIME stores date-and-time values. ON DELETE RESTRICT prevents deleting a book that has any loan records.

Step 2: Insert Data

Add books, members, and loans

INSERT INTO books (title, author, isbn, category, copies_available)
VALUES
  ('The Great Gatsby', 'F. Scott Fitzgerald', '9780743273565', 'Fiction', 3),
  ('To Kill a Mockingbird', 'Harper Lee', '9780061120084', 'Fiction', 5),
  ('A Brief History of Time', 'Stephen Hawking', '9780553380163', 'Science', 2);

INSERT INTO members (name, email) VALUES
  ('Alice Johnson', 'alice@example.com'),
  ('Bob Smith', 'bob@example.com');

INSERT INTO loans (book_id, member_id, loan_date, return_date) VALUES
  (1, 1, '2024-01-15', '2024-02-01'),
  (2, 1, '2024-02-10', NULL),
  (3, 2, '2024-03-01', NULL);

Two loans are still active (return_date is NULL). Alice has two loans (one returned, one active). Bob has one active loan.

Step 3: Query Across Tables

Books currently on loan

SELECT b.title, b.author, m.name AS borrower, l.loan_date
FROM books b
INNER JOIN loans l ON b.id = l.book_id
INNER JOIN members m ON l.member_id = m.id
WHERE l.return_date IS NULL
ORDER BY l.loan_date;

Three-table join: books to loans to members. WHERE return_date IS NULL filters to active loans. Only two books are currently on loan.

Books by category with counts

SELECT category,
  COUNT(*) AS book_count,
  SUM(copies_available) AS total_copies
FROM books
GROUP BY category
ORDER BY total_copies DESC;

GROUP BY category groups books by their category. COUNT and SUM provide summaries. Fiction has 2 books with 8 total copies.

Step 4: Check Performance

EXPLAIN the complex query

EXPLAIN SELECT b.title, m.name
FROM books b
INNER JOIN loans l ON b.id = l.book_id
INNER JOIN members m ON l.member_id = m.id
WHERE l.return_date IS NULL;

Check the 'type' and 'key' columns. For this query, you will likely see type: ALL on loans (no index on return_date yet). Books and members use eq_ref via their primary keys. Consider adding an index on return_date.

Add an index for active loans

CREATE INDEX idx_loan_return ON loans (return_date);

This index speeds up queries that filter by return_date, which is common for finding active loans.

Step 5: Review the Schema

Inspect constraints and indexes

SHOW CREATE TABLE loans\G

SHOW INDEX FROM books;

SHOW CREATE TABLE shows all constraints. SHOW INDEX shows all indexes on a table, including the primary key and any indexes you created.

Why use DECIMAL for money instead of FLOAT?

A query runs slowly. What is the first thing to check?

Key Takeaways

  • Identify entities and relationships before writing CREATE TABLE.
  • Use the right data types: DECIMAL for money, INT for IDs, DATE for dates, DATETIME for date-and-time values.
  • Apply constraints from the start: NOT NULL, UNIQUE, FOREIGN KEY.
  • Join tables to combine related data; aggregate to summarize it.
  • Always run EXPLAIN on queries that will run in production.

Congratulations

You have completed the fundamentals. You now understand databases, tables, SQL commands, joins, aggregation, constraints, indexes, and schema design. The next stages cover advanced topics like schema modification, subqueries, window functions, and more. The best way to keep learning is to practice. Open the playground and build something.

Ready to test your knowledge?

Take a Quiz

Related Tutorials