Multi-Language Database Design for SaaS

-- Base content table (language-independent)
CREATE TABLE blog_posts (
  id TEXT PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  author_id TEXT NOT NULL,
  published_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Translation table (one row per locale)
CREATE TABLE blog_post_translations (
  blog_post_id TEXT NOT NULL REFERENCES blog_posts(id),
  locale TEXT NOT NULL,
  title TEXT NOT NULL,
  excerpt TEXT,
  body TEXT NOT NULL,
  meta_description TEXT,
  is_auto_translated BOOLEAN DEFAULT false,
  updated_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (blog_post_id, locale)
);

-- Query with fallback chain
SELECT 
  COALESCE(t.title, f.title) as title,
  COALESCE(t.body, f.body) as body
FROM blog_posts p
LEFT JOIN blog_post_translations t 
  ON t.blog_post_id = p.id AND t.locale = 'de'
LEFT JOIN blog_post_translations f 
  ON f.blog_post_id = p.id AND f.locale = 'en'
WHERE p.slug = 'getting-started';

-- Locale columns: simple but not scalable
CREATE TABLE products (
  id TEXT PRIMARY KEY,
  name_en TEXT NOT NULL,
  name_zh TEXT,
  name_de TEXT,
  name_ja TEXT,
  description_en TEXT NOT NULL,
  description_zh TEXT,
  description_de TEXT,
  description_ja TEXT,
  price DECIMAL(10, 2) NOT NULL
);

-- JSONB translations: flexible but harder to query
CREATE TABLE pages (
  id TEXT PRIMARY KEY,
  slug TEXT NOT NULL,
  translations JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW()
);

-- Example JSONB content
{
  "en": {
    "title": "About Us",
    "body": "<h1>Our Story</h1><p>...</p>"
  },
  "de": {
    "title": "Über Uns",
    "body": "<h1>Unsere Geschichte</h1><p>...</p>"
  },
  "zh": {
    "title": "关于我们",
    "body": "<h1>我们的故事</h1><p>...</p>"
  }
}