Start typing to search this publication.
techcirkle
Open menu

Subscribe to techcirkle

Get new posts delivered straight to your inbox.

The Bilingual Schema - Modelling Names, Addresses and Search for Arabic

Names that do not split, addresses without postcodes, and search that misses records users know exist. Bilingual data modelling, concretely.

techcirkle
6 min read
Cover image for The Bilingual Schema - Modelling Names, Addresses and Search for Arabic
Dubai downtown skyline meeting the desert, representing the UAE technology market
Dubai downtown skyline meeting the desert, representing the UAE technology market

Teams preparing an app for the Gulf usually start with the interface. Mirror the layout, translate the strings, check the fonts render.

That is the visible half. The half that actually causes production incidents sits underneath, in the schema — and unlike CSS, a schema mistake requires a migration on a live table to correct. This post is about four modelling decisions worth getting right before your first user record exists.

1. Names do not decompose

The single most common bilingual data bug is a first_name / last_name schema declared NOT NULL.

Arabic naming conventions do not map onto that structure. A full name commonly chains a given name, a father's name, a grandfather's name, and a family or tribal name, connected by bin/ibn or bint. Some people use a nasab spanning several generations. Some use a nisba indicating origin. There is no reliable rule for which token is the "last name," and different official documents for the same person may render the name differently.

Force this into two fields and you get one of three outcomes, all bad: users truncate their own names to fit, staff enter data inconsistently, or your import script splits on whitespace and produces records that cannot be matched against an identity document later.

A workable model:

  • full_name — the authoritative field, required, stored exactly as the user provided it

  • full_name_normalized — derived, for search (covered below)

  • display_name — optional, what to show in constrained UI

  • given_name, family_name — both optional, populated when known, never required

  • full_name_latin and full_name_arabic — separate optional fields where you genuinely need both scripts, which you do for anything touching official records

The rule: one required authoritative field, structured parts optional. Every downstream system should read full_name and treat the components as hints.

2. Addresses without postcodes

The second structural surprise. UAE addressing does not work like the systems most address schemas were designed against. Postcodes are not meaningfully used for delivery. Street names exist but are often not how people navigate. Buildings are identified by name. Directions are given by landmark.

An address schema with street_line_1, city, state, postal_code NOT NULL will be filled with junk within a week — 00000 in the postcode field, landmark descriptions crammed into street lines.

Model it as:

  • emirate — a controlled enumeration, not free text

  • area / community — the meaningful locality unit

  • building_name, unit_number

  • street — optional

  • landmark — a real, first-class field, because couriers actually use it

  • makani or equivalent geolocation code — optional but valuable where available

  • latitude / longitude — the field couriers really rely on

  • postal_code — optional, and never required

Store a country_code and make the whole structure versioned per country if you plan to expand to Saudi Arabia, which most UAE products eventually do. Retrofitting a second address shape is far worse than designing for two from the start.

3. Search normalization as a stored column

This is the one that ships broken silently, because it raises no errors. Search just returns fewer results than it should, and nobody reports a bug because nobody knows what should have matched.

Arabic has several sources of variance users do not perceive as variance:

  • Alef forms — أ, إ, آ, ا are typed interchangeably

  • Ta marbuta / ha — ة and ه substituted at word endings

  • Alef maqsura / ya — ى and ي likewise

  • Diacritics (tashkeel) — U+064B–U+0652, usually absent, occasionally present

  • Tatweel — the decorative kashida ـ, semantically meaningless

  • Presentation forms — text pasted from PDFs or legacy systems arrives as compatibility characters

The normalization pipeline: apply NFKC, strip diacritics and tatweel, unify alef variants to bare ا, map ة to ه and ى to ي, collapse whitespace, lowercase any Latin content.

Store the output in a dedicated indexed column, written at insert and update time via the application layer or a generated column. Normalize the incoming query with the identical function before comparison — the two must never drift, so keep them in one shared module with a test matrix covering each variance class.

