Tutorials
Databases
Database Basics
SQL Fundamentals
Best Practices

Best Practices

Learn SQL best practices for writing clean, maintainable, and secure database code.

Naming Conventions

Tables and Columns

  • Use lowercase for table and column names
  • Use underscores for multi-word names: user_profiles
  • Use singular for table names: user not users
  • Use descriptive names: created_at not date
-- Good
CREATE TABLE user_profile (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Avoid
CREATE TABLE up (
    ID INT,
    FN VARCHAR(50),
    LN VARCHAR(50),
    date_created DATETIME
);

Primary Keys

  • Use id as the primary key column name
  • Use auto-incrementing integers or UUIDs
-- Good
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

-- Also good (for distributed systems)
CREATE TABLE users (
    id TEXT PRIMARY KEY DEFAULT (hex(randomblob(16))),
    name TEXT NOT NULL
);

Query Writing

Use Specific Columns 

-- Good
SELECT id, name, email FROM users WHERE active = 1;

-- Avoid in production
SELECT * FROM users WHERE active = 1;

Use Parameterized Queries 

-- Good (parameterized)
SELECT * FROM users WHERE email = ?;

-- Bad (SQL injection vulnerable)
SELECT * FROM users WHERE email = '" + userInput + "';

Consistent Formatting 

-- Good
SELECT 
    users.name,
    departments.name as department
FROM users
INNER JOIN departments ON users.department_id = departments.id
WHERE users.active = 1
ORDER BY users.name;

-- Harder to read
SELECT users.name, departments.name as department FROM users INNER JOIN departments ON users.department_id = departments.id WHERE users.active = 1 ORDER BY users.name;

Performance Best Practices

Index Strategy

  • Create indexes on frequently queried columns
  • Use composite indexes for multi-column queries
  • Don’t over-index (slows down writes)
-- Create indexes for common queries
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_user_active_created ON users(active, created_at);

Query Optimization

  • Use LIMIT for large result sets
  • Avoid functions on indexed columns
  • Use appropriate data types
-- Good
SELECT id, name FROM users WHERE active = 1 LIMIT 100;

-- Avoid (prevents index usage)
SELECT * FROM users WHERE UPPER(name) = 'JOHN';

Security

Input Validation

  • Validate all input data
  • Use parameterized queries
  • Implement proper error handling
-- Good (prepared statement)
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => $email]);

Access Control

  • Grant minimum necessary permissions
  • Use different users for different applications
  • Regular connection strings in configuration
-- Grant specific permissions
GRANT SELECT, INSERT, UPDATE ON app_db.users TO 'app_user'@'%';

Documentation

Table Documentation

  • Comment complex table structures
  • Document business rules
  • Use consistent naming that explains purpose
-- Users table: stores all registered user accounts
-- Business rule: email must be unique across all users
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,  -- User's unique email address
    name TEXT NOT NULL,          -- Full display name
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Account creation time
);

Practice Exercises

Test your knowledge with these exercises:

-- 1. Find all users older than 30
-- 2. Count users by age group (under 25, 25-35, over 35)
-- 3. Find the highest paid employee in each department
-- 4. List departments with no employees
-- 5. Find users who haven't logged in in the last 30 days

Resources

Congratulations! You’ve completed the SQL fundamentals tutorial. You now have the knowledge to create, query, and optimize relational databases.

Last updated on
Indexing and Performance