Data Design, Constraints, and Indexes

Good schemas prevent bugs before the application code runs. Testers should understand constraints because many defects are data integrity defects.

Constraints

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMP NOT NULL
);

Normalization

Normalization reduces duplication by splitting repeated data into related tables. It keeps updates safer and reports more reliable.

Indexes

Indexes speed up lookups but add write cost. Add indexes for columns used often in filters, joins, and ordering.

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

Schema Test Ideas

• Required fields reject missing values.
• Unique fields reject duplicates.
• Foreign keys reject invalid relationships.
• Indexes support critical queries without slow scans.