Best data model for a multi-tenant, multi-language application in .NET with SQL 2008

Question

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.

I am hoping to have one database to store all of the data, segmented by a tenantid.

A table may look something like this:

Members

TenantID MemberID Name

1234 5678 John

1235 5679 Jane

The data will be stored in unicode columns (nvarchar). I would also like to use the full text indexer to index the data.

How best to handle collation in a multi-tenant environment where all of the data is stored in the same database?

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?

Or would it be better to have a database per language and at run time determine which connection string to use?

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.

Anything else to consider?

Thanks!

Answer 1

I believe in your situation you only have the choice between:

• Handle collation in the application layer (that's what I did in the past)
• Denormalise your DB and keep content in separate per-language tables (and deal with a lot of pain down the road, but it may be doable)

As for multiple languages in full-text search (not a topic I'm very up to speed with), MSDN has some documentation, which appears to say that you don't have to indicate language on a per-column level, but may use BLOB or XML language settings:

SQL Server 2005 full-text search honors document-specified language settings in BLOB and XML documents at indexing time.

Still, that may not be what you want.