Ok, so there's absolutely no design pattern for Globalizing "data". So I've come up with a solution which seems to be working well. It's based on a combination of different patterns including the way the .NET Framework stores Resources, the EAV Model, and my own brew of things. I knew all along this was the way to go but just wanted to see if there were other suggestions.
First off, let me state I am sorry about the formatting, this Forum seems to strip all Tabs and Spaces out of everthing!
So, I've created a Table (ObjectGlobalization - sorry, couldn't think of a better name) which allows me to globalize data for any table in my database using an EAV Model. You'll notice there are multiple "Value" columns. Only one is used per Column / Row depending on the Column's DataType. For all DataTypes besides VarChar(MAX), Text, NText, and Xml use the Value column, otherwise use the appropriate ValueText or ValueXml columns (you can add others if need be; for example, if you're storing Icons per Culture add a ValueBinary columns with a data type of either VarBinary(MAX) or Image). Also, because the Value column is a Sql_Variant, you should be able to store Chinese characters in there because it can accept a NVarChar data type.
CREATE TABLE [dbo].[ObjectGlobalization](
[ObjectName] [varchar](128) NOT NULL,
[ColumnName] [varchar](128) NOT NULL,
[Culture] [varchar](5) NOT NULL,
[RowID] [sql_variant] NOT NULL,
[Value] [sql_variant] NOT NULL,
[ValueText] [varchar](max) NULL,
[ValueXml] [xml] NULL,
[CreatedBy] [varchar](50) NULL,
[CreatedDate] [datetime] NULL CONSTRAINT [DF_ObjectGlobalization_CreatedDate] DEFAULT (getutcdate()),
[ModifiedBy] [varchar](50) NULL,
[ModifiedDate] [datetime] NULL CONSTRAINT [DF_ObjectGlobalization_ModifiedDate] DEFAULT (getutcdate()),
CONSTRAINT [PK_ObjectGlobalization_1] PRIMARY KEY CLUSTERED ([ObjectName] ASC,[ColumnName] ASC,[Culture] ASC,[RowID] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE FUNCTION [dbo].[ufn_get_GlobalizedValue]
(
@ObjectName VarChar(128),
@ColumnName VarChar(128),
@Culture VarChar(10) = Null,
@RowID Sql_Variant,
@Default Sql_Variant = Null
)
RETURNS Sql_Variant
AS
BEGIN
Declare @Value Sql_Variant
Select @Value = [Value]
From dbo.ObjectGlobalization
Where (ObjectName = @ObjectName)
And (ColumnName = @ColumnName)
And (Culture = @Culture)
And (RowID = @RowID)
Set @Value = IsNull(@Value, @Default)
-- Return the result of the function
RETURN(@Value)
END
GO
CREATE FUNCTION [dbo].[ufn_get_GlobalizedValueText]
(
@ObjectName VarChar(128),
@ColumnName VarChar(128),
@Culture VarChar(10) = Null,
@RowID Sql_Variant,
@Default VarChar(MAX) = Null
)
RETURNS VarChar(MAX)
AS
BEGIN
Declare @Value VarChar(MAX)
Select @Value = IsNull([ValueText], @Default)
From dbo.ObjectGlobalization
Where (ObjectName = @ObjectName)
And (ColumnName = @ColumnName)
And (Culture = @Culture)
And (RowID = @RowID)
-- Return the result of the function
RETURN(@Value)
END
GO
Ok, so how is this used? Well, for my scenario I needed to store Product Descriptions and Names per Culture. I started out attempting to dynamically determine the Culture by using the SqlConnectionString option called "LocaleID", however, it's WAY too much work because you can't simply set it to an LCID, it needs to be SQL Server's own Unique ID for Locales in the SysLanguages table. So, instead I've opted to *sigh* pass in another parameter to my Procedures called @Culture no less.
Most of you will be designing you data using "Invariant Culture" which means basically "English". For my Products table, I opted NOT to store the Description in the table but instead to just store all of them including the "Invariant" version in the ObjectGlobalization table. However, I do store the Invariant version of the Product "Name" in the table, so you'll notice two different techniques in this Proc for retrieving these values.
CREATE PROCEDURE [dbo].[usp_get_ProductsByCategoryID]
(
@CategoryID UniqueIdentifier,
@Culture VarChar(10) = Null-- Defaults to Invariant
)
AS
SET NOCOUNT ON
Select
P.ID,
P.Code,
Convert(VarChar(50), dbo.ufn_get_GlobalizedValue('Product.Products', 'Name', @Culture, P.ID, P.Name)) As [Name],
Convert(VarChar(255), Left(dbo.ufn_get_GlobalizedValueText('Product.Products', 'Description', @Culture, P.ID, Null), 255)) As [Description],
IsNull(P.UnitPrice, 0.00) As UnitPrice,
IsNull(P.ListPrice, 0.00) As ListPrice,
P.Attributes,
P.CreatedBy, P.CreatedDate,
P.ModifiedBy, P.ModifiedDate
From Product.Products P
Inner Join Product.ProductCategories C ON (P.ID = C.ProductID)
Where
(C.CategoryID = IsNull(@CategoryID, C.CategoryID))
You'll notice that you can pass in a "Default" value to these functions which is what I do in the case for the Product Name just in case there is no data for that particular Culture. Also, in my case this is the Proc for returning Products in a List, so I only need the 1st 255 chars of the Description. Also, since the first function (dbo.ufn_get_GlobalizedValue) returns a Sql_Variant, it's very important to explicitly cast it to the correct type so that you UI code performs correctly.
And lastly, I basically just typed the values I needed for this test using SQL MGMT Studio, however, the following Proc will allow you to save the Globalized values. You'd most likely just call this Proc from you're other Ins/Upd/Save Procs.
CREATE PROCEDURE [dbo].[usp_save_GlobalizedValue]
(
@ObjectName VarChar(128),
@ColumnName VarChar(128),
@Culture VarChar(10),
@RowID Sql_Variant,
@Value Sql_Variant = Null,
@ValueText VarChar(MAX) = Null,
@ValueXml Xml = Null,
@createdby VarChar(50) = Null,
@CreatedDate DateTime = Null,
@ModifiedBy VarChar(50) = Null,
@ModifiedDate DateTime = Null
)
AS
Set NoCount On
-- Exit if there is no data
If (@Value Is Null) And (@ValueText Is Null) And (@ValueXml Is Null) RETURN(1)
-- Validate the Data
Select
@ObjectName = NullIf(LTrim(RTrim(@ObjectName)), ''),
@ColumnName = NullIf(LTrim(RTrim(@ColumnName)), ''),
@Culture = NullIf(LTrim(RTrim(@Culture)), ''),
@createdby = IsNull(@CreatedBy, SUser_Name()),
@CreatedDate = IsNull(@CreatedDate, GetUtcDate()),
@ModifiedBy = IsNull(@ModifiedBy, SUser_Name()),
@ModifiedDate = IsNull(@ModifiedDate, GetUtcDate())
If (@ObjectName Is Null) RaisError('The [@ObjectName] Parameter cannot be Null or Empty.', 16, 1)
If (@ColumnName Is Null) RaisError('The [@ColumnName] Parameter cannot be Null or Empty.', 16, 1)
If (@RowID Is Null) RaisError('The [@RowID] Parameter cannot be Null or Empty.', 16, 1)
If (@@ERROR <> 0) RETURN(-1)
BEGIN TRY
If Not Exists(
Select Null From dbo.ObjectGlobalization
Where ObjectName = @ObjectName
And ColumnName = @ColumnName
And Culture = IsNull(@Culture, '')
And RowID = @RowID
)
BEGIN
Insert Into dbo.ObjectGlobalization
(ObjectName, ColumnName, Culture, RowID,
Value, ValueText, ValueXml,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
Values
(@ObjectName, @ColumnName, IsNull(@Culture, ''), @RowID,
@Value, @ValueText, @ValueXml,
@createdby, @CreatedDate, @ModifiedBy, @ModifiedDate)
END
Else
BEGIN
Update dbo.ObjectGlobalization Set
ObjectName = @ObjectName,
ColumnName = @ColumnName,
Culture = IsNull(@Culture, ''),
RowID = @RowID,
Value = @Value,
ValueText = @ValueText,
ValueXml = @ValueXml,
ModifiedBy = @ModifiedBy,
ModifiedDate = @ModifiedDate
Where ObjectName = @ObjectName
And ColumnName = @ColumnName
And Culture = IsNull(@Culture, '')
And RowID = @RowID
END
If (@@TRANCOUNT > 0) COMMIT
RETURN(1)
END TRY
BEGIN CATCH
If (@@TRANCOUNT > 0) ROLLBACK
RETURN(-1)
END CATCH