Where to store translations for values to their corresponding display texts?

Question

This is probably a naive question but I'm trying to figure out the industry best practice for working with magic numbers and their corresponding display texts. For example, whether a transaction is debit or credit might be stored as a bit field in the database, but the true/false/0/1 need to be displayed as "Debit" or "Credit" etc. Typically, there would be an enum as well that needs to be kept in sync with the magic numbers and their meanings.

There are two specific cases that I'm trying to resolve -

1. We want to translate them to human readable text - UI needs to know that when it sees zero, it needs to display "Debit" etc.

2. We want to work with the magic numbers within the source code - Here we usually translate them as enums. Enums get rid of the magic numbers but are hardcoded so difficult to keep in sync with the database values.

In a moderately sized application we can end up with hundreds to thousands of such translations, for example -

Context - {Value, Translation}

TransactionType - {0, Debit}, {1, Credit}

FileType - {0, CSV}, {1, XML}, {2, Excel}

ItemType - {0, Manual}, {1, Automated}, {2, Writeoff}, {3, System}

Status - {0, Active}, {1, InActive}, {2, Pending}, {3, Delivered} etc etc

I can't figure out a solution that allows enums to be synced/loaded dynamically from the database AND allows translations of database values to texts without losing referential integrity.

I see the below options -

1. Hardcoding in the UI (Views or via GetDisplayTextFor(int) methods etc).
2. Some external Text/XML files.
3. A database table with columns (Context, Value, Translation) - But now we've created new magic numbers in the "Context" column.
4. Database tables for each set of such mappings with values as referencing foreign keys - This will allow referential integrity but will mean addition of potentially numerous tables.

What are the industry practices for this problem? Can enums be generated dynamically from the database and can they be converted to texts or should enums be avoided altogether for such cases? Any other standard solutions/patterns that solve at least as much of the problem as possible?

Answer 1

Since you're talking in a context of .NET, translations are usually stored in resource files. This means that you'll create two resources: TransactionTypeCredit and TransactionTypeDebit. Those resources would then be translated in English, Chinese, Russian, etc.

Those resources is what UI will use. But unlike you suggest in your question:

UI needs to know that when it sees zero, it needs to display "Debit"

the UI should never see zero. Or one. Or twenty four. Those are the database values which should stay in the database: the UI should not be aware of those values.

This means that in the business layer, you will create two classes: one for credit-type transaction, and another one for debit-type transaction. Both CreditTransaction and DebitTransaction will inherit from BusinessTransaction, and contain the business rules corresponding to the respective transaction types.

Why classes instead of enum? Simply because I'm pretty sure your business rules differ for a credit transaction from a debit one. This means that, if you stick with an enum, you'll end up doing a lot of switch statements. In those situations, “replace conditional with polymorphism” refactoring technique makes your code more maintainable.

Once you have those classes, UI will display them differently, possibly by pointing to different resources (that we created above), but maybe also by customizing the presentation such as changing the background.

The task that's left is to bind those classes to the database values. This is the goal of the data access layer. If you use an ORM, there is usually an option which makes it create different classes based on a specific value of a database column. If not, write your own code (eventually using a factory pattern). This factory should be the only location where you'll have a switch statement for the transaction types (a map will work too).

Keeping it in sync

I can't figure out a solution that allows enums to be synced/loaded dynamically from the database AND allows translations of database values to texts

You can't.

Taking your example Status - {0, Active}, {1, InActive}, {2, Pending}, {3, Delivered}, imagine you implemented them with some magical solution which auto-generates the code for you.

Now, you want to add the fifth status: cancelled. You do it in the database, and some very smart ORM goes through the database constraints and creates an additional status in code. First issue: how would the ORM guess how to name this status, given that the database only tells that instead of 0..3, it now accepts 0..4?

Let's imagine that you figured that out, and magically, the ORM guesses that the fifth status corresponds to Cancelled. Some other tool auto-generates the corresponding resource and even affects “Cancelled” value for its en-US culture. Now what? How would it translate it to Hebrew, German or Japanese?

A note about the enums

You seem to like enums, given the number of times you mention them in your question. However, you should avoid using them as a default choice for everything. A rule of thumb is that if you have a collection of items which could be extended in the future, enums are a good choice. If not, stick with constants. If you have different business rules depending on the collection item, use inheritance to avoid switch statements.

Answer 2

Using enums usually help to keep control over in-code usage, but i typically limit enums to values that are application controlled, and don't expect to change with any level of frequency. the values you're talking about sound like they could have quite a bit of change, or new sets of values being created at times. using enums for this scenario would get unwieldy. i would opt of a database-driven data model to manage these items, which i usually consider a sort of "mapping".

storing this data in tables solves the issue of data integrity, and it can be done without a need for constantly creating new tables, by employing a self-referencing table design.

https://i.stack.imgur.com/QfzOV.jpg

Update (01/17/2017):

With the mapping table approach, synchronization is pretty much "built-in", as long as you are using some form of ORM within your data layer.

Here's some quick and dirty coding examples, assuming you are using EntityFramework:

public class Trade
{
    public int Id {get; set; }
    public string Description {get; set; }
    public decimal Amount {get; set; }
    public int TransactionTypeId {get; set; }

    [ForeignKey("TransactionTypeId")
    public DataTypeMap TransactionType {get; set; }
}

public class DataTypeMap
{
    public int Id {get; set; }
    public string Name {get; set; }
    public string DisplayName {get; set; }
    public int ParentId {get; set; }

    [ForeignKey("TransactionTypeId")
    public DataTypeMap ParentMap {get; set; }
}

With these two models, EF, will pull in the necessary information from the related table (Navigation Property), so as long as Trade.TransactionTypeId has the correct integer value, you can get the display text by referencing Trade.TransactionType.DisplayName.

Of course, there is still mode coding to be done to wire it all up on the EF side, but it is pretty much standard EF stuff.