title: Data localization in SQL Server with GUIDs or...?
body: <p>I need to make a database that is highly localized. For almost all entities I need a translation to 5+ languages. Some entities even require and additional resource localized (like images which I enter as paths).</p>
  
  <p>The question now is:</p>
  
  <h2>1: LOOKUP TABLE PER ENTITY/TABLE (kinda bloated schema?)</h2>
  
  <p>should I create a "Localized" localization lookup table for each table I need localized values (and use standard int/bigint PKs for elements)
  Like here:</p>
  
  <pre><code>MYITEMS
  -------
   - MyItemId BIGINT PK
   - MyItemPrice DECIMAL
  
  MYITEMLOCALIZED
  ---------------
   - CPK_MyItemId BIGINT FK
   - CPK_LanguageCode NCHAR
   - LocalizedName NVARCHAR
   - LocalizedResourcePath NVARCHAR
  
  CUSTOMERS
  ---------
   - CustomerId BIGINT PK
   - CustomerName NVARCHAR
  
  CUSTOMERLOCALIZED
  ---------------
   - CPK_CustomerId BIGINT FK
   - CPK_LanguageCode NCHAR
   - LocalizedName NVARCHAR
   - LocalizedResourcePath NVARCHAR
  </code></pre>
  
  <p>or</p>
  
  <h2>2: GUIDS WITH SINGLE LOOKUP LOCALIZATION TABLE (heavy guid usage?)</h2>
  
  <p>should I use GUIDs as PKs and then just use a single name and a single resource localization table.</p>
  
  <pre><code>MYITEMS
  -------
   - MyItemId uniqueidentifier PK
   - MyItemPrice DECIMAL    
  
  CUSTOMERS
  ---------
   - CustomerId uniqueidentifier PK
   - CustomerName NVARCHAR
  
  LOCALIZED
      ---------------
       - CPK_ElementGuid uniqueidentifier FK
       - CPK_LanguageCode NCHAR
       - LocalizedValue NVARCHAR
       - LocalizedResourcePath NVARCHAR
  </code></pre>
  
  <h2>3: SINGLE LOOKUP BUT GUIDS ONLY FOR LOCALIZATION (best of 2 worlds?)</h2>
  
  <p>should I use normal int/bigint PKs and then add a GUID column for each column I need localized and store localized values into a single localization lookup table.</p>
  
  <pre><code>MYITEMS
  -------
   - MyItemId BIGINT PK
   - MyItemPrice DECIMAL
   - ItemNameLocalizationGuid uniqueidentifier(GUID)
   - ItemPictureLocalizationGuid uniqueidentifier(GUID)
  
  
  CUSTOMERS
  ---------
   - CustomerId BIGINT PK
   - CustomerName NVARCHAR
   - CustomeerNameLocalizationGuid uniqueidentifier(GUID)
  
  LOCALIZED
  ---------------
   - CPK_ElementGuid uniqueidentifier FK
   - CPK_LanguageCode NCHAR
   - LocalizedValue NVARCHAR
  </code></pre>
  
  <h2>4: LOOKUP TABLE THAT RETURNS LOCALIZATION ID (go back and forth?)</h2>
  
  <p>should I create tables with no guids, but store localization id in the mother-table?</p>
  
  <p>Like here:</p>
  
  <pre><code>MYITEMS
  -------
   - MyItemId BIGINT PK
   - MyItemPrice DECIMAL
   - MyItemNameLocalizedId BIGINT    
  
  CUSTOMERS
  ---------
   - CustomerId BIGINT PK
   - CustomerName NVARCHAR
   - CustomerGenderLocalizedId BIGINT
  
  LOCALIZED
  ---------------
   - LocalizationId BIGINT PK
   - CustomerId BIGINT FK
   - LanguageCode NCHAR
   - LocalizedName NVARCHAR
   - LocalizedResourcePath NVARCHAR
  </code></pre>
  
  <hr>
  
  <p>If I use GUIDs as PKs I've read I'll suffer huge performance and data size penalty, but I will also instantly deal with element uniqueness across servers, dbs...</p>