I have this table called Locales. It's in the Globalization database:
Id Key Description
1 en English
3 ar العربية
4 ru Русский
I want to create a multi-lingual online shop. I have a Products table in the Products database, and for each record of the product, I should specify its language.
I came up with this design:
For clarity, I keep the Key of the Locale in the Products table, instead of its Id:
Id Title Locale
1 Desk en
2 Phone en
3 هاتف ar
For performance reasons, I allow databases to create views from each other. This increases coupling but reduces complexity. So I create a ProductViews view in the Products database, that refers to the Locales in the Globalization database (cross-database view).
create view ProductViews
as
select
Products.*,
Globalization.Locales.Id as LocaleId
from Products
inner join Globalization.Locales
on Products.Locale = Globalization.Locales.`Key`
This way if I want to select the en products, I don't have to query on a string column, and I can query on the LocaleId column from the view, thus in theory I should not have a performance issue.
But is that true? I still need to join over two tables to access LocaleId. And that join is done over two string columns.
Does that view give me performance benefit or am I just a naive developer complicating things?
Update: I'm using MariaDB 11.0.3. I'm using InnoDB.
