Строку в качестве внешнего ключа ссылка на SQL - i18l поддержка предустановленные таблицы данных

У меня есть ответ на мой вопрос еще до его публикации, но я не уверен, что это лучшее решение. Вот проблема бизнеса:

Для поддержки i18l фиксированных таблиц данных. Приложение полностью знает язык, но часть текста, отображаемого на страницах, поступает из базы данных. Вот пример:

Таблица-это "период": 

INSERT INTO PERIOD (lang, ref, desc) VALUES
('en', 'HOUR', 'Hourly'),
('en', 'WEEK', 'Weekly'),
('en', 'MONTH', 'Monthly'),
('de', 'HOUR', 'Stündlich'),
('de', 'WEEK', 'Wöchentlich'),
('de', 'MONTH', 'Monatlich');

Причина, по которой я не использую автоматически сгенерированный идентификатор, заключается в том, что когда кто-то использует сайт на немецком языке, и выберите "Wochentlich" для еженедельное появление, мы хотим, чтобы, если языковой стандарт изменен на английский, мы хотим, чтобы отображался "Weekly". Если бы мы использовали ID, то это было бы зафиксировано на самом выбранном элементе, и мы бы закончили фразами типа "у этого клиента есть постоянный заказ wochtenlich" - немного глупо, как вы можете видеть. Поэтому мы решили эту проблему, сохранив " ref "в качестве " внешнего ключа". Каждый запрос для получения таких данных использует параметр' ref 'и параметр' lang'для получения правильного элемента на текущем языке.

I знайте недостатки, что мы не получаем правильную связь в базе данных, так как мы не можем указать VARCHAR в качестве внешнего ключа. (Мы используем MySQL 5.x)

Может ли кто-нибудь предложить более правильный способ решения этой проблемы?

Большое спасибо.

1 2
sql mysql foreign-keys

1 ответ:

Я бы разработал приложение для хранения одной строки для каждого типа периода и использовал дополнительные столбцы для каждого из переведенных терминов.

INSERT INTO PERIOD (ref, desc_en, desc_de) VALUES
('HOUR', 'Hourly', 'Stündlich'),
('WEEK', 'Weekly', 'Wöchentlich'),
('MONTH', 'Monthly', 'Monatlich');

Вы можете получить список всех описаний периодов для данной локали: 

SELECT desc_de FROM PERIOD;

Вы можете получить описание в данной локали для данного периода: 

SELECT desc_de FROM PERIOD WHERE ref = 'WEEK';

Вы можете даже учитывать случаи, когда вы не сделали перевод: 

SELECT COALESCE(desc_de, desc_en) AS desc FROM PERIOD WHERE ref = 'WEEK';

Вы можете получить описание из заданной пользователем локали (будьте осторожны, чтобы включить ее в белый список выбор, чтобы избежать SQL-инъекции). Например, в PHP: 

$locales = array('en'=>1, 'de'=>1);
$locale = array_key_exists($_GET['locale'], $locales) ? $_GET['locale'] : 'en';
$sql = "SELECT desc_{$locale} FROM PERIOD WHERE ref = 'WEEK'";
PS: Для чего это стоит, вы можете использовать varchar в качестве первичного ключа или внешнего ключа в любой версии MYSQL (пока вы используете InnoDB). 
CREATE TABLE FOO (id VARCHAR(10) PRIMARY KEY);
CREATE TABLE BAR (foo_id VARCHAR(10), FOREIGN KEY (foo_id) REFERENCES FOO(id));
1