SQL Terminology and Structure: The Building Blocks That Make Databases Work

Now that you understand what SQL is and why it matters, it’s time to learn the vocabulary. Think of this as learning the parts of speech before you start writing sentences. Just like you need to understand nouns, verbs, and adjectives to write good English, you need to understand tables, records, and fields to write good SQL.

Here’s the thing about database terminology - it sounds more complicated than it actually is. Once you understand the basic building blocks, everything else falls into place naturally. We’re going to start with the fundamental concepts and build up to more advanced ideas in a way that makes sense.

Tables: The Foundation of Everything

A table is the most basic unit of organization in a relational database. Think of it as a specialized spreadsheet with strict rules about what can go where.

What Makes a Database Table Different from a Spreadsheet?

 1-- Database table structure is defined upfront
 2CREATE TABLE employees (
 3    employee_id INTEGER PRIMARY KEY,
 4    first_name TEXT NOT NULL,
 5    last_name TEXT NOT NULL,
 6    email TEXT UNIQUE,
 7    hire_date DATE,
 8    salary DECIMAL(10,2),
 9    department_id INTEGER
10);

Unlike a spreadsheet where you can put anything anywhere, database tables have:

Fixed column structure - you define what columns exist when you create the table
Data type enforcement - each column only accepts specific types of data
Constraint rules - built-in validation that prevents bad data

Table Naming Conventions

1-- Good table names are:
2customers          -- plural noun, descriptive
3order_items        -- underscore for multi-word names
4product_reviews    -- clear what the table contains
5
6-- Avoid:
7tbl_customers      -- unnecessary prefixes
8CustomerData       -- mixed case can cause issues
9orders2            -- numbers suggest poor planning

Records (Rows): Individual Instances of Data

A record is one complete set of information in a table. Each row represents one instance of whatever the table stores.

Understanding Records Through Examples

 1-- In an employees table, each record is one employee
 2INSERT INTO employees VALUES 
 3(1, 'Alice', 'Johnson', 'alice@company.com', '2024-01-15', 75000.00, 1),
 4(2, 'Bob', 'Smith', 'bob@company.com', '2024-02-01', 68000.00, 2),
 5(3, 'Carol', 'Williams', 'carol@company.com', '2024-01-20', 82000.00, 1);
 6
 7-- In a products table, each record is one product
 8INSERT INTO products VALUES
 9(101, 'Laptop Pro', 'High-performance laptop', 1299.99, 25),
10(102, 'Wireless Mouse', 'Ergonomic wireless mouse', 29.99, 150),
11(103, 'USB-C Hub', '7-port USB-C hub', 79.99, 80);

Key Principles About Records

Atomicity: Each record should represent exactly one thing
Completeness: Each record should contain all necessary information about that thing
Uniqueness: Each record should be distinguishable from every other record

Fields (Columns): Individual Pieces of Information

Fields define what information you store about each record. Think of them as the questions you’re answering about each item in your table.

Field Design Principles

 1-- Well-designed fields are specific and purposeful
 2CREATE TABLE customers (
 3    customer_id INTEGER PRIMARY KEY,    -- Unique identifier
 4    first_name TEXT NOT NULL,           -- Required information
 5    last_name TEXT NOT NULL,            -- Required information
 6    email TEXT UNIQUE,                  -- Must be unique across all records
 7    phone TEXT,                         -- Optional information
 8    date_of_birth DATE,                 -- Specific data type
 9    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- Automatic values
10    is_active BOOLEAN DEFAULT TRUE      -- Status flags
11);

Common Field Categories

Identifier Fields

1-- Primary keys and unique identifiers
2customer_id INTEGER PRIMARY KEY,
3order_number TEXT UNIQUE,
4sku TEXT UNIQUE

Descriptive Fields

1-- Information that describes the record
2product_name TEXT NOT NULL,
3description TEXT,
4category TEXT

Quantitative Fields

1-- Numbers, measurements, amounts
2price DECIMAL(10,2),
3quantity INTEGER,
4weight_pounds DECIMAL(8,2)

Temporal Fields

1-- Dates and times
2created_at TIMESTAMP,
3updated_at TIMESTAMP,
4expires_on DATE

Status Fields

