| Author |
Topic |
|
dave_winchester
Starting Member
6 Posts |
 Posted - 2011-10-25 : 11:20:38
|
| Hi allI am after some advice from the SQL experts. Please be gentle...I currently work for a large global organisation that has offices in numerous countries, that operate their own internal "CRM" type systems. We have now started to bring them all together but a number of our offices i.e. France, China etc... require that the application be localised.This is not a problem as far as the actual web pages are concerned, as localising the controls is easy but we have an issue with things like dropdown lists etc...being in English.The application which we run our UK, US and Canadian offices uses a SQL database which has 186 tables, 47 of these are things such AccountType, ContractStatus etc... These form the basis of our dropdown list content and picklists.How do I these make these multilingual for a good design. My ideas was to split these tables into two tables, like:------------------------------------------------------AccountType- AccountTypeID (PK)- IsDeletedAccountTypeLocale- AccountTypeLocaleID (PK or maybe a clustered key such as AccountTypeID, CultureCode)- AccountTypeID (FK to AccountType.AccountTypeID)- CultureCode- Name------------------------------------------------------This would allow me to have 1 "AccountType" record with many translations against it, then depending on the selected language of the user, filter and set the values of the dropdown lists.Please let me know your thoughts. My concern is that it adds another extra 47 tables ontop of what I already have.Is there a better way to do this?Thanks in advanceDavid |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-10-25 : 11:42:31
|
| Common approach is to have a translation table.This has the data item id and translation (can have a column for each language or better a language id)Then there is a table for the data item Ids and item in engish - or this could be in the translation table.Look up the item to get the id then the translation (well - in a single statement).An extension is to have a translation layer in between the database annd the web page - have data items marked as translatable and alter them when delivering - data structures would be similar but may be held in a different place.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
dave_winchester
Starting Member
6 Posts |
Posted - 2011-10-25 : 11:47:20
|
| Thanks for the reply.Do have a simple example of the design to help me along the way.Thanks/D |
|
|
|
dave_winchester
Starting Member
6 Posts |
Posted - 2011-10-26 : 06:29:32
|
| I have been playing, and this is what I have come up with.If I implemented this design that was to hold all values in a single place, it means items such AccountType, ContractStatus, CallType etc...would all be grouped together, but in terms of the database they are not related?For a simple example, properties like Color such as Red, Blue and Green are not related to properties such as texture e.g. Soft, Hard, Furry. Therefore they should go in their own table. But to have 100 tables that have the same design and do the same thing, does not sit well with me.Please comment and let me know your thoughts.--CREATE TABLE dbo.EntityType--(-- EntityTypeID INT IDENTITY(1,1) NOT NULL,-- Name NVARCHAR(250) NOT NULL,-- -- CONSTRAINT [PK_EntityType] PRIMARY KEY (EntityTypeID)--)----CREATE TABLE dbo.Language--(-- LanguageID INT IDENTITY(1,1) NOT NULL,-- Name NVARCHAR(250) NOT NULL,-- CultureCode NVARCHAR(10) NOT NULL,-- -- CONSTRAINT [PK_Language] PRIMARY KEY (LanguageID)--)----CREATE TABLE dbo.Picklist--(-- ItemID INT IDENTITY(1,1) NOT NULL,-- EntityTypeID INT NOT NULL,-- IsActive BIT NOT NULL CONSTRAINT [DF_Picklist_IsActive] DEFAULT (1),---- CONSTRAINT [PK_Picklist] PRIMARY KEY (ItemID),-- CONSTRAINT [FK_Picklist_EntityTypeID] FOREIGN KEY (EntityTypeID) REFERENCES dbo.EntityType(EntityTypeID)--)----CREATE TABLE dbo.PicklistLocale--(-- ItemID INT NOT NULL,-- LanguageID INT NOT NULL,-- Value NVARCHAR(250) NOT NULL,---- CONSTRAINT [PK_PicklistLocale] PRIMARY KEY CLUSTERED (ItemID, LanguageID),-- CONSTRAINT [FK_PicklistLocale_ItemID] FOREIGN KEY (ItemID) REFERENCES dbo.Picklist(ItemID),-- CONSTRAINT [FK_PicklistLocale_LanguageID] FOREIGN KEY (LanguageID) REFERENCES dbo.Language(LanguageID)--)----INSERT INTO dbo.EntityType (Name) VALUES ('LeadSource')--INSERT INTO dbo.EntityType (Name) VALUES ('LeadStatus')--INSERT INTO dbo.EntityType (Name) VALUES ('Industry')--INSERT INTO dbo.EntityType (Name) VALUES ('AccountType')--INSERT INTO dbo.EntityType (Name) VALUES ('MaritalStatus')--INSERT INTO dbo.Language (Name, CultureCode) VALUES ('English', 'en_GB')--INSERT INTO dbo.Language (Name, CultureCode) VALUES ('French', 'fr')--INSERT INTO dbo.Language (Name, CultureCode) VALUES ('German', 'ge')--INSERT INTO dbo.Language (Name, CultureCode) VALUES ('Spanish', 'es')--INSERT INTO dbo.Language (Name, CultureCode) VALUES ('Italian', 'it')--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (1, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (1, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (1, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (1, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (1, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (1, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (1, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (1, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (1, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (1, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (1, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (2, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (2, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (2, 1)--INSERT INTO dbo.Picklist (EntityTypeID, IsActive) VALUES (2, 1)--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (1, 1, 'Advertisement')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (2, 1, 'Partner')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (3, 1, 'Word of Mouth')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (4, 1, 'Employee Referral')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (5, 1, 'External Referral')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (6, 1, 'Public Relations')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (7, 1, 'Web')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (8, 1, 'Other')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (9, 1, 'Trade Show')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (10, 1, 'Seminar - Internal')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (11, 1, 'Seminar - Partner')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (1, 2, 'Publicité')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (2, 2, 'Partenaire')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (3, 2, 'Bouche à Oreille')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (4, 2, 'Référence d''un Employé')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (5, 2, 'Renvoi Externe')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (6, 2, 'Public Relations')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (7, 2, 'Web')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (8, 2, 'Autres')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (9, 2, 'Salon')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (10, 2, 'Séminaire - Interne')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (11, 2, 'Séminaire - Partenaire')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (12, 1, 'Contacted')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (13, 1, 'Open')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (14, 1, 'Qualified')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (15, 1, 'Unqualified')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (12, 2, 'Contacté')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (13, 2, 'Ouvrez')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (14, 2, 'Qualifiés')--INSERT INTO dbo.PicklistLocale (ItemID, LanguageID, Value) VALUES (15, 2, 'Sans réserve')SELECT p.ItemID, pl.Value FROM PicklistLocale plINNER JOIN Picklist p ON p.ItemID = pl.ItemIDINNER JOIN Language l ON l.LanguageID = pl.LanguageIDWHERE l.CultureCode = 'fr' AND p.EntityTypeID = 1 |
|
|
|
|
|
|
Multilingual database design approaches