多语言数据库设计问询：可翻译字段存储方案选型

Hey there! Let's work through your database schema challenge for handling translatable item descriptions. From what you shared, you've got an Items table with fields like description_60, description_180 (where the suffix indicates character limits) plus related fields like apiSourceName, and you're weighing approaches for managing translations. Let's break down common patterns, their pros/cons, and which might fit your needs best.

Option 1: Combined Translation Table (Per Item + Language) #

This is the approach you hinted at with a descriptions_translations table, where each row holds all translated description variants for a single item and language. A typical schema would look like this:

CREATE TABLE descriptions_translations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    item_id INT NOT NULL,
    language_code VARCHAR(5) NOT NULL, -- e.g., 'en-US', 'fr-FR'
    description_60 VARCHAR(60) NOT NULL,
    description_180 VARCHAR(180) NOT NULL,
    description_300 VARCHAR(300), -- Optional, if you need it later
    FOREIGN KEY (item_id) REFERENCES Items(id),
    UNIQUE KEY unique_item_lang_pair (item_id, language_code)
);

Note: Keep apiSourceName in the original Items table unless it also requires translation—no need to duplicate non-translatable data!

Pros of This Approach #

• Simpler Reads: Fetch all description variants for a specific item and language with a single JOIN, no extra pivoting needed.
• Clear Structure: All translations for an item are grouped together, making the schema easy to understand for new developers.
• Performance: Fewer rows to scan compared to normalized approaches, which helps with read speed for common queries.

Cons #

• Less Flexible: If you add more description types (e.g., description_500) later, you'll need to alter the table to add a new column.
• Potential NULLs: If some description types are optional for certain items/languages, you'll end up with NULL values (though this is usually manageable with proper constraints).

Option 2: Normalized Translation Table (Per Field + Language) #

A more flexible alternative is to store each translated field as a separate row. This works well if you anticipate adding new translatable fields over time. Here's what that schema might look like:

CREATE TABLE item_translations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    item_id INT NOT NULL,
    language_code VARCHAR(5) NOT NULL,
    field_identifier VARCHAR(50) NOT NULL, -- e.g., 'description_60', 'description_180'
    translation_text TEXT NOT NULL,
    FOREIGN KEY (item_id) REFERENCES Items(id),
    UNIQUE KEY unique_item_lang_field (item_id, language_code, field_identifier)
);

Pros of This Approach #

• Future-Proof: Add new translatable fields (like a short_title or description_500) without modifying the table schema—just insert new rows with the new field_identifier.
• No NULLs: Each row represents a single translated value, so you avoid empty columns for optional fields.
• Narrow Table: The schema stays lean even as you add more translatable elements.

Cons #

• Complexer Reads: To fetch all descriptions for an item, you'll need to either retrieve multiple rows or use a pivot query to combine them into a single result set.
• Enforcement Needed: You'll need to add application-level checks or database constraints to ensure field_identifier values are valid (e.g., prevent typos like desc_60 instead of description_60).

Which Should You Choose? #

• Go with Option 1 if your set of description types is fixed (you know you'll only have 60, 180, 300, etc.) and you prioritize simplicity and read performance. It's the most straightforward approach for static field sets.
• Go with Option 2 if you expect to add new translatable fields over time, or if you want maximum flexibility to handle any future translation needs without schema changes.

Bonus Best Practices #

• Use standard language_code values (ISO 639-1 for language, plus region if needed, like en-US or es-ES) to ensure consistency across your app.
• Add unique constraints to prevent duplicate translations for the same item, language, and field/description type.
• Consider storing default-language descriptions (e.g., English) directly in the Items table. This lets you avoid joining the translation table for the default language, boosting query performance.

内容的提问来源于stack exchange，提问作者thitami