Note the shape of adding this later: a new column, a backfill across every existing row, and a rewrite of every query touching that table. On a hot table with production traffic, that is a genuinely disruptive afternoon. In the initial migration it is four lines.

Also: sorting needs a locale-aware collation, not byte order, or alphabetical lists are arbitrary to the people reading them.

4. Content in two scripts

For user-generated or editorial content that exists in both languages, resist the temptation to add title_ar and title_en columns alongside every field. It looks simple with two languages and becomes unmanageable at three.

Prefer a translations table keyed by (entity_id, locale, field), or a JSONB column holding a locale map where your database supports indexed access into it. Either way, define explicitly:

  • The source of truth locale — which version is authoritative when they disagree

  • Fallback behaviour — what renders when a translation is missing, and whether partial records are publishable

  • Independent workflow state — the Arabic version may be in review while the English version is live, and the schema must express that rather than assuming lockstep

That third point is where most content models break in practice. Teams assume both language versions move together, then discover the editorial reality does not work that way.

Where AI genuinely helps

Concretely, because this is worth separating from marketing claims.

Useful: generating the normalization function plus an exhaustive test matrix across variance classes; producing transliteration candidates between Arabic and Latin script for search augmentation; drafting migrations; extracting structured address components from free-text legacy data, which is otherwise a miserable manual cleanup; and reviewing an existing schema against a checklist like this one.

That last one is worth doing today if you already have a bilingual product in production — the audit is cheap and frequently surfaces a NOT NULL constraint nobody remembers adding.

Not useful: deciding whether the Arabic or English record is authoritative, resolving how your organisation defines a customer, or determining which regulator governs your data. Those stay human, and on regulated Gulf projects they dominate the effort.

The larger change is what became buildable at all. Mixed-language semantic search, bilingual document extraction, and Arabic-and-English support triage were six-figure custom projects three years ago and now assemble in weeks. That shifts the interesting engineering question from "can we handle Arabic" to "what should we build now that we can."

The short version

If you are starting a bilingual build:

  1. One required full_name; structured name parts optional forever.

  2. Address model with landmark and coordinates as first-class fields, postal_code optional.

  3. Normalized search column in the initial migration, with a shared normalize function used by both writes and queries.

  4. Translations in a keyed table with explicit fallback and independent workflow state.

Four decisions. All trivial before launch. Two of them genuinely painful afterwards.

The full country-level guide — vendor selection, cost bands in AED, data residency, and contract terms — is here: App Development Companies in UAE: The 2026 Buyer's Guide.

Product designers working through mobile app interface wireframes
Product designers working through mobile app interface wireframes

Frequently Asked Questions

Why can't Arabic names use first and last name fields?

Because Arabic naming chains given, paternal, grandpaternal, and family or tribal elements with no reliable rule for which is the surname, and official documents for the same person may render it differently. Require one authoritative full name and keep structured parts optional.

What should an Arabic search normalization function do?

Apply NFKC, strip diacritics and tatweel, unify alef variants, map ta marbuta to ha and alef maqsura to ya, collapse whitespace, and lowercase Latin content. Store the result in an indexed column and normalize queries with the identical shared function.

How should UAE addresses be modelled?

With emirate as a controlled enumeration, area and building name as primary identifiers, landmark as a real field because couriers use it, coordinates stored explicitly, and postal code optional. Requiring a postcode guarantees junk data within a week.

Separate columns or a translations table for bilingual content?

A translations table keyed by entity, locale, and field, or an indexed JSONB locale map. Parallel _ar and _en columns look simple at two languages and become unmanageable beyond that. Define source-of-truth locale, fallback behaviour, and independent workflow state explicitly.

Can I add search normalization after launch?

Yes, but it means a new column, a full backfill, and rewriting every query on that table — disruptive on a hot table under production traffic. In the initial migration it is a few lines. This is the clearest example of a cheap-now, expensive-later schema decision.

Subscribe to techcirkle