Database design best practices in SQL - Deep Dive

Overview - Database design best practices

What is it?

Database design best practices are guidelines to organize data efficiently and clearly in a database. They help create structures that store data logically, avoid errors, and make it easy to find and update information. Good design ensures the database works well as it grows and changes over time.

Why it matters

Without good design, databases become slow, confusing, and full of mistakes. This can cause lost data, wrong reports, and frustrated users. Good design saves time and money by preventing problems before they happen and making the database easy to maintain and expand.

Where it fits

Before learning database design best practices, you should understand basic database concepts like tables, rows, columns, and keys. After mastering design, you can learn advanced topics like indexing, query optimization, and database security.

Mental Model

Core Idea

Good database design organizes data clearly and efficiently to prevent errors and make data easy to use and maintain.

Think of it like...

Designing a database is like organizing a large library: books (data) must be sorted into clear sections (tables) with labels (columns) so anyone can find and add books without confusion.

┌───────────────┐      ┌───────────────┐      ┌───────────────┐
│   Customers   │      │   Orders      │      │   Products    │
├───────────────┤      ├───────────────┤      ├───────────────┤
│ CustomerID PK │◄─────│ CustomerID FK │      │ ProductID PK  │
│ Name          │      │ OrderID PK    │      │ Name          │
│ Email         │      │ ProductID FK  │─────►│ Price         │
└───────────────┘      │ Quantity      │      └───────────────┘
                       └───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Tables and Rows

Concept: Learn what tables and rows are and how they store data.

A table is like a spreadsheet with columns and rows. Each row holds one record, like one person or one order. Columns define what kind of data is stored, like name or date. Tables organize data into groups of related information.

Result

You can see data organized in rows and columns, making it easy to read and update.

Understanding tables and rows is the base for all database design because they hold the actual data.

2

Foundation

Primary Keys for Unique Identification

Concept: Introduce primary keys to uniquely identify each row in a table.

A primary key is a column or set of columns that uniquely identifies each row. For example, a CustomerID can be a primary key in a Customers table. This prevents duplicate records and helps link tables together.

Result

Each record can be found quickly and without confusion because it has a unique ID.

Knowing about primary keys prevents data duplication and is essential for connecting tables.

3

Intermediate

Using Foreign Keys to Link Tables

🤔Before reading on: do you think foreign keys store data or just point to other data? Commit to your answer.

Concept: Foreign keys connect one table to another by referencing primary keys.

A foreign key is a column in one table that refers to the primary key in another table. For example, an Orders table might have a CustomerID foreign key linking to the Customers table. This creates relationships between data, like which customer made which order.

Result

Data from different tables can be connected and combined safely.

Understanding foreign keys is key to building relationships and keeping data consistent across tables.

4

Intermediate

Normalization to Reduce Data Duplication

🤔Before reading on: do you think storing the same data in multiple places is good or bad? Commit to your answer.

Concept: Normalization organizes tables to minimize repeated data and improve consistency.

Normalization breaks data into smaller tables and links them with keys. For example, instead of storing a customer's address in every order, store it once in the Customers table. This avoids mistakes and saves space.

Result

The database uses less space and avoids conflicting data entries.

Knowing normalization helps prevent errors and keeps data clean and efficient.

5

Intermediate

Choosing Appropriate Data Types

Concept: Select the right data type for each column to store data efficiently and correctly.

Data types define what kind of data a column holds, like numbers, text, or dates. Choosing the right type helps save space and avoid errors. For example, use DATE for birthdates, INT for counts, and VARCHAR for names.

Result

Data is stored in the best format, improving speed and accuracy.

Understanding data types prevents wasted space and data errors.

6

Advanced

Indexing for Faster Data Access

🤔Before reading on: do you think indexes slow down or speed up data searches? Commit to your answer.

Concept: Indexes create quick lookup paths to find data faster.

An index is like a book's table of contents. It helps the database find rows quickly without scanning the whole table. For example, indexing a CustomerID column speeds up searches for a specific customer.

Result

Queries run faster, improving application performance.

Knowing how and when to use indexes is crucial for scaling databases efficiently.

7

Expert

Balancing Normalization and Performance

🤔Before reading on: do you think fully normalized databases always perform best? Commit to your answer.

Concept: Sometimes denormalizing data improves speed at the cost of duplication.

While normalization reduces duplication, it can require many table joins that slow queries. Denormalization stores some data redundantly to speed up reads. Experts balance these trade-offs based on use cases, like reporting or real-time apps.

Result

Databases perform well while keeping data integrity manageable.

Understanding this balance helps design databases that work well in real-world, high-demand environments.

Under the Hood

Databases store data in files on disk organized into pages. Tables map to these pages, and indexes create special data structures like B-trees to speed up lookups. Keys enforce rules to keep data unique and consistent. The database engine uses query planners to decide how to fetch data efficiently.

Why designed this way?

