Skip to content

User Authentication Schema

Easy ~20 min

What you'll build

A SQL schema for a user authentication system with a users table, unique email constraint, an index on email for fast lookups, and a roles column.

Goals

  • Create a users table with authentication fields
  • Enforce unique email with a constraint
  • Create an index for fast email lookups
  • Add a CHECK constraint for valid roles

Prerequisites

  • CREATE TABLE and constraints
  • Understanding of indexes
  • Basic authentication concepts

Steps

  1. 1

    Create the users table

    Create a users table with id, email, password_hash, role, and timestamps. Email must be unique and not null.

    Code
    CREATE TABLE users (
      id            SERIAL PRIMARY KEY,
      email         TEXT UNIQUE NOT NULL,
      password_hash TEXT NOT NULL,
      role          TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
      created_at    TIMESTAMP DEFAULT NOW(),
      updated_at    TIMESTAMP DEFAULT NOW()
    );

    Expected result

    The users table is created with a unique email and a CHECK on role.

  2. 2

    Create an index on email

    Create an index on the email column to speed up login queries, which look up users by email.

    Code
    CREATE INDEX idx_users_email ON users(email);

    Expected result

    An index is created, making email lookups fast.

  3. 3

    Insert users

    Insert a regular user and an admin. Use placeholder password hashes (in real apps, use bcrypt or argon2).

    Code
    INSERT INTO users (email, password_hash, role) VALUES
      ('[email protected]', 'hash_ada_123', 'user'),
      ('[email protected]', 'hash_admin_456', 'admin');

    Expected result

    Two users are inserted — one regular and one admin.

  4. 4

    Query for login

    Write the query that would be used at login time — find a user by email. The index makes this fast.

    Code
    SELECT id, email, password_hash, role
    FROM users
    WHERE email = '[email protected]';

    Expected result

    The query returns Ada's user record instantly using the index.

  5. 5

    Add indexes to optimize queries

    Create indexes on frequently queried columns to improve query performance. Indexes are the primary means of database performance optimization, but be mindful of write overhead.

    Code
    -- Create indexes on frequently filtered columns
    CREATE INDEX idx_users_email ON users(email);
    CREATE INDEX idx_orders_user_id ON orders(user_id);
    CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
    
    -- Verify the index is used
    EXPLAIN SELECT * FROM orders WHERE user_id = 42;

    Expected result

    EXPLAIN output shows an index scan using idx_orders_user_id instead of a full table scan.

Try it yourself

What you learned

You built a practical authentication schema with unique constraints, a CHECK constraint for roles, and an index for fast lookups — the same patterns used in real web applications.

Related Resources

Related Lessons

Related Glossary

Related Glossary

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to topic