Databases, Schemas, Tables, Columns, Data Types, and Database Design Basics
Build a strong PostgreSQL foundation through schema organization, good naming, appropriate data types, and practical table design.
Inside this chapter
- Database and Schema Structure
- Creating a Starter Table
- Common PostgreSQL Data Types
- Schema Design Example
Series navigation
Study the chapters in sequence for the clearest path from beginner PostgreSQL concepts to advanced query design and production operations. Use the navigation at the bottom of every page to move chapter by chapter.
Database and Schema Structure
In PostgreSQL, a database is a top-level container. Inside it, schemas help organize tables, views, functions, and other objects. Beginners often ignore schemas at first, but they matter in larger systems because they help structure domains, separate ownership, and avoid naming collisions.
Creating a Starter Table
CREATE TABLE customers (
customer_id BIGSERIAL PRIMARY KEY,
full_name VARCHAR(120) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);This example shows a PostgreSQL-style auto-incrementing key with BIGSERIAL, not-null constraints, uniqueness, and timestamps. Students should pay attention to the fact that table design is where data quality begins.
Common PostgreSQL Data Types
| Type | Use Case | Important Note |
|---|---|---|
INTEGER / BIGINT | Identifiers and numeric counters | Choose a type that supports expected growth |
VARCHAR / TEXT | Names, labels, descriptions | Use constraints intentionally, not randomly |
NUMERIC | Money and precise values | Prefer over floating point for finance |
DATE, TIMESTAMP, TIMESTAMPTZ | Time-aware records | Understand timezone requirements carefully |
BOOLEAN | True/false state | Clearer than many custom status flags |
JSONB | Semi-structured data | Useful, but not a replacement for good relational design |
Schema Design Example
CREATE TABLE products (
product_id BIGSERIAL PRIMARY KEY,
sku VARCHAR(40) NOT NULL UNIQUE,
product_name VARCHAR(150) NOT NULL,
unit_price NUMERIC(10,2) NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE orders (
order_id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
order_status VARCHAR(20) NOT NULL,
order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);A good schema reflects real business entities clearly. That clarity makes querying, validation, reporting, and maintenance much easier later.