Database design evolved to handle growing data sizes and complex queries. Early systems stored data flatly, causing errors and slow searches. Keys and normalization were introduced to organize data logically and maintain integrity. Indexes were added to speed up access without scanning entire tables.

┌───────────────┐       ┌───────────────┐       ┌───────────────┐
│   Disk Files  │◄──────│   Database    │◄──────│   Application  │
│ (Data Pages)  │       │   Engine      │       │   Queries     │
└───────────────┘       └───────────────┘       └───────────────┘
         ▲                      ▲                      ▲
         │                      │                      │
         │                      │                      │
   ┌───────────┐          ┌───────────┐          ┌───────────┐
   │ Tables    │          │ Indexes   │          │ Keys      │
   └───────────┘          └───────────┘          └───────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does adding more indexes always make queries faster? Commit yes or no.

Common Belief: More indexes always speed up database queries.

Reality: Too many indexes slow down data inserts, updates, and deletes because each index must be updated.

Why it matters: Adding unnecessary indexes can degrade overall database performance and increase maintenance costs.

Quick: Is it okay to store the same data in multiple tables to make queries easier? Commit yes or no.

Common Belief: Duplicating data in multiple tables is fine if it makes queries simpler.

Reality: Duplicating data leads to inconsistencies and harder maintenance unless carefully managed with denormalization strategies.

Why it matters: Data duplication can cause conflicting information and bugs in applications.

Quick: Does a primary key column allow NULL values? Commit yes or no.

Common Belief: Primary key columns can have NULL values if needed.

Reality: Primary keys must be unique and NOT NULL to identify rows reliably.

Why it matters: Allowing NULL in primary keys breaks uniqueness and causes data integrity issues.

Quick: Can normalization always solve all database performance problems? Commit yes or no.

Common Belief: Normalization always improves database performance.

Reality: Normalization improves data integrity but can slow down queries due to many joins; sometimes denormalization is better for performance.

Why it matters: Relying only on normalization can cause slow applications and poor user experience.

Expert Zone

1

Indexes should be chosen based on query patterns, not just table size; a small table may not need indexes.

2

Foreign keys enforce data integrity but can impact write performance; sometimes they are omitted in high-speed systems with application-level checks.

3

Choosing between normalization and denormalization depends on workload type: OLTP systems favor normalization, OLAP systems often use denormalization.

When NOT to use

Avoid strict normalization in read-heavy reporting databases where denormalized schemas like star schemas improve query speed. Use NoSQL databases when data is unstructured or schema changes frequently.

Production Patterns

Real-world systems use hybrid designs: normalized core data with denormalized summary tables for fast reporting. Indexes are monitored and adjusted based on query logs. Foreign keys are used where data integrity is critical but sometimes disabled during bulk loads.

Connections

Software Engineering Principles

Database design builds on principles like modularity and separation of concerns.

Understanding software design helps grasp why databases separate data into tables and enforce rules to keep systems maintainable.

Information Architecture

Both organize information logically for easy access and use.

Knowing how websites or libraries organize content helps understand database schema design.

Urban Planning

Database design and urban planning both arrange elements efficiently to support growth and avoid chaos.

Seeing database tables as city blocks and keys as roads helps appreciate the importance of clear structure and connections.

Common Pitfalls

#1 Creating tables without primary keys.

Wrong approach:

CREATE TABLE Customers (Name VARCHAR(50), Email VARCHAR(50));

Correct approach:

CREATE TABLE Customers (CustomerID INT PRIMARY KEY, Name VARCHAR(50), Email VARCHAR(50));

Root cause:

Not understanding that primary keys uniquely identify records and are essential for relationships.

#2 Storing multiple values in one column.

Wrong approach:

CREATE TABLE Orders (OrderID INT PRIMARY KEY, ProductIDs VARCHAR(100)); -- storing '1,2,3' in ProductIDs

Correct approach:

CREATE TABLE OrderProducts (OrderID INT, ProductID INT, PRIMARY KEY (OrderID, ProductID));

Root cause:

Misunderstanding that columns should hold atomic (single) values to allow proper querying.

#3 Using wrong data types for columns.

Wrong approach:

CREATE TABLE Events (EventDate VARCHAR(20)); -- storing dates as text

Correct approach:

CREATE TABLE Events (EventDate DATE);

Root cause:

Not knowing that proper data types improve data integrity and query performance.

Key Takeaways

Good database design organizes data into clear tables with unique keys to avoid confusion and errors.

Using foreign keys and normalization keeps data consistent and reduces duplication.

Choosing the right data types and indexes improves performance and storage efficiency.

Balancing normalization with denormalization is key for real-world database speed and maintainability.

Understanding design trade-offs helps build databases that grow and perform well over time.

Practice

1. Which of the following is a key reason to use primary keys in database tables?

easy

A. To store large amounts of text data

B. To create duplicate records easily

C. To speed up the database server hardware

