Why table design matters in SQL - See It in Action

Why Table Design Matters

📖 Scenario: You are working for a small bookstore that wants to keep track of its books and authors. To do this well, you need to design tables in a database that store this information clearly and efficiently.

🎯 Goal: Build a simple database with two tables: one for books and one for authors. Learn why designing these tables properly helps keep data organized and easy to use.

📋 What You'll Learn

Create a table called authors with columns author_id (integer, primary key) and author_name (text).

Create a table called books with columns book_id (integer, primary key), title (text), and author_id (integer) to link to authors.

Add a foreign key constraint on books.author_id referencing authors.author_id.

Understand how this design helps avoid duplicate author data and keeps book information connected to authors.

💡 Why This Matters

🌍 Real World

Bookstores, libraries, and many businesses use databases with well-designed tables to keep their data clean and connected.

💼 Career

Understanding table design and foreign keys is essential for database administrators, developers, and data analysts to build reliable and efficient databases.

1

Create the authors table

Write a SQL statement to create a table called authors with two columns: author_id as an integer primary key, and author_name as text.

SQL

-- Your code here to create the authors table

Need a hint?

Use CREATE TABLE authors and define author_id as the primary key.

2

Create the books table

Write a SQL statement to create a table called books with three columns: book_id as an integer primary key, title as text, and author_id as an integer.

SQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT
);
-- Your code here to create the books table

Need a hint?

Remember to define book_id as the primary key and include author_id as an integer column.

3

Add a foreign key to link books to authors

Modify the books table creation to add a foreign key constraint on author_id that references authors.author_id.

SQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT
);

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER,
    -- Your code here to add the foreign key constraint
);

Need a hint?

Use FOREIGN KEY (author_id) REFERENCES authors(author_id) inside the books table definition.

4

Explain why this table design matters

Add a SQL comment at the end of your code explaining why linking books to authors with a foreign key helps keep data organized and avoids duplication.

SQL

CREATE TABLE authors (
    author_id INTEGER PRIMARY KEY,
    author_name TEXT
);

CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
-- Your comment here explaining why this design matters

Need a hint?

Write a comment starting with -- that explains the benefit of using a foreign key here.