Supporting Multiple Languages
The actual selection of which language is used will be solved at the application layer but the storing of data currently isn't and I think that is okay. I looked into resource files and the data we would be storing would have a similar format were you classify data with an identifier (Page.LabelName) and then use those classifiers to figure out which content needs to be inserted. Our users need to be able to edit some of this content and in some cases the wording needs to be very precise...eliminating any 3rd party api calls and (maybe?) makes it a bit of a struggle to use resource files.
A solution to address J.B. concerns about the duplication of data is that you could theoretically tie this all into one table but then you lose the link between the data and content. This may not be a concern for some tables but maybe it's very important (TBD on our end) I could see a mix of where it is an isn't.
Another solution for when you care about the data link. You could create a ExampleDisplayTable that contains all of your ui content and ties back to the ExampleTable. This results in a lot more tables and spreads out where content is located, two cons just depends on the sizing of your system.
I really keep coming back to the idea of how a CMS system works and it's almost what we are recreating.
We are adding support for multiple languages to our application. Our system in place currently has content in two main areas. There are a lot of columns on tables with text and a table that contains data not tied to any other particular table. Shown below is the DisplayPropertyTable and it holds purely UI values, think like a page header. The ExampleTable has a column called DisplayData that would also be shown on the UI. We have many of these tables and they could include many columns for display.
I would like to combine these two systems. Here is what I have thought of so far.
What drawbacks are there in this design? The new DisplayValue Table hold the information need to actually display the value. The DisplayProperty table can now be linked back to any table and still hold those pure UI property names while not linking any display columns with a value directly. I'll be solving which DisplayValue is used at the application layer.
Mystic last edited by
Agreed with David Browne on this one. You should evaluate if the problem you're trying to solve makes sense to solve in the database layer or would be better handled in the application / API layer instead.
Off the top of my head the following few things are potential issues:
Redundant data being stored in the database bloating your tables. If you wanted to support only five languages, you're now going to be storing five times the amount of data, almost immediately.
Data Management Issues: If a value changes for some reason you now need to update that value for each language you're supporting in the database
Performance Overhead: Managing five times the amount of data for the scenario from #2 means more overhead for those updates (because you're updating five times as many rows in that case), and more overhead for deletes, and inserts too.
https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver15 Issues: Certain collations only support a certain set of characters or treat comparison and sorting of those characters differently (e.g. on
ORDER BYclauses). Some of the characters of one language might not be supported in the collation you want to use for the other languages, or have unexpected results.
There's multiple frameworks out there for handling language translation in the application / API layer which may be the better solution depending on your end goals.