1-- Boolean flags and status indicators
2is_active BOOLEAN DEFAULT TRUE,
3is_featured BOOLEAN DEFAULT FALSE,
4status TEXT CHECK (status IN ('pending', 'approved', 'rejected'))

Primary Keys: The Unique Identifiers

A primary key is a field (or combination of fields) that uniquely identifies each record in a table. Think of it as the table’s built-in filing system.

Why Primary Keys Matter

 1-- Without primary keys, you can't reliably identify specific records
 2-- This is problematic:
 3CREATE TABLE bad_customers (
 4    name TEXT,
 5    email TEXT,
 6    phone TEXT
 7);
 8
 9-- What happens when you have two customers named "John Smith"?
10-- How do you update the right one?
11-- How do you delete the correct record?

Primary Key Design Patterns

Auto-incrementing integers (most common)

1CREATE TABLE customers (
2    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,  -- SQLite syntax
3    name TEXT NOT NULL,
4    email TEXT UNIQUE
5);
6
7-- Database automatically assigns: 1, 2, 3, 4, 5...

Natural keys (when you have unique business identifiers)

1CREATE TABLE products (
2    sku TEXT PRIMARY KEY,  -- SKU is naturally unique
3    product_name TEXT NOT NULL,
4    price DECIMAL(10,2)
5);
6
7-- Example records: 'LAPTOP-001', 'MOUSE-002', 'HUB-003'

Composite keys (multiple fields together make uniqueness)

1CREATE TABLE order_items (
2    order_id INTEGER,
3    product_id INTEGER,
4    quantity INTEGER,
5    unit_price DECIMAL(10,2),
6    PRIMARY KEY (order_id, product_id)  -- Combination is unique
7);
8
9-- Each order can have each product only once

Data Types: Ensuring Data Quality

Data types are rules that define what kind of information can go in each field. They’re your first line of defense against bad data.

Core Data Types

Text and Strings

1-- Different systems have variations, but concepts are similar
2name TEXT,                    -- Variable-length text
3description VARCHAR(500),     -- Text with maximum length
4status CHAR(1),              -- Fixed-length text
5notes TEXT                   -- Unlimited text (in most systems)

Numbers

1-- Integers
2employee_id INTEGER,         -- Whole numbers
3quantity SMALLINT,           -- Smaller integers (saves space)
4population BIGINT            -- Very large integers
5
6-- Decimals
7price DECIMAL(10,2),         -- Exact decimal (10 digits, 2 after decimal)
8weight FLOAT,                -- Approximate decimal
9percentage REAL              -- Another decimal type

Dates and Times

1-- Time-related data
2birth_date DATE,             -- Just the date: 2024-01-15
3created_at TIMESTAMP,        -- Date and time: 2024-01-15 14:30:22
4meeting_time TIME,           -- Just the time: 14:30:22

Boolean (True/False)

1is_active BOOLEAN,           -- TRUE or FALSE
2is_premium BOOLEAN DEFAULT FALSE,
3has_discount BOOLEAN

Why Data Types Matter

 1-- Data types prevent logical errors
 2CREATE TABLE orders (
 3    order_id INTEGER,
 4    order_date DATE,        -- Can't accidentally put text here
 5    total_amount DECIMAL(10,2),  -- Can't put negative currency
 6    is_shipped BOOLEAN      -- Only TRUE/FALSE allowed
 7);
 8
 9-- This would fail:
10-- INSERT INTO orders VALUES (1, 'yesterday', 'expensive', 'maybe');
11
12-- This works:
13INSERT INTO orders VALUES (1, '2024-01-15', 299.99, FALSE);

Database Schemas: The Blueprint

A schema is the overall structure of your database - all the tables, their relationships, constraints, and rules. Think of it as the architectural blueprint for your data.

Schema Design Example

 1-- A simple e-commerce schema
 2CREATE TABLE customers (
 3    customer_id INTEGER PRIMARY KEY,
 4    first_name TEXT NOT NULL,
 5    last_name TEXT NOT NULL,
 6    email TEXT UNIQUE NOT NULL,
 7    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 8);
 9
10CREATE TABLE products (
11    product_id INTEGER PRIMARY KEY,
12    product_name TEXT NOT NULL,
13    description TEXT,
14    price DECIMAL(10,2) NOT NULL,
15    stock_quantity INTEGER DEFAULT 0
16);
17
18CREATE TABLE orders (
19    order_id INTEGER PRIMARY KEY,
20    customer_id INTEGER NOT NULL,
21    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
22    total_amount DECIMAL(10,2),
23    status TEXT CHECK (status IN ('pending', 'processing', 'shipped', 'delivered')),
24    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
25);
26
27CREATE TABLE order_items (
28    order_id INTEGER,
29    product_id INTEGER,
30    quantity INTEGER NOT NULL,
31    unit_price DECIMAL(10,2) NOT NULL,
32    PRIMARY KEY (order_id, product_id),
33    FOREIGN KEY (order_id) REFERENCES orders(order_id),
34    FOREIGN KEY (product_id) REFERENCES products(product_id)
35);

Schema Documentation

 1-- Good schemas are self-documenting
 2CREATE TABLE employees (
 3    -- Primary identifier for each employee
 4    employee_id INTEGER PRIMARY KEY,
 5    
 6    -- Personal information
 7    first_name TEXT NOT NULL,
 8    last_name TEXT NOT NULL,
 9    email TEXT UNIQUE NOT NULL,
10    
11    -- Employment details
12    hire_date DATE NOT NULL,
13    salary DECIMAL(10,2),
14    department_id INTEGER,
15    
16    -- System fields
17    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
18    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
19);

Relationships Between Tables: Making Connections

The real power of relational databases comes from connecting tables together through relationships.

Foreign Keys: Creating Connections

 1-- Foreign keys link records in different tables
 2CREATE TABLE departments (
 3    department_id INTEGER PRIMARY KEY,
 4    department_name TEXT NOT NULL,
 5    manager_id INTEGER
 6);
 7
 8CREATE TABLE employees (
 9    employee_id INTEGER PRIMARY KEY,
10    first_name TEXT NOT NULL,
11    last_name TEXT NOT NULL,
12    department_id INTEGER,
13    -- This creates the relationship:
14    FOREIGN KEY (department_id) REFERENCES departments(department_id)
15);

Types of Relationships

One-to-Many (Most Common)

 1-- One customer can have many orders
 2-- But each order belongs to exactly one customer
 3CREATE TABLE customers (
 4    customer_id INTEGER PRIMARY KEY,
 5    customer_name TEXT NOT NULL
 6);
 7
 8CREATE TABLE orders (
 9    order_id INTEGER PRIMARY KEY,
10    customer_id INTEGER,
11    order_date DATE,
12    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
13);

Many-to-Many (Requires Junction Table)

 1-- Students can take many courses
 2-- Courses can have many students
 3-- Need a junction table to connect them
 4CREATE TABLE students (
 5    student_id INTEGER PRIMARY KEY,
 6    student_name TEXT NOT NULL
 7);
 8
 9CREATE TABLE courses (
10    course_id INTEGER PRIMARY KEY,
11    course_name TEXT NOT NULL
12);
13
14CREATE TABLE student_enrollments (
15    student_id INTEGER,
16    course_id INTEGER,
17    enrollment_date DATE,
18    grade TEXT,
19    PRIMARY KEY (student_id, course_id),
20    FOREIGN KEY (student_id) REFERENCES students(student_id),
21    FOREIGN KEY (course_id) REFERENCES courses(course_id)
22);

One-to-One (Less Common)

 1-- Each employee has exactly one employee detail record
 2-- Used to separate frequently-accessed from rarely-accessed data
 3CREATE TABLE employees (
 4    employee_id INTEGER PRIMARY KEY,
 5    first_name TEXT NOT NULL,
 6    last_name TEXT NOT NULL,
 7    email TEXT UNIQUE
 8);
 9
10CREATE TABLE employee_details (
11    employee_id INTEGER PRIMARY KEY,
12    social_security_number TEXT,
13    emergency_contact TEXT,
14    medical_info TEXT,
15    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
16);

Constraints: Enforcing Data Quality

Constraints are rules that the database enforces automatically to maintain data quality and integrity.

Essential Constraints

NOT NULL - Required Fields

1CREATE TABLE customers (
2    customer_id INTEGER PRIMARY KEY,
3    first_name TEXT NOT NULL,    -- Must have a value
4    last_name TEXT NOT NULL,     -- Must have a value
5    email TEXT,                  -- Optional
6    phone TEXT                   -- Optional
7);

UNIQUE - No Duplicates

1CREATE TABLE users (
2    user_id INTEGER PRIMARY KEY,
3    username TEXT UNIQUE,        -- No two users can have same username
4    email TEXT UNIQUE,           -- No two users can have same email
5    display_name TEXT            -- Multiple users can have same display name
6);

CHECK - Value Validation

1CREATE TABLE products (
2    product_id INTEGER PRIMARY KEY,
3    product_name TEXT NOT NULL,
4    price DECIMAL(10,2) CHECK (price >= 0),           -- Price can't be negative
5    stock_quantity INTEGER CHECK (stock_quantity >= 0), -- Stock can't be negative
6    category TEXT CHECK (category IN ('electronics', 'books', 'clothing'))
7);

DEFAULT - Automatic Values

1CREATE TABLE orders (
2    order_id INTEGER PRIMARY KEY,
3    customer_id INTEGER NOT NULL,
4    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,    -- Automatic timestamp
5    status TEXT DEFAULT 'pending',                     -- Default status
6    is_priority BOOLEAN DEFAULT FALSE                  -- Default to not priority
7);

Indexes: Making Queries Fast

Indexes are like the index in the back of a book - they help the database find information quickly without scanning every record.

When to Use Indexes

 1-- Create indexes on fields you search frequently
 2CREATE TABLE customers (
 3    customer_id INTEGER PRIMARY KEY,  -- Automatically indexed
 4    first_name TEXT,
 5    last_name TEXT,
 6    email TEXT UNIQUE,               -- Automatically indexed
 7    city TEXT,
 8    state TEXT
 9);
10
11-- Add indexes for common searches
12CREATE INDEX idx_customer_last_name ON customers(last_name);
13CREATE INDEX idx_customer_location ON customers(city, state);
14
15-- Now these queries will be fast:
16-- SELECT * FROM customers WHERE last_name = 'Smith';
17-- SELECT * FROM customers WHERE city = 'Boston' AND state = 'MA';

Index Trade-offs

1-- Indexes speed up SELECT queries but slow down INSERT/UPDATE
2-- Only create indexes you actually need
3
4-- Good: Index on frequently searched field
5CREATE INDEX idx_order_date ON orders(order_date);
6
7-- Bad: Index on field you never search
8CREATE INDEX idx_customer_middle_initial ON customers(middle_initial);

Putting It All Together: A Real Schema

Let’s design a complete schema for a library management system to see how all these concepts work together:

 1-- Authors table
 2CREATE TABLE authors (
 3    author_id INTEGER PRIMARY KEY,
 4    first_name TEXT NOT NULL,
 5    last_name TEXT NOT NULL,
 6    birth_year INTEGER CHECK (birth_year > 1000 AND birth_year <= 2024),
 7    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 8);
 9
10-- Books table
11CREATE TABLE books (
12    book_id INTEGER PRIMARY KEY,
13    title TEXT NOT NULL,
14    isbn TEXT UNIQUE,
15    publication_year INTEGER,
16    pages INTEGER CHECK (pages > 0),
17    genre TEXT CHECK (genre IN ('fiction', 'non-fiction', 'mystery', 'sci-fi', 'biography')),
18    copies_available INTEGER DEFAULT 0,
19    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
20);
21
22-- Book authors junction table (many-to-many)
23CREATE TABLE book_authors (
24    book_id INTEGER,
25    author_id INTEGER,
26    author_order INTEGER DEFAULT 1,  -- First author, second author, etc.
27    PRIMARY KEY (book_id, author_id),
28    FOREIGN KEY (book_id) REFERENCES books(book_id),
29    FOREIGN KEY (author_id) REFERENCES authors(author_id)
30);
31
32-- Library members
33CREATE TABLE members (
34    member_id INTEGER PRIMARY KEY,
35    first_name TEXT NOT NULL,
36    last_name TEXT NOT NULL,
37    email TEXT UNIQUE NOT NULL,
38    phone TEXT,
39    membership_date DATE DEFAULT CURRENT_DATE,
40    is_active BOOLEAN DEFAULT TRUE,
41    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
42);
43
44-- Book checkouts
45CREATE TABLE checkouts (
46    checkout_id INTEGER PRIMARY KEY,
47    book_id INTEGER NOT NULL,
48    member_id INTEGER NOT NULL,
49    checkout_date DATE DEFAULT CURRENT_DATE,
50    due_date DATE NOT NULL,
51    return_date DATE,
52    is_returned BOOLEAN DEFAULT FALSE,
53    FOREIGN KEY (book_id) REFERENCES books(book_id),
54    FOREIGN KEY (member_id) REFERENCES members(member_id)
55);
56
57-- Useful indexes
58CREATE INDEX idx_books_title ON books(title);
59CREATE INDEX idx_books_genre ON books(genre);
60CREATE INDEX idx_members_email ON members(email);
61CREATE INDEX idx_checkouts_due_date ON checkouts(due_date);
62CREATE INDEX idx_checkouts_not_returned ON checkouts(is_returned, due_date);

