SQL Academy · Lesson

Arrays vs Normalized Tables

When arrays are the right choice.

Two Ways to Store Multiple Values

When a single row needs to hold multiple related values, PostgreSQL gives you two main approaches: store them as an array column in the same row, or create a separate child table where each value gets its own row.

Understanding when to use each approach is a key skill for designing efficient, maintainable databases.

The Normalized Approach

In a fully normalized schema, each piece of data lives in its own row. If a user can have multiple phone numbers, you create a user_phones table with a foreign key back to users.

This is the classic relational model and is the default choice in most situations.

CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE user_phones (
  id      SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  phone   TEXT NOT NULL
);

INSERT INTO users (name) VALUES ('Alice'), ('Bob');

INSERT INTO user_phones (user_id, phone) VALUES
  (1, '+1-555-0101'),
  (1, '+1-555-0102'),
  (2, '+1-555-0200');

All lessons in this course

← Back to SQL Academy