Part V: Designing Your Schema

 

Designing a robust database schema is crucial for data integrity, performance, and long‐term maintainability. In this module, we’ll cover:

• Creating and altering tables

• Defining keys and leveraging indexes

By the end, you’ll know how to structure your data model to support reliable applications and fast queries.

Creating and Altering Tables

A well‐designed table lays the foundation of your database. Start by defining clear columns, appropriate data types, and necessary constraints.

1. CREATE TABLE Syntax

sql

CREATE TABLE employees (
  employee_id   SERIAL PRIMARY KEY,
  first_name    VARCHAR(50) NOT NULL,
  last_name     VARCHAR(50) NOT NULL,
  hire_date     DATE NOT NULL,
  email         VARCHAR(100) UNIQUE,
  salary        NUMERIC(10, 2) CHECK (salary > 0)
);

Key takeaways:

• Use SERIAL or IDENTITY for auto-incrementing primary keys.

• Choose string lengths (VARCHAR) based on real data.

• Apply NOT NULL to mandatory columns.

• Enforce business rules with CHECK constraints.

2. ALTER TABLE Examples

As requirements evolve, you’ll need to change schemas without losing data.

• Add a new column:

  sql

  ALTER TABLE employees
  ADD COLUMN department_id INT;
  

• Modify an existing column:

  sql

  ALTER TABLE employees
  ALTER COLUMN email TYPE VARCHAR(150);
  

• Drop an obsolete column:

  sql

  ALTER TABLE employees
  DROP COLUMN salary;
  

Best practices:

• Backup data before altering critical tables.

• Test changes in a development environment first.

• Monitor locks and downtime when applying schema updates in production.

Keys and Indexes

Proper use of keys and indexes ensures data accuracy and query performance.

1. Primary and Unique Keys

• Primary Key: Uniquely identifies each row and enforces NOT NULL.

• Unique Key: Guarantees column uniqueness without being the primary identifier.

sql

ALTER TABLE employees
ADD CONSTRAINT uq_employee_email UNIQUE (email);

Unique constraints prevent duplicate entries and support faster lookups when indexed.

2. Foreign Keys and Referential Integrity

Foreign keys link tables and enforce valid relationships.

sql

ALTER TABLE employees
ADD CONSTRAINT fk_dept
FOREIGN KEY (department_id)
REFERENCES departments (department_id)
ON DELETE SET NULL;

Options like ON DELETE CASCADE or SET NULL control how child rows behave when a parent is removed.

3. Indexes: Types and Usage

Indexes speed up data retrieval but add overhead on writes. Choose wisely:

• Single‐Column Index: Ideal for columns used in WHERE or join conditions.

• Composite Index: Covers multiple columns in specific query orders.

• Partial Index (PostgreSQL): Indexes only a subset of rows.

sql

CREATE INDEX idx_lastname
ON employees (last_name);

4. Performance Considerations

• Analyze query plans to identify missing or unused indexes.

• Avoid over-indexing: too many indexes slow down INSERT, UPDATE, and DELETE.

• Use covering indexes to satisfy queries without accessing the base table.

Conclusion

Designing your schema with clear tables, strong constraints, and strategic indexes lays the groundwork for scalable, maintainable databases. In the next session, we’ll explore advanced SQL constructs—views, stored procedures, and functions—to further encapsulate logic and boost developer productivity.