Database design best practices in SQL

Introduction

Good database design helps keep data organized, easy to find, and safe from mistakes.

When creating a new database for a small business to track customers and orders.

When building a website that needs to store user profiles and posts.

When setting up a system to manage inventory in a store.

When planning a database to keep records for a school or library.

When improving an old database to make it faster and easier to use.

Syntax

SQL

-- No single syntax for design, but key ideas include:
-- 1. Define tables with clear columns and data types
-- 2. Use primary keys to identify rows uniquely
-- 3. Use foreign keys to link related tables
-- 4. Avoid repeating data (normalization)
-- 5. Add indexes for faster searches

Design is about planning tables and relationships, not just writing queries.

Good design makes your database easier to update and less likely to have errors.

Examples

Create a table with a unique ID and unique email to avoid duplicates.

SQL

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

Link orders to customers using a foreign key to keep data connected.

SQL

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  OrderDate DATE,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Store customer details only in Customers table, not in Orders.

SQL

-- Avoid repeating customer info in Orders table to prevent mistakes.

Sample Program

This example shows two tables linked by a foreign key. It stores authors and their books separately to avoid repeating author info.

SQL

CREATE TABLE Authors (
  AuthorID INT PRIMARY KEY,
  Name VARCHAR(100) NOT NULL
);

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title VARCHAR(200) NOT NULL,
  AuthorID INT,
  FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

INSERT INTO Authors (AuthorID, Name) VALUES (1, 'Jane Austen');
INSERT INTO Books (BookID, Title, AuthorID) VALUES (101, 'Pride and Prejudice', 1);

SELECT Books.Title, Authors.Name FROM Books
JOIN Authors ON Books.AuthorID = Authors.AuthorID;

Important Notes

Always choose clear and meaningful names for tables and columns.

Use NOT NULL for columns that must have a value to avoid missing data.

Normalize your data to reduce duplication but balance with performance needs.

Summary

Good database design organizes data clearly and avoids mistakes.

Use keys to connect tables and keep data consistent.

Plan your tables and relationships before adding data.