title: Best data model for a multi-tenant, multi-language application in .NET with SQL 2008
body: <p>I am developing a multi-tenant application in .NET with SQL 2008.  Each tenant can specify their own language.  For instance, a Chinese company may use my application and want all of their data sorted and searchable via Chinese characters.  And a German company may use my application and want all of their data sorted and searchable via German characters.</p>
  
  <p>I am hoping to have one database to store all of the data, segmented by a tenantid. </p>
  
  <p>A table may look something like this:</p>
  
  <p>Members</p>
  
  <p>TenantID  MemberID  Name</p>
  
  <p>1234 5678 John</p>
  
  <p>1235 5679 Jane</p>
  
  <p>The data will be stored in unicode columns (nvarchar).  I would also like to use the full text indexer to index the data.</p>
  
  <p>How best to handle collation in a multi-tenant environment where all of the data is stored in the same database?</p>
  
  <p>Should I specify the collation in the query string (i.e. SELECT * FROM Members ORDER By Name Collate (Chinese))?  Are there any performance issues with dynamically passing a collation, especially if the index on the column is sorted in one particular language?  </p>
  
  <p>Or would it be better to have a database per language and at run time determine which connection string to use?</p>
  
  <p>Also, how best to handle the full text index?  I believe it can only index in one language and you can only specify one index per table.</p>
  
  <p>Anything else to consider?</p>
  
  <p>Thanks!</p>