Multi-Language Database Design: Schema-Muster für SaaS

-- Basis-Content-Tabelle (sprachunabhängig)
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()
);

-- Übersetzungstabelle (eine Zeile pro 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)
);

-- Abfrage mit Fallback-Kette
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-Spalten: einfach, aber nicht skalierbar
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-Übersetzungen: flexibel, aber schwerer abzufragen
CREATE TABLE pages (
  id TEXT PRIMARY KEY,
  slug TEXT NOT NULL,
  translations JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW()
);

-- Beispiel-JSONB-Inhalt
{
  "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>"
  }
}