I'm working on a web application that I plan to make available in multiple languages. As I design the database, I'm going back and forth between two different ways to store localized descriptions and whatnot in the database.
First option is the well-known table_name, table_name_ml type option:
TABLE Category (
ID int,
ParentID int,
Name varchar(50),
Description varchar(255)
)
TABLE Category_ML (
ID int,
CategoryID int,
LocaleID int,
Name varchar(50),
Description varchar(255)
)
The second option would be to not store the text in the base tables at all, but instead store a token that could be used to lookup the actual localized text elsewhere, like this:
TABLE Category (
ID int,
ParentID int,
NameToken varchar(50),
DescriptionToken varchar(50),
)
// Tables in a separate content management type system
TABLE Content (
ID int,
Token varchar(50)
)
TABLE Translation (
ID int
ContentID int,
LocaleID int,
Value text
)
The idea here is that the Content and Translation tables would hold the localized text for many different entities in the database. The service layer would return the base objects with just the tokens and the view layer would look up the actual text values using the Content/Translation tables - which would be heavily cached. The Content/Translation tables would also be used for storing other CMS type content (static text on web pages, etc.)
I like the first option because it's tried and true, but the second option seems to have so many other advantages:
- All my text/localized content is in one place (makes translating easier).
- Service layer doesn't really need to care about locales.
- Simplifies queries by not having to join in a bunch of ML type tables.
Since I've never seen a design like this before, I assume I must be missing something. Any good reasons not to design it this way? Or maybe there's a better option that I haven't thought of?