Products translation ?

Good day all,Good day NC team ,Good day Mr. Andrei ,

  I'd love to share some info with you, a design that is best (from my view) for both  full text search & language support .
then I have another design concerning Translators staff based on it as follows:
1-An Initiator is the level 1 Translator who will view only what he is permeted to view(latter inStatusID it will be explaned).

2-Validator who will see the translation & verify it before it can be shown to visitors of the web site.




now consider a table called cultures like this :

CREATE TABLE [Culture](
  [CultureID] INT NOT NULL,
  [Name] NVARCHAR (20) NOT NULL UNIQUE,
  [Sorting] SMALLINT NOT NULL,
  [Active] BIT NOT NULL DEFAULT 1,
    CONSTRAINT [PK_CultureID] PRIMARY KEY CLUSTERED ([CultureID] ASC));
Go

CultureID int but not auto increment brcause we will enter its LCD code i.e1033 for en-US,1036 for fr-Fr ,You can get this list from http://msdn.microsoft.com/en-us/library/0h88fahh(VS.85).aspx

Now,let's take an entity as Product table to be  an example to localize & use catalog search i.e FTS,then  we can implement the same technique for all other entities


her is our Product table:
for simplicity,I need ProductID ,name,Description,Price ,but of course you can add any other proprties you need.

As you can see,We'll use English as our default language .So, We'll create Product table to handel English Text,
& ProductCulture to handele any other language.



CREATE TABLE [Product](
  [ProductID] INT IDENTITY(1,1) NOT NULL,
  [Name] VARCHAR(50) NOT NULL UNIQUE,
  [Description] VARCHAR(MAX) NOT NULL,
  [Price] MONEY NOT NULL
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED ([ProductID] ASC))
Go


Now,let's create full text search:

CREATE FULLTEXT CATALOG MyFullTextCatalog
WITH ACCENT_SENSITIVITY = OFF
AS DEFAULT
AUTHORIZATION dbo; --Owner
GO


CREATE FULLTEXT INDEX
ON [Product]
(
  [Name]  LANGUAGE English,
  [Description] LANGUAGE English
)
KEY INDEX [PK_Product]--PK
ON
(
  MyFullTextCatalog
)
WITH
(
  CHANGE_TRACKING AUTO
);
GO                                                                                                                                                                                                                                                                                                                                                          
                                                                                                      

ALTER FULLTEXT INDEX
ON [Product] ENABLE;
GO


Now,Create ProductCulture & Please pay attention as this is the most important part!!!!


CREATE TABLE [ProductCulture](
  [ProductCultureID] INT IDENTITY(1,1) NOT NULL,
  [ProductID] INT NOT NULL,
  [CultureID] INT NOT NULL,
  [Name] NVARCHAR(50) NOT NULL,--[Name] default for NON  en-us
  [Description] NVARCHAR(1000) NOT NULL,--[Description] default for NON  en-us
CONSTRAINT [PK_ProductCulture] PRIMARY KEY CLUSTERED ([ProductCultureID] ASC),
CONSTRAINT [AK_ProductID_CultureID] UNIQUE NONCLUSTERED([ProductID],[CultureID]))
GO




ALTER TABLE [ProductCulture] WITH CHECK ADD CONSTRAINT FK_ProductCulture_Culture
FOREIGN KEY([CultureID])
REFERENCES [Culture]([CultureID])
GO



ALTER TABLE [ProductCulture] WITH CHECK ADD CONSTRAINT FK_ProductCulture_Product
FOREIGN KEY([ProductID])
REFERENCES [Product]([ProductID])
GO




Explanation:

when a new product is created,its PriductID is inserted in the database (can be read by @@identity).

Now for French,In ProductCulture
ProductID in ProductCulture is  FK to ProductID in Product ,So We make sure that no product will be translated except
if it exidt in Product table.


Again,CultureID in ProductCulture is FK to CultureID in Culture.
Now,For CultureID 1036 for French can be inserted in ProductCulture if it exist in Cultures

