0Pricing
SQL Academy · Lesson

Arrays vs Normalized Tables

When arrays are the right choice.

Arrays vs Normalized Tables is a free SQL Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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');

The Array Approach

PostgreSQL's TEXT[] (or any other type followed by []) lets you store multiple values directly inside a single column. No extra table is needed.

The same phone-number data can be stored in one compact row per user.

CREATE TABLE users_with_phones (
  id     SERIAL PRIMARY KEY,
  name   TEXT    NOT NULL,
  phones TEXT[]
);

INSERT INTO users_with_phones (name, phones) VALUES
  ('Alice', ARRAY['+1-555-0101', '+1-555-0102']),
  ('Bob',   ARRAY['+1-555-0200']);

Querying Arrays Is Easy

Searching inside an array column is straightforward with the ANY operator or the @> (contains) operator. You can find all users who have a specific phone number with a simple WHERE clause.

-- Find users who have a specific phone number
SELECT name
FROM users_with_phones
WHERE '+1-555-0101' = ANY(phones);

-- Or using the array-contains operator
SELECT name
FROM users_with_phones
WHERE phones @> ARRAY['+1-555-0101'];

When Arrays Win: Simple Lookups

Arrays are a strong choice when:

  • The list of values is read together as a unit (tags, labels, categories)
  • You never need to join on individual elements
  • The list has a natural upper bound and is rarely updated in parts

A classic example is storing tags on a blog post. You always fetch all tags at once and rarely query posts by a single tag in a complex join.

CREATE TABLE posts (
  id         SERIAL PRIMARY KEY,
  title      TEXT    NOT NULL,
  tags       TEXT[]
);

INSERT INTO posts (title, tags) VALUES
  ('Intro to SQL',      ARRAY['sql', 'beginner', 'database']),
  ('Advanced Indexes',  ARRAY['sql', 'performance', 'indexes']),
  ('NoSQL Overview',    ARRAY['nosql', 'beginner']);

-- Get all posts tagged 'beginner'
SELECT title FROM posts
WHERE 'beginner' = ANY(tags);

When Normalized Tables Win: Relationships

Normalized tables are the better choice when:

  • Individual values need their own attributes (e.g., a phone number has a type: home/work)
  • You need to join on individual values
  • Values change independently and frequently
  • You need referential integrity via foreign keys
-- Phone numbers need a 'type' attribute — array can't do this cleanly
CREATE TABLE user_phones (
  id      SERIAL PRIMARY KEY,
  user_id INT REFERENCES users(id),
  phone   TEXT NOT NULL,
  type    TEXT CHECK (type IN ('home', 'work', 'mobile'))
);

INSERT INTO user_phones (user_id, phone, type) VALUES
  (1, '+1-555-0101', 'home'),
  (1, '+1-555-0102', 'work');

The Indexing Difference

With a normalized table, you can add a standard B-tree index on the foreign key or value column. With arrays, you need a GIN index (Generalized Inverted Index) to enable fast searches inside the array.

GIN indexes work well but are larger and slower to update than B-tree indexes.

-- Index for fast array element lookups
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);

-- Now this query uses the index efficiently
EXPLAIN SELECT title FROM posts
WHERE tags @> ARRAY['sql'];

Aggregating Across Rows: Normalized Wins

When you need to count, group, or aggregate on individual values, normalized tables are far more natural. Aggregating inside arrays requires unnest(), which expands the array into rows first — essentially recreating the normalized structure at query time.

-- Count posts per tag (array approach — needs unnest)
SELECT tag, COUNT(*) AS post_count
FROM posts, unnest(tags) AS tag
GROUP BY tag
ORDER BY post_count DESC;

-- With a normalized post_tags table this would be simpler:
-- SELECT tag, COUNT(*) FROM post_tags GROUP BY tag;

Modifying Array Elements

Updating or deleting a single element inside an array requires awkward syntax — you must replace the whole array or use array_remove(). In a normalized table, you simply DELETE or UPDATE the specific row.

-- Remove a single tag from an array column
UPDATE posts
SET tags = array_remove(tags, 'beginner')
WHERE id = 1;

-- Append a new tag
UPDATE posts
SET tags = array_append(tags, 'tutorial')
WHERE id = 1;

SELECT title, tags FROM posts WHERE id = 1;

Enforcing Valid Values

In a normalized table, you can use a foreign key to enforce that every value comes from a known set. Arrays cannot reference another table — they have no foreign key support.

If you need guaranteed referential integrity for each element, a child table is the only option.

-- Normalized: only valid category IDs allowed (FK enforced)
CREATE TABLE categories (
  id   SERIAL PRIMARY KEY,
  name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_categories (
  post_id     INT REFERENCES posts(id),
  category_id INT REFERENCES categories(id),
  PRIMARY KEY (post_id, category_id)
);

-- Array: no constraint possible — any text value is accepted
-- UPDATE posts SET tags = ARRAY['totally_invalid_tag'] WHERE id = 1;

A Practical Decision Guide

Use an array when the data is a simple flat list, always read as a unit, has no extra attributes per element, and referential integrity is not required (e.g., tags, labels, search keywords).

Use a normalized child table when each element has its own attributes, you join or aggregate on individual values, you need foreign keys, or individual elements are updated or deleted frequently.

-- Summary example: tags as array (good fit)
SELECT title, tags
FROM posts
WHERE tags @> ARRAY['sql']
ORDER BY title;

-- Unnest when you need row-level processing
SELECT title, unnest(tags) AS tag
FROM posts
ORDER BY title, tag;

Quick Check

Which scenario is the best fit for storing data as a PostgreSQL array rather than a normalized child table?

Lesson Recap

In this lesson you learned the key trade-offs between arrays and normalized tables in PostgreSQL.

  • Arrays are compact and convenient for flat, unit-read lists like tags — but they lack foreign keys, make per-element updates awkward, and require GIN indexes for fast searches.
  • Normalized tables support per-element attributes, foreign key integrity, efficient aggregation, and simple row-level updates — at the cost of an extra join.
  • The right choice depends on how you query, update, and relate the data — not just how you store it.

Frequently asked questions

Is the “Arrays vs Normalized Tables” lesson free?

Yes — the full text of “Arrays vs Normalized Tables” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Arrays vs Normalized Tables”?

When arrays are the right choice. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Arrays vs Normalized Tables” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Array Columns Basics
  2. Searching Inside Arrays
  3. UNNEST and Aggregating
  4. Arrays vs Normalized Tables
← Back to SQL Academy