Data Types, Tables, and Schema Design

Choose correct data types, define constraints, use identity columns, and build maintainable schemas.

Common PostgreSQL Data Types

Choose types based on meaning and required operations. Use INTEGER or BIGINT for whole numbers, NUMERIC(precision, scale) for exact decimal values such as money, REAL or DOUBLE PRECISION for approximate floating-point values, TEXT or VARCHAR for strings, BOOLEAN for true/false values, and DATE, TIME, or TIMESTAMPTZ for temporal values.

For timestamps representing real-world moments, TIMESTAMPTZ is usually a strong default because PostgreSQL stores the instant and displays it in the session time zone. It does not preserve the original named time zone supplied by the client. Avoid storing formatted numbers or dates as strings when they need numeric or temporal behavior.

Table with suitable types

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  in_stock BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

💡 The schema uses an identity column for generated IDs, exact decimal precision for price, a boolean for stock state, and a timestamp with time zone for creation time.

Key Takeaways

Choose types by semantics, not convenience alone.
NUMERIC is appropriate for exact decimal quantities.
TIMESTAMPTZ represents an instant with time-zone-aware input/output behavior.

Knowledge Check

Which type is generally appropriate for exact decimal financial values?

Constraints: Primary Key, Foreign Key, Unique, Check, Not Null

Constraints enforce data invariants regardless of which application writes the rows. PRIMARY KEY enforces uniqueness and non-nullness for the key; FOREIGN KEY enforces references; UNIQUE prevents duplicate values; CHECK enforces a boolean condition; NOT NULL requires a value. Defaults provide a value when an insert omits a column, but a default is not a substitute for a constraint.

Put critical rules in the database when possible. Application validation is still valuable for user-friendly error messages, but database constraints protect against bugs, scripts, concurrent requests, and other clients.

Use multiple constraints

CREATE TABLE memberships (
  user_id BIGINT NOT NULL,
  team_id BIGINT NOT NULL,
  role TEXT NOT NULL CHECK (role IN ('member', 'admin')),
  joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, team_id)
);

💡 The composite primary key prevents a user from being added to the same team twice. The CHECK constraint limits role values to the supported set.

Key Takeaways

Database constraints protect integrity across all clients.
A composite primary key can represent a many-to-many relationship.
Defaults do not replace NOT NULL or CHECK constraints.

Knowledge Check

Which constraint can restrict a role column to a known set of allowed values?

Normalization and Relationship Design

Normalization organizes facts to reduce duplication and update anomalies. A one-to-many relationship often stores a foreign key on the many side. A many-to-many relationship typically uses a junction table whose composite primary key contains the two foreign keys. One-to-one relationships can use a unique foreign key, depending on the domain.

Normalization is not an absolute ban on duplication. Carefully chosen denormalization can improve read performance, but it creates synchronization and correctness responsibilities. Start with a clear normalized model, measure real query patterns, and denormalize only with an explicit strategy.

Many-to-many junction table

CREATE TABLE course_enrollments (
  course_id BIGINT NOT NULL REFERENCES courses(id),
  student_id BIGINT NOT NULL REFERENCES students(id),
  enrolled_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (course_id, student_id)
);

💡 The junction table stores each course-student relationship once and can carry relationship attributes such as enrollment time.

Key Takeaways

Normalize to reduce redundant facts and update anomalies.
Use junction tables for many-to-many relationships.
Denormalize only when measured needs justify the added consistency burden.

Knowledge Check

How is a typical many-to-many relationship represented relationally?

Common PostgreSQL Data Types

Constraints: Primary Key, Foreign Key, Unique, Check, Not Null

Normalization and Relationship Design

STAY UPDATED WITH BYTES!