Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Multiple Language Database Design - Additional Columns vs. Additional Tables... Expand / Collapse
Author
Message
Posted Tuesday, January 27, 2009 11:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 6, 2009 5:03 AM
Points: 1, Visits: 2
tymberwyld (12/5/2007)
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 MMMMM
Post #644702
Posted Thursday, February 19, 2009 11:56 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
I have to admint I did not spent too much time reading all the posts carefully but I think I did not find anyone suggesting the usage of the sysmessages table. We create software for the international markets and we have to support a whole set of languages. All our translations are stored in sysmessages tables. The nice thing about this apoproach is that you can use bulid in SQL function FORMATMESSAGE to deliver the translated text to you. On top of this the mesasges could be parametrized so you do not have to worry about the proper string concatenation to create a proper message in a given order (Many languages have different sentence structure).
In your tables you just have to store the ID of the message stored in sysmessages (and any replacement parameters if you use them) and depending on the connection string options your application is displaying everything in the correct language.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #660669
Posted Thursday, February 19, 2009 12:14 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:32 PM
Points: 3,428, Visits: 14,439
If an application uses sys anything in the code means it may change with the next service pack of SQL Server so your user need you to keep that application running my version with byte changes made to Nvarchar/Nchar in SQL Server 2008 most people can make changes to any application as needed.

Define a column for each language using correct collation and start with translated text, it is that easy.



Kind regards,
Gift Peddie
Post #660694
Posted Thursday, February 19, 2009 12:43 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
I do not think the sysmessages table structure changed since at least version 7
Plus I am not linking to sysmessages but using FORMATMESSAGE function so I do not care much if the schema changes or not.
I like to use existing tools instead of reinventing the wheel and FORMATMESSAGE gives you a lot of functionality.

We use it to translate all texts used by tha application including the windows forms captions.

This may not be viable solution for everyone but wasn't even considered in the post thread so I figured I can mention it.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #660746
Posted Thursday, February 19, 2009 3:01 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:32 PM
Points: 3,428, Visits: 14,439
The thing is what is relevant to Winform is not relevant to Asp.net in localization and even Winform some forms are part of the operating system so to develop for right to left langauges like Arabic and Thai it is better to install Visual Studio for Arabic development.

You can use the Windows Resource localization editor for Winform but Asp.net translated text is still better because I still get wrong language emails from sites using automatic localization.





Kind regards,
Gift Peddie
Post #660904
Posted Tuesday, April 7, 2009 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 8, 2009 10:30 AM
Points: 1, Visits: 4
@Sam McGoldrick

It seems that multilingual databases have been though about, but their concept may have been patented and no one is willing to pay the patent license fees. I may be wrong in this, but it's the closest I could come up with as a solution to this all to common problem of localized data.

Oh, and here is an example of such a patent:
http://www.freshpatents.com/Techniques-for-processing-data-from-a-multilingual-database-dt20081009ptan20080249998.php
Post #692393
Posted Tuesday, April 7, 2009 12:27 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:32 PM
Points: 3,428, Visits: 14,439
I have just looked at that application the US government will not even issue trade mark for crap people like me can create in our sleep with most RDBMS. So I blame the Latin alphabet controlled Unicode standard for such confusion about pedestrian bytes counting and nothing more.




Kind regards,
Gift Peddie
Post #692409
Posted Tuesday, April 7, 2009 12:37 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, March 29, 2011 2:59 PM
Points: 473, Visits: 606
Gift Peddie (4/7/2009)
I have just looked at that application the US government will not even issue trade mark for crap people like me can create in our sleep with most RDBMS...


I would not be so sure about that.

They issued a patent to Microsoft for the Cascading Style Sheets.


---------------------------------------------
Nothing is impossible.
It is just a matter of time and money.
Post #692415
Posted Tuesday, April 7, 2009 1:28 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:32 PM
Points: 3,428, Visits: 14,439
JacekO (4/7/2009)
Gift Peddie (4/7/2009)
I have just looked at that application the US government will not even issue trade mark for crap people like me can create in our sleep with most RDBMS...


I would not be so sure about that.

They issued a patent to Microsoft for the Cascading Style Sheets.


I am hopeful the US government just rejected Dell application to trade mark Cloud as in Cloud computiing.




Kind regards,
Gift Peddie
Post #692452
Posted Sunday, June 14, 2009 7:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, October 11, 2009 3:17 PM
Points: 23, Visits: 107
Am I mistaken in thinking most of the information posted in this thread is more about translating static (or semi static) information back to the end user? Such as within an application, or on a web page?

Although we look to do similar, I am also interested in learning whether it's possible / feasible to receive input data from the end user in different languages

In other words, we'll have some users using a single database whereby someone wants to add in their name in their native language (Korean for example) someone else will be adding in similar data in English, another user will be adding it in Arabic, so effectively we want to be populating the same field (first_name, for example) with data from 3 (or more) different languages

I would be expecting the users to be inputting this data via a public facing web interface
Post #734755
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse