Designing a Database Schema for a Multilingual App (Arabic + English)

Last spring, I spent four hours staring at a MySQL query that kept returning empty results for Arabic menu items in a Laravel app. The client — a luxury hotel group in Abu Dhabi — wanted both Arabic and English support for their dining service. My first schema design was a hot mess: duplicate product entries for each language, half-baked JSON hacks, and a WHERE clause that treated Arabic like a third-class citizen. By day three, I’d learned exactly how many ways MySQL collations can bite you in the ass.

Why the Default Approach Fails: Flat Multilingual Tables

Most tutorials suggest adding language columns directly to your tables. Like this:

CREATE TABLE products (  
  id INT,  
  name_en VARCHAR(255),  
  name_ar VARCHAR(255),  
  description_en TEXT,  
  description_ar TEXT  
);  

This works until you hit 100 products, have three languages, or need dynamic translation management. I did this on a logistics app in Dubai once. It looked simple, but every query became a nightmare. Imagine filtering 10k rows where Arabic name contains ياسمين — without accidentally selecting Jasmine in English.

Here’s the real kicker: Arabic text sorting isn’t alphabetical. ح comes before ه, which comes before ع — none of the Latin rules apply. My first WHERE clauses returned 90% irrelevant results because I forgot Arabic has its own collation sequence.

The Better Way: Normalized Translation Tables

After burning myself on flat tables, I started using a translation table for each translatable entity. For products, it looked like:

CREATE TABLE products (  
  id INT PRIMARY KEY  
);  

CREATE TABLE product_translations (  
  product_id INT,  
  lang_code CHAR(2),  
  name VARCHAR(255),  
  description TEXT,  
  PRIMARY KEY (product_id, lang_code)  
);  

This pattern saved me on Tawasul Limo, the luxury limo booking platform. We could add support for Urdu without rewriting 80% of the models.

Benefits:

• •Scalability: Add languages without schema changes
• •Validation: Enforce required languages at the database level
• •Performance: Index lang_code + product_id combos for fast lookups

But you have to be careful with joins. Joining five translation tables in a single query? That’ll freeze your dev DB. More on optimization later.

Handling Arabic-Specific Gotchas

Right-to-Left Text Storage

MySQL doesn’t care about text direction — your application does. But don’t overengineer it. I wasted six hours once trying to store directionality in the DB, only to realize the frontend framework (React Native) handled it via HTML dir attributes.

Arabic Alphabet Variations

The letter ي isn’t just one character — there’s ي (064a) and ى (0649) and a half-dozen ligatures. Use utf8mb4_unicode_ci collation to normalize comparisons.

Sorting in Arabic

Use MySQL’s CONVERT() function with specific collations:

SELECT * FROM products  
JOIN product_translations pt ON pt.product_id = products.id  
ORDER BY CONVERT(pt.name USING utf8mb4) COLLATE utf8mb4_0900_ci;  

Yes, it’s verbose. Yes, it works when Arabic users complain your menu is sorted alphabetically but not correctly.

Practical Implementation in Laravel

Tawasul Limo’s search endpoint needed to filter Arabic names across 15,000 listings. Here’s how I structured it:

1. Eloquent Models:

class Product extends Model {  
  public function translations() {  
    return $this->hasMany(ProductTranslation::class);  
  }  
}  

class ProductTranslation extends Model {  
  protected $primaryKey = ['product_id', 'lang_code'];  
}  

1. Querying:

Product::whereHas('translations', function($q) {  
  $q->where('name', 'like', '%مرحبا%')  
    ->where('lang_code', App::getLocale());  
});  

1. API Response:

{  
  "id": 123,  
  "name": "مرحبا",  
  "description": "This is a test product",  
  "ar": {  
    "name": "مرحبا",  
    "description": "هذا المنتج اختباري"  
  }  
}  

The API client decides which language to use. This made integrating with React Native’s i18n library easier than juggling JSON on the server.

Performance Tweaks: How I Saved 200ms

Early versions of the Reach Home Properties app (a UAE real estate platform) had translation joins that made search API calls hit 500ms+. After adding Redis caching, I cut that in half.

But here’s the trick: Cache translated responses per language code. Don’t just cache /products — cache /products?lang=ar and /products?lang=en separately. Arabic content is often longer and includes diacritics, which affects Redis byte usage.

Mistakes I Made (So You Don’t Have To)

1. Forgetting HTML Entities for Arabic: One client’s admin panel allowed HTML in Arabic descriptions. We encoded them as HTML entities instead of using CDATA sections. The browser rendered ي as &yacut; — hilarious until 500 user complaints came in.

1. Assuming All Strings Need Translation: Product SKUs in Arabic-only apps sometimes use English. I added a force_english flag to the translation table for things like model numbers.

1. Underestimating Text Length: Arabic words are often longer than English equivalents. VARCHAR(255) fields? Not enough. I switched to TEXT columns for names during the Greeny Corner project — the plant app we built in 2024.

Frequently Asked Questions

How to store languages without duplicating tables?

Use a shared translation table pattern. Store language-specific data (names, descriptions) in a separate table linked by foreign key and language code. Works in Postgres too, just adjust the query syntax.

Should I store Arabic text in JSON columns instead of tables?

Only for static content. I tried it once — fast to build, slower to query, impossible to index for search. Stick to proper relational design for dynamic content.

How to handle Arabic diacritics in search?

Normalize input with PHP’s Normalizer::normalize() before inserting into the DB. In MySQL, use the utf8mb4 collation with diacritic-insensitive comparison rules.

Does Laravel’s `Lang::get()` work for database content?

Not out of the box. Laravel uses key-value files for translations. For database content, build a service wrapper that fetches the current language's fields.

I’ve been designing multilingual schemas since 2017 — longer than I’ve owned my current laptop. If your UAE/GCC app needs support for Arabic and English, and you want it done without the JSON mess I suffered through, get in touch. I’ll help you avoid the pitfalls I spent 7 years falling into.