Practical Data Modeling Patterns: Hierarchies, Tags, and Multilingual Data

Why Learn Data Modeling Patterns?

Certain data structures appear again and again across projects: hierarchical menus, categories, tag systems, multilingual content, and audit trails. Knowing proven patterns for these recurring challenges lets you avoid trial and error and build more efficient, maintainable schemas from the start.

Pattern 1: Hierarchical (Tree) Structures

Categories, menus, org charts, comment threads — any parent-child tree structure.

Option 1: Adjacency List — Simplest

CREATE TABLE categories (
  id        INT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  parent_id INT REFERENCES categories(id)  -- self-reference
);

• Pros: Simple, fast direct parent/child lookup
• Cons: Full tree traversal requires recursive queries

-- PostgreSQL recursive CTE to fetch entire tree
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;

Option 2: Closure Table — Most Flexible

CREATE TABLE category_closure (
  ancestor_id   INT REFERENCES categories(id),
  descendant_id INT REFERENCES categories(id),
  depth         INT,
  PRIMARY KEY (ancestor_id, descendant_id)
);

Stores every ancestor-descendant pair. Efficient for both reads and writes, at the cost of extra storage.

Pattern 2: Tag Systems

Attaching multiple tags to posts, products, etc. — a classic N:M relationship.

Basic Structure

CREATE TABLE tags (
  id   INT PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE post_tags (
  post_id INT REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  INT REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Tag-Based Queries

-- Posts containing ALL of multiple tags (AND logic)
SELECT p.title
FROM posts p
JOIN post_tags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE t.name IN ('database', 'sql')
GROUP BY p.id, p.title
HAVING COUNT(DISTINCT t.name) = 2;

PostgreSQL Array Alternative

CREATE TABLE posts (
  id    INT PRIMARY KEY,
  title VARCHAR(255),
  tags  TEXT[]
);

CREATE INDEX idx_posts_tags ON posts USING GIN(tags);

-- Array containment search
SELECT title FROM posts WHERE tags @> ARRAY['database', 'sql'];

Pattern 3: Multilingual (i18n) Support

Option 1: Translation Table

CREATE TABLE products (
  id    UUID PRIMARY KEY,
  price DECIMAL(10,2)
);

CREATE TABLE product_translations (
  product_id  UUID REFERENCES products(id) ON DELETE CASCADE,
  language    CHAR(2) NOT NULL,
  name        VARCHAR(255) NOT NULL,
  description TEXT,
  PRIMARY KEY (product_id, language)
);

Option 2: JSONB Column (PostgreSQL)

CREATE TABLE products (
  id    UUID PRIMARY KEY,
  price DECIMAL(10,2),
  name  JSONB  -- {"en": "Smartphone", "ko": "스마트폰", "ja": "スマートフォン"}
);

CREATE INDEX idx_products_name ON products USING GIN(name);
SELECT name->>'en' AS name_en FROM products;

Pattern 4: Soft Delete

Mark records as deleted with a timestamp rather than removing them physically.

CREATE TABLE users (
  id         UUID PRIMARY KEY,
  email      VARCHAR(255),
  deleted_at TIMESTAMP  -- NULL = active; non-NULL = deleted
);

SELECT * FROM users WHERE deleted_at IS NULL;

-- Allow email reuse after deletion
CREATE UNIQUE INDEX idx_users_email_active
  ON users(email) WHERE deleted_at IS NULL;

Visualize Patterns with Erdmates

Erdmates helps you design complex modeling patterns visually — self-referencing closure tables, composite-key translation tables, JSONB structures — and share them with your team as intuitive diagrams.

Conclusion

Choosing the right data modeling pattern directly impacts application performance and maintainability. Consider your read/write ratio, query patterns, and data volume when deciding. Document your choices in an ERD so the whole team understands the reasoning and can maintain the system confidently.