WPResidence Translate Database Schema — Tables, Columns & Indexes

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.

Screenshot: ERD diagram of the six wpestate_translation tables with primary keys and foreign-key-style links to wp_posts and wp_terms

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).

Screenshot: SHOW CREATE TABLE output for wp_wpestate_translation_translations showing the four declared keys

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.