An alternate key is used to maintain integrety(ProductID,CultureID) i.e ProductID =1 will be translated once & only once to any language.


So, This is fine for language support, but what about FTS that cann't accept PK with multicolums?

Here comes ProductCultureID key !
This is the key that will be consumed by the FTS feature!


If you like this design, you can follow me to the Translators section !!!!!!

we will create  log file for Product to track products created by Admins!

but what about the translators????

We need to know the status of the product


Now let's create the StatusID table

CREATE TABLE [Status](
  [StatusID] INT NOT NULL,
  [Name] VARCHAR (20) NOT NULL,
CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED ([StatusID] ASC));
Go



let's say w'll have:
StatusId 1 to refer to Pending
StatusId 2 to refer to Intialized
StatusId 3 to refer to Rejected
StatusId 4 to refer to Accepted.

Now  lets create the log table for  ProductCulture



---
--- PRODUCT log
---
CREATE TABLE [ProductCultureLog](
  [EventID] INT IDENTITY(1,1) NOT NULL,
  [ProductCultureID] INT NOT NULL,
  [StatusID] INT NOT NULL,
  [UserID] UNIQUEIDENTIFIER NOT NULL,
  [AddDate] SMALLDATETIME NOT NULL DEFAULT GETDATE(),
CONSTRAINT PK_ProductCultureLog PRIMARY KEY CLUSTERED ([EventID] ASC))
Go



ALTER TABLE [ProductCultureLog] WITH CHECK ADD CONSTRAINT FK_ProductCultureLog_Product
FOREIGN KEY([ProductID])
REFERENCES [Product] ([ProductID])
GO



ALTER TABLE [ProductCultureLog] WITH CHECK ADD CONSTRAINT FK_ProductCultureLog_Status
FOREIGN KEY([StatusID])
REFERENCES [Status] ([StatusID])
GO



ALTER TABLE [ProductCultureLog] WITH CHECK ADD CONSTRAINT FK_ProductCultureLog_UsersInAccounts
FOREIGN KEY([UserID])
REFERENCES [UsersInAccounts] ([UserID])
GO



now we have this table so.what's up??

1-Initiator will see only what he can i.e
Pending after adminstrator insert new product.
Rejected if the verifier rejected such translation for any mistackes.



2-Verifier will see Intialized indecating that primary translation is over ,so He must Accept to be shown to visitors,
or,rejected to appear back to Initiator to make any neccesary actions.



each of the precceding step is loged as a row in ProductCultureLog table.


I hope You enjoy.



Dear Mr. Andrei,

I'll be waiting for your evaluation to my precceding design as your openion is very valuable to me.

Thank you all.

Keem,

Thanks for suggestion and good explanation. But we have already implemented entity (products, categories etc) localization. It's very similar to your one, but without statuses. This feature will be available in the next release

Dear Andrei,
   Thanks a lot for your time & concern.

nopCommerce team | a.m. wrote:


Very nice to hear that products-categories multilingual it is being implemented. So which date will this version will be released. It is very crucial for me to know.

Thanks

nopCommerce team | a.m. wrote:


Hi...

Beinng new to NOPcommerce...  I can not find the product translation...  When I look at a product in the back end.. nowhere I can see anything related to a language... Where do I manage that?

I think you should implement the latest version or ask this Translation Agency for their Spanish Translation Services
Good luck!

hi everybody
im using nopcommerce v.1.9 and im using two languages english and italian ,but on the page Shipping Method and payments methods the langauage in only one does not change in different language, in english language show me in english which is ok,but when i change it to italian language still showing me the english and in the administration pannel if i change it to italian its show me italian also in english language. some one can help?
thanks

Many English phrases will not translate congruently with a target language in word by word manner of translation. Some phrases of a particular language don't have their counterpart version in other foreign language. Therefore, in this case, the message or thought being conveyed must be properlyTranslation Newark Nj translated in the targeted language's manner of expression. This is a very crucial part of document translation that must be done with utmost clarity and appropriateness.  Poor translation often leads to loss business projects, misunderstandings, impression of unprofessionalism and a lot more negative effects.