Data Modeling and Table Design

In this chapter, we will learn how to design and structure the data in PostgreSQL using tables. Understanding how to model the data properly is crucial for building efficient, maintainable, and scalable databases.

Understanding Tables, Rows, and Columns

Tables

• A table is like a spreadsheet: it holds data in rows and columns.
• Each table represents one entity, such as users, products, or orders.

Columns

• Define attributes or fields for the table.
• Each column has a data type (e.g., text, integer, data).

Rows

• Each row is a record in the table, storing a set of values under the defined columns.

Data Types in PostgreSQL

PostgreSQL has a rich set of built-in data types:

Tip: Use SERIAL or BIGSERIAL for auto-incrementing primary keys.

Creating and Modifying Tables

Basic CREATE TABLE example

CREATE TABLE employees {
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email TEXT UNIQUE,
    position TEXT DEFAULT 'Staff',
    hire_date DATE
};

• SERIAL: Auto-incrementing integer
• PRIMARY KEY: Uniquely identifies each row
• NOT NULL: Field must have a value
• UNIQUE: Not two rows can have the same value
• DEFAULT: Automatically assigns a value if none provided

Modifying Tables

-- Add a new column
ALTER TABLE employees ADD COLUMN salary NUMERIC(10, 2);

-- RENAME a column
ALTER TABLE employees RENAME COLUMN position TO job_title;

-- DELETE a column
ALTER TABLE employees DROP COLUMN salary;

Be cautious when altering tables in production databases.

Keys and Relationships

Primary Keys

• A primary key ensures each row is uniquely identifiable.
• Usually applied to column like `id