Database Design & Normalization
Design well-structured relational schemas: entity-relationship modeling, normal forms (1NF–3NF), and schema design patterns for real security applications.
Learning Objectives
- → Model data with entity-relationship diagrams
- → Identify and fix 1NF, 2NF, and 3NF violations
- → Choose appropriate relationships: one-to-one, one-to-many, many-to-many
- → Design junction tables for many-to-many relationships
- → Apply schema design patterns: soft delete, audit log, status history
Entity-Relationship Modeling
Before writing SQL, model your domain:
- Entity — a thing you store data about (User, Scan, Host, Port)
- Attribute — a property of an entity (username, created_at)
- Relationship — how entities connect (User runs Scans)
Relationship cardinalities:
User ──────< Scan (one-to-many: one user, many scans)
Scan ──────< Port (one-to-many: one scan, many ports)
User >──────< Role (many-to-many: users have many roles, roles have many users)
User ──────| Profile (one-to-one: each user has one profile)
Normal Forms
Normalization reduces redundancy and update anomalies.
1NF — Atomic Values
Each column must hold one value; no repeating groups.
BAD (violates 1NF):
┌──────┬───────────────────────┐
│ host │ open_ports │
├──────┼───────────────────────┤
│ 10.1 │ 22, 80, 443 │ ← multiple values in one cell
└──────┴───────────────────────┘
GOOD (1NF):
┌──────┬──────┐
│ host │ port │
├──────┼──────┤
│ 10.1 │ 22 │
│ 10.1 │ 80 │
│ 10.1 │ 443 │
└──────┴──────┘
2NF — No Partial Dependencies
Every non-key column must depend on the WHOLE primary key (matters with composite keys).
BAD (violates 2NF) — composite PK (scan_id, host):
scan_id | host | host_os | port
1 | 10.0.1 | Linux 5.x | 22
1 | 10.0.1 | Linux 5.x | 80 ← host_os depends on host, not full PK
GOOD (2NF): split into hosts(host, host_os) and scan_ports(scan_id, host, port)
3NF — No Transitive Dependencies
Non-key columns must depend only on the primary key, not on other non-key columns.
BAD (violates 3NF):
┌──────────┬──────────┬──────────┐
│ scan_id │ user_id │ username │
├──────────┼──────────┼──────────┤
│ 1 │ 5 │ alice │ ← username depends on user_id, not scan_id
└──────────┴──────────┴──────────┘
GOOD (3NF): remove username from scans; join to users table
Normalized Schema Design
-- Users
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Hosts (separate entity)
CREATE TABLE hosts (
id INTEGER PRIMARY KEY,
ip TEXT NOT NULL UNIQUE,
os TEXT,
notes TEXT
);
-- Scans (belongs to user AND host)
CREATE TABLE scans (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
host_id INTEGER NOT NULL REFERENCES hosts(id),
status TEXT NOT NULL DEFAULT 'pending',
started_at DATETIME,
ended_at DATETIME
);
-- Ports (belongs to scan)
CREATE TABLE ports (
id INTEGER PRIMARY KEY,
scan_id INTEGER NOT NULL REFERENCES scans(id) ON DELETE CASCADE,
port INTEGER NOT NULL CHECK(port BETWEEN 1 AND 65535),
protocol TEXT NOT NULL DEFAULT 'tcp',
status TEXT NOT NULL DEFAULT 'closed',
service TEXT,
banner TEXT
);
Many-to-Many with Junction Tables
-- Users can have many roles; roles have many users
CREATE TABLE roles (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE user_roles (
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
granted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, role_id) -- composite PK prevents duplicates
);
-- Hosts can be in many groups; groups have many hosts
CREATE TABLE host_groups (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE host_group_members (
host_id INTEGER NOT NULL REFERENCES hosts(id) ON DELETE CASCADE,
group_id INTEGER NOT NULL REFERENCES host_groups(id) ON DELETE CASCADE,
PRIMARY KEY (host_id, group_id)
);
Design Patterns
Soft Delete
-- Never delete records — just mark deleted
ALTER TABLE scans ADD COLUMN deleted_at DATETIME DEFAULT NULL;
-- All queries filter out soft-deleted rows
SELECT * FROM scans WHERE deleted_at IS NULL;
-- "Delete" a record
UPDATE scans SET deleted_at = CURRENT_TIMESTAMP WHERE id = 123;
-- Recover
UPDATE scans SET deleted_at = NULL WHERE id = 123;
Audit Log
CREATE TABLE audit_log (
id INTEGER PRIMARY KEY,
table_name TEXT NOT NULL,
record_id INTEGER NOT NULL,
action TEXT NOT NULL, -- INSERT, UPDATE, DELETE
changed_by INTEGER REFERENCES users(id),
changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
old_data TEXT, -- JSON of old values
new_data TEXT -- JSON of new values
);
Status History
-- Current status on main table
CREATE TABLE scans (
id INTEGER PRIMARY KEY,
status TEXT NOT NULL DEFAULT 'pending'
);
-- Full status history
CREATE TABLE scan_status_history (
id INTEGER PRIMARY KEY,
scan_id INTEGER NOT NULL REFERENCES scans(id),
status TEXT NOT NULL,
changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
changed_by INTEGER REFERENCES users(id),
note TEXT
);
When to Denormalize
Normalization is ideal for correctness. Denormalize for read performance:
- Store computed counts directly (e.g., scans.port_count)
- Duplicate frequently-joined data to avoid JOINs
- Use materialized summary tables for reporting
Trade-off: faster reads vs. update complexity (must update in multiple places).
Given a table: scan_report(report_id, scan_date, analyst, analyst_email, host, host_os, ports_csv, severity) — identify all 1NF, 2NF, and 3NF violations. Draw the normalized schema (3 or more tables) that fixes them.
What does 1NF require?
What is a transitive dependency?
Design a normalized database for a bug bounty platform: researchers submit vulnerabilities to programs, each vulnerability has a severity and status history, programs belong to companies, researchers earn rewards per vulnerability. Write the full CREATE TABLE SQL.
What type of table resolves a many-to-many relationship?
Add an audit_log table to your bug bounty schema. Write INSERT statements that log: (1) a new vulnerability submission, (2) a status change from 'new' to 'triaged'. Use JSON strings for old_data/new_data columns.
What is soft delete?