function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Thomas StrohThomas Stroh 

Basic Database Design & Localization

We have translation toolbox enabled and it does the job with basic picklist, field titles, etc.  We will not be using picklists anywhere in our solution for a couple of reasons; most importantly, that we must use lookup tables in order to add associated columns.
 
As a simple example, we need a lookup list that contains "Colors", containing “Red”, "White" and “Blue”.   We may also decide later that we need abbreviated titles, hence “R”, "W" and “B”.  Later we add other associated values, and so on.  And secondly, "Colors" needs to have references to more than just one Object, so why manage the same pick-list values in each Object referenced?
 
For the sake of scalability, Colors must be in a table (or Object) rather than a picklist.  The problem is, we don’t know of a way to provide translations of lookup tables.  Yes, we could add columns like “units-de”, “units-es”, etc.  In order to use the proper column in the UI for the current locale, we would have to write s-controls for every affected object.  There has to be an easier way.
werewolfwerewolf
Well lookups are intended to look up to data, and that data could be vast in scale.  Think of the account lookup.  Lookups aren't really intended for use with fixed static data of the type you would usually translate -- that's what picklists are for.

So what are you doing with these associated columns exactly?
Thomas StrohThomas Stroh
Actually, lookup lists are generally small in scope and unlike “picklists”, they have more than one column.  If you use a picklist, 1) it can’t be consumed in relationships with other tables and 2) you can’t add columns to it.  Therefore, scalable design requires very careful use or complete avoidance of picklists.  Below is an example of a table that would never exceed approximately 300 records, and it clearly could be referenced by many tables in a database.  Note also the need to have multiple columns – if COUNTRY was the only column, you could not easily show OFFICIAL_LANGUAGE in a report that joins to the table.
 
ISONAME   COUNTRY     ABBREV    POPULATION      OFFICIAL_LANGUAGE
CN              China              PRC          1,300,000,000       Chinese
US              America          USA           300,000,000          English
DE              Germany        Ger             80,000,000            German
 
Here’s the issue with localization.  You could easily localize the data in fields like COUNTRY by adding COUNTRY_ES, COUNTRY_DE, COUNTRY_FR  fields for each supported language.  SFDC makes it very difficult to conditionally choose what columns to display in a UI.   For example, when the user is using a Spanish locale, you want to show the COUNTRY_ES column when displaying the table contents instead of the COUNTRY column.  Of course  anything is possible when you throw enough code at it, but it would take visual force pages or s-controls for every lookup table to support localization.  Because you can’t generate text-based SOQL queries on the fly in APEX, you have to write highly repetitive APEX code for every specific lookup object type.
 
Any other input out there? We are really starting to question SFDC's viability as a true enterprise platform.
werewolfwerewolf
Well, there will soon be an ability to generate queries in Apex on the fly.  Also, the lookups dialog is changing to look more like normal search results, so that might help you.  Some of that stuff may be in the Summer release.  Check out the Admin Preview for it when it becomes available to see what can help you out.

You do have a point about the joins on picklists -- it would be nice to be able to do a report joining 2 objects based on equivalent picklist values.