This is the full schema reference for the six custom tables created by the WPResidence Translate plugin (internal slug wpestate-translate, text domain wpr-translate). Schemas are defined inline in includes/activator.php inside wpr_translate_activator_create_tables() and installed via dbDelta() on activation. All tables use the active $wpdb->prefix and inherit $wpdb->get_charset_collate(). Context on the runtime that reads these tables is in the multi-language real estate website overview article.
Table 1 — {prefix}wpestate_translation_translations
Central link table. One row per translated element per language. Rows sharing the same trid are language variants of the same logical entity (post, term, or custom element).
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
| translation_id | BIGINT(20) UNSIGNED | No | AUTO_INCREMENT | Primary key. |
| element_type | VARCHAR(45) | No | — | E.g. post_property, post_page, tax_property_category. |
| element_id | BIGINT(20) UNSIGNED | No | — | Points at wp_posts.ID or wp_terms.term_id depending on element_type. |
| trid | BIGINT(20) UNSIGNED | No | — | Translation group ID. Same value across all language variants. |
| language_code | VARCHAR(7) | No | — | Language of this variant (e.g. en, fr, zh-CN). |
| source_language_code | VARCHAR(7) | Yes | NULL | Source language this variant was derived from. |
| post_status | VARCHAR(20) | No | publish | Mirrors WP post status for fast filtering. |
| translator_id | BIGINT(20) UNSIGNED | Yes | NULL | User ID of the translator. |
| original | TINYINT(1) | No | 0 | 1 = this is the source variant in its trid group. |
| needs_update | TINYINT(1) | No | 0 | 1 = source changed since last translation. |
| last_updated | DATETIME | No | CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Auto-maintained by MySQL. |
Indexes: PRIMARY KEY (translation_id), KEY trid (trid), KEY element (element_id, element_type), KEY language (language_code).
Relationships: element_id is a soft FK to wp_posts.ID when element_type starts with post_, or to wp_terms.term_id when it starts with tax_. No DB-level FK constraint is declared.
Table 2 — {prefix}wpestate_translation_strings
Gettext-style string storage for theme and plugin UI strings. One row per (context, name, language_code) triple.
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
| string_id | BIGINT(20) UNSIGNED | No | AUTO_INCREMENT | Primary key. |
| context | VARCHAR(255) | No | — | Domain/context, e.g. wpresidence, wpresidence_admin, a plugin text domain. |
| name | VARCHAR(255) | No | — | String key, typically str_ followed by an md5 of the original text. |
| value | TEXT | No | — | Original source string. |
| language_code | VARCHAR(7) | No | — | Target language of the translation. |
| translation | TEXT | Yes | NULL | Translated value. |
| status | TINYINT(3) UNSIGNED | No | 0 | Workflow status (untranslated / needs review / complete). |
| processed | TINYINT(1) UNSIGNED | No | 0 | Export tracking flag for MO compilation. |
| translator_id | BIGINT(20) UNSIGNED | Yes | NULL | User ID of the last translator. |
| updated_at | DATETIME | No | CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Auto-maintained. |
| md5 | CHAR(32) | Yes | NULL | Optional md5 of value for diffing. |
Indexes: PRIMARY KEY (string_id), UNIQUE KEY uniq_string (context, name, language_code), KEY language (language_code).
The processed column is added lazily by wpr_translate_admin_ensure_processed_column() in includes/admin/string-database.php for installs that predate it.
Table 3 — {prefix}wpestate_translation_slugs
Per-language URL slugs for translated permalinks. One row per (element_id, language_code).
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
| id | BIGINT(20) UNSIGNED | No | AUTO_INCREMENT | Primary key. |
| element_id | BIGINT(20) UNSIGNED | No | — | Soft FK to wp_posts.ID or wp_terms.term_id. |
| element_type | VARCHAR(45) | No | — | Matches the element_type convention used in the translations table. |
| language_code | VARCHAR(7) | No | — | Language this slug belongs to. |
| slug | VARCHAR(255) | No | — | Current translated slug (URL-safe, UTF-8 preserved). |
| old_slugs | TEXT | Yes | NULL | Serialized history for redirect handling. |
| updated_at | DATETIME | No | CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Auto-maintained. |
Indexes: PRIMARY KEY (id), UNIQUE KEY uniq_slug (element_id, language_code), KEY lang (language_code).
Table 4 — {prefix}wpestate_translation_glossary
Fixed term translations consumed by the auto-translate layer. Rows represent “translate this source term as this target term, in this language.”
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
| id | BIGINT(20) UNSIGNED | No | AUTO_INCREMENT | Primary key. |
| term | VARCHAR(255) | No | — | Source term. |
| language_code | VARCHAR(7) | No | — | Target language. |
| translation | VARCHAR(255) | No | — | Forced target translation. |
| context | VARCHAR(255) | Yes | NULL | Optional scoping context. |
| updated_at | DATETIME | No | CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Auto-maintained. |
Indexes: PRIMARY KEY (id), KEY lang (language_code).
Table 5 — {prefix}wpestate_translation_memory
Translation memory for auto-translate provider reuse. Rows cache prior source/target pairs keyed by a similarity hash.
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
| id | BIGINT(20) UNSIGNED | No | AUTO_INCREMENT | Primary key. |
| source_text | LONGTEXT | No | — | Original text submitted to the provider. |
| language_code | VARCHAR(7) | No | — | Target language. |
| translated_text | LONGTEXT | Yes | NULL | Cached translation. |
| similarity_hash | CHAR(32) | Yes | NULL | md5-sized hash used for fast lookup. |
| source_language_code | VARCHAR(7) | Yes | NULL | Detected or provided source language. |
| updated_at | DATETIME | No | CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Auto-maintained. |
Indexes: PRIMARY KEY (id), KEY hash (similarity_hash), KEY lang (language_code).
Table 6 — {prefix}wpestate_translation_languages
Active language records. Mirrors the wpr_translate_languages option for SQL-level joins and ordering.
| Column | Type | Null | Default | Notes |
|---|---|---|---|---|
| code | VARCHAR(7) | No | — | Primary key. Language code (e.g. en, pt-BR). |
| name | VARCHAR(64) | No | — | Display name. UTF-8 preserved. |
| locale | VARCHAR(10) | Yes | NULL | WordPress locale, e.g. en_US. |
| flag | VARCHAR(255) | Yes | NULL | Flag asset key or filename. |
| is_default | TINYINT(1) | No | 0 | Exactly one row should have this set. |
| active | TINYINT(1) | No | 1 | Controls visibility to visitors. |
| display_order | INT | No | 0 | Ascending order used by the switcher. |
Indexes: PRIMARY KEY (code).
Relationships to Core WP Tables
- translations.element_id → wp_posts.ID when element_type is a post_* value.
- translations.element_id → wp_terms.term_id when element_type is a tax_* value.
- slugs.element_id follows the same convention.
- No FK constraints are declared, relationships are maintained in application code via translation-runtime.php, translation-hooks.php, and query-filter.php.
Read-Path Notes
- Language filtering — pre_get_posts / the_posts filters in query-filter.php restrict WP_Query results to the active language_code by joining on the translations table.
- String lookups — wpr_translate_lookup_runtime_translation() in translation-runtime.php uses the UNIQUE KEY uniq_string (context, name, language_code) index. Results are also cached in a per-request static $cache array.
- Slug routing — per-language slugs are resolved against uniq_slug (element_id, language_code) during permalink generation and rewrite matching.
- Trid grouping — counting languages in a translation group scans the KEY trid index.
Non-Latin Safety
All tables are created with $wpdb->get_charset_collate(), typically utf8mb4_unicode_520_ci on modern hosts. Slugs, names, and translated values are stored verbatim. Do not pipe language slugs or string names through sanitize_title() in extension code; use sanitize_key() for language codes only and preserve original casing elsewhere.
Uninstall Behavior
wpr_translate_uninstall_plugin() drops only options, never tables:
delete_option( 'wpr_translate_version' ); delete_option( 'wpr_translate_languages' ); delete_option( 'wpr_translate_language_catalog' ); delete_option( 'wpr_translate_settings' );
For a full wipe, drop the six wpestate_translation_* tables manually or use the plugin’s Delete Translations admin tool in includes/admin/delete-translations.php.
Further Reading
- Translation Linking (trid system) — how trid values are allocated and reused.
- WP_Query Language Filtering — the JOINs produced by query-filter.php.
- Gettext Pipeline & MO Files — how rows in the strings table feed the gettext layer.
- Performance & Caching — request-level caches layered on top of these tables.
For the product context behind the plugin, see the WPResidence multi-language real estate website page.