D. To uniquely identify each record in the table

Solution

1. Step 1: Understand the role of primary keys

   Primary keys uniquely identify each row in a table, preventing duplicates.

2. Step 2: Compare options with primary key purpose

   Only To uniquely identify each record in the table describes uniqueness, which is the main purpose of primary keys.

3. Final Answer:

   To uniquely identify each record in the table -> Option D

4. Quick Check:

   Primary key = unique record ID [OK]

Hint: Primary keys always ensure unique records [OK]

Common Mistakes:

• Thinking primary keys store large text
• Confusing primary keys with performance hardware
• Believing primary keys allow duplicates

2. Which SQL statement correctly creates a table with a primary key on the id column?

easy

A. CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));

B. CREATE TABLE users (id INT, name VARCHAR(50) PRIMARY KEY);

C. CREATE TABLE users (id INT, name VARCHAR(50), PRIMARY id);

D. CREATE TABLE users (id INT UNIQUE, name VARCHAR(50));

Solution

1. Step 1: Review correct primary key syntax

   The primary key is declared as id INT PRIMARY KEY inside the column definition.

2. Step 2: Check each option's syntax

   CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); uses correct syntax; A has invalid syntax; B places PRIMARY KEY on wrong column; D uses UNIQUE but not PRIMARY KEY.

3. Final Answer:

   CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); -> Option A

4. Quick Check:

   Primary key syntax = column_name TYPE PRIMARY KEY [OK]

Hint: Primary key goes right after the column type [OK]

Common Mistakes:

• Placing PRIMARY KEY on wrong column
• Using UNIQUE instead of PRIMARY KEY
• Incorrect syntax for primary key declaration

3. Given these two tables:
CREATE TABLE authors (author_id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(100), author_id INT, FOREIGN KEY (author_id) REFERENCES authors(author_id));
What will happen if you try to insert a book with author_id = 5, but no author with author_id = 5 exists?

medium

A. The insert will succeed and create a new author automatically

B. The insert will fail due to foreign key constraint violation

C. The insert will succeed but author_id will be set to NULL

D. The insert will succeed but cause duplicate author records

Solution

1. Step 1: Understand foreign key constraints

   Foreign keys require the referenced value to exist in the parent table to maintain data integrity.

2. Step 2: Apply constraint to the insert operation

   Inserting a book with author_id = 5 fails if no author with id 5 exists, causing a constraint violation.

3. Final Answer:

   The insert will fail due to foreign key constraint violation -> Option B

4. Quick Check:

   Foreign key enforces existing parent record [OK]

Hint: Foreign keys block inserts without matching parent [OK]

Common Mistakes:

• Assuming foreign keys auto-create missing parents
• Thinking foreign keys allow NULL by default
• Ignoring constraint errors on insert

4. You have a table orders with columns order_id (primary key), customer_id, and order_date. You want to add a foreign key constraint on customer_id referencing customers(customer_id). Which of these statements has an error?

medium

A. ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

B. ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

C. ALTER TABLE orders ADD FOREIGN KEY customer_id REFERENCES customers(customer_id);

D. ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE;

Solution

1. Step 1: Review correct syntax for adding foreign keys

   The correct syntax requires parentheses around the column name after FOREIGN KEY.

2. Step 2: Identify the incorrect statement

   ALTER TABLE orders ADD FOREIGN KEY customer_id REFERENCES customers(customer_id); misses parentheses around customer_id, causing syntax error.

3. Final Answer:

   ALTER TABLE orders ADD FOREIGN KEY customer_id REFERENCES customers(customer_id); -> Option C

4. Quick Check:

   Foreign key column must be in parentheses [OK]

Hint: Always use parentheses around foreign key columns [OK]

Common Mistakes:

• Omitting parentheses around column names
• Forgetting to name the constraint (optional but recommended)
• Misplacing ON DELETE options

5. You are designing a database for a library. You want to track books, authors, and the categories each book belongs to. Which design best follows database design best practices?

hard

A. Separate tables for books, authors, and categories with primary keys and foreign keys linking them

B. Separate tables for books and categories only, storing author names as text in books

C. One table for books and authors combined, and a separate table for categories

D. One table with columns: book_id, book_title, author_name, category_name

Solution

1. Step 1: Analyze normalization and relationships

   Best practice is to separate entities into tables and link them with keys to avoid duplication.

2. Step 2: Evaluate each option's design

   Separate tables for books, authors, and categories with primary keys and foreign keys linking them uses separate tables with keys, which supports clear organization and data consistency.

3. Final Answer:

   Separate tables for books, authors, and categories with primary keys and foreign keys linking them -> Option A

4. Quick Check:

   Separate tables + keys = best design [OK]

Hint: Use separate tables and keys for related data [OK]

Common Mistakes:

• Putting all data in one table causing duplicates
• Mixing unrelated data in same table
• Storing related entity names as plain text