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

  • You should tag your database system, version, and edition, as it's not really possible to answer a performance question without that information. (Seems like you might be using SQL Server?)
    – J.D.
    Commented Aug 29, 2023 at 17:07
  • 1
    @J.D. updated the answer. Thanks for the note. Commented Aug 30, 2023 at 6:28
  • How about just trying this out. Fill your product table with dummy products - say 1 mio of them and create 100 locales. Then run your queries and see if the response time matches your business requirements. With the proper indexes in place any relational database will handle that without any problem. Partitioning the product table (and its indexes) based on locale may be something to consider too. Commented Aug 30, 2023 at 7:39
  • IMO the real things to worry about in your case is scalability and the work done in the actual application, and how it connects to the database, i.e. how you manage the connection pool. So you should also test your database design under load with simulated concurrent users and think times based on your business needs. Commented Aug 30, 2023 at 7:43


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.