This schema demonstrates:

Primary keys for unique identification
Foreign keys for relationships
Data types appropriate for each field
Constraints to maintain data quality
Indexes for common queries
Junction tables for many-to-many relationships

Common Schema Design Patterns

The User-Profile Pattern

 1-- Separate frequently-accessed from rarely-accessed data
 2CREATE TABLE users (
 3    user_id INTEGER PRIMARY KEY,
 4    username TEXT UNIQUE NOT NULL,
 5    email TEXT UNIQUE NOT NULL,
 6    password_hash TEXT NOT NULL,
 7    is_active BOOLEAN DEFAULT TRUE,
 8    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 9);
10
11CREATE TABLE user_profiles (
12    user_id INTEGER PRIMARY KEY,
13    first_name TEXT,
14    last_name TEXT,
15    bio TEXT,
16    avatar_url TEXT,
17    birth_date DATE,
18    FOREIGN KEY (user_id) REFERENCES users(user_id)
19);

The Audit Trail Pattern

 1-- Track changes for important data
 2CREATE TABLE orders (
 3    order_id INTEGER PRIMARY KEY,
 4    customer_id INTEGER NOT NULL,
 5    total_amount DECIMAL(10,2),
 6    status TEXT,
 7    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 8    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 9);
10
11CREATE TABLE order_audit (
12    audit_id INTEGER PRIMARY KEY,
13    order_id INTEGER NOT NULL,
14    old_status TEXT,
15    new_status TEXT,
16    changed_by INTEGER,
17    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
18    FOREIGN KEY (order_id) REFERENCES orders(order_id)
19);

The Soft Delete Pattern

 1-- Mark records as deleted instead of actually removing them
 2CREATE TABLE customers (
 3    customer_id INTEGER PRIMARY KEY,
 4    first_name TEXT NOT NULL,
 5    last_name TEXT NOT NULL,
 6    email TEXT UNIQUE,
 7    is_deleted BOOLEAN DEFAULT FALSE,
 8    deleted_at TIMESTAMP,
 9    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
10);
11
12-- Queries typically filter out deleted records
13-- SELECT * FROM customers WHERE is_deleted = FALSE;

The Bottom Line: Structure Enables Power

Here’s what makes database structure so important - it’s not just about organizing data, it’s about enabling the kinds of questions you can ask. A well-designed schema makes complex queries simple and fast. A poorly designed schema makes even simple questions difficult or impossible to answer.

The terminology we’ve covered - tables, records, fields, keys, constraints, indexes, relationships - these aren’t just academic concepts. They’re the building blocks that let you build systems that can handle real-world complexity while maintaining data integrity and performance.

Think of schema design as architecture for data. Just like a well-designed building can adapt to different uses over time, a well-designed database schema can support new features and requirements as your application evolves.

Master these fundamentals, and you’ll understand not just how to write SQL queries, but how to design data systems that are reliable, scalable, and maintainable. That’s the difference between writing code that works and writing code that lasts.

SQL Fundamentals: The Language That Speaks to Every Database Database Relationships Explained: How Tables Talk to Each Other