Localizing data in SQL Server 2008 tables and sorting

Question

I need to localize data in tables in a SQL Server 2008 database. Each language will need its own sort order when selecting records. I was told in another thread that creating separate tables for each language would make it nearly impossible to create the select statement in a Stored Proc. to return the records for a culture. However, how do I allow for different sort orders if I put all the data in a single table with different rows for each language? I would not have separate columns for each language but instead multiple rows. Can the select statement used to pull the records have a sort order associated with it or does it have to be stored for the table or column?

Answer 1

First thing to do is add a column to determine which culture the row belongs to. After that it is a simple matter of filtering on that column.

Select * from myTable where Culture=1003

You might also consider adding views to that table. From there you can add indexes and sort to your hearts content.

Answer 2

I suggest you create your localised tables with additional column called ie. Locale that will store language. Since all data will ALWAYS be contained in the same columns, there won't be any need for different sort orders (unless users actually need different order by data or asc/desc... Anyway:

Sample table:

ID | Name | Description | Locale
--------------------------------
1    Me     This is me    en
2    Ich    Das bin ich   de
3    You    This is you   en
4    Du     Das bist du   de
....

Your sort order will stay the same. All you gain is an additional where clause

select ID,
       [Name],
       Description
from SampleTable
where Locale = 'en'
order by [Name]

To make things even better I'd normalize these tables by creating a Lookup table with Culture locale abreviations that other tables (like this one I created) would use by foreign keys.