Multilingual System Design: (2)Data Layer (data structures)

After taking a look on the system, in this post I will describe the suggested design for the Data Layer.

In the data layer we need to have a place to store the languages included in the system. This design support dynamic number of languages even while the system is running. So, a table to store the languages is needed.

In this table we have the Language ID (LangID) that stored a number uniquely indentifies the language I think using the Local ID as LangID will be better or it can be added as a new column in the table. Then we have the language name stored the name of the language written in that language so this column must have NVarChar type. Also we have Language direction identify if the language displayed in right-to-Left or left-to-write order. This information can be retrieved from the CultureInfo object so we can neglect it from the table.

Sometimes the system users table has a column that refers to the default language for the user. Some times its gained from the current culture of the operating system deployed in the user machine. I think prefer storing it in the users table.

Let us move now for the most important part of the system. How to design the database to support multilingual content? First design your database without considering its multilingual. Then, for each table has columns may contain information varying from language to another: first: leave the columns that not depending on the language in the same table then add a new table have a composed primary key (the key from the parent table and the language) and move all the columns depending on the language into that table. So in the new table the parent table primary key will be a foreign key to the parent table.

So, if we have a branches table contains: branch id, name, address, manager and telephone number. We can put the branch ID, manager and telephone in the parent table. While the child table will contains branch ID, language, name and address. Branch ID is a primary key for the parent table. In the child table, the primary key is composed from branch ID and language. Branch ID is a foreign key in the child table.

So if we have the branch data as following:

ID

English Name

Arabic Name

Manager ID

Telephone

English Address

Arabic Address

1

Cairo Branch

فرع القاهرة

200

+2022000

Cairo

القاهرة

Then we need to add a record the branch table:

ID

Manager ID

Telephone

1

200

+2022000

And two records in the branch details table:

ID

Lang

Branch Name

Branch Address

1

1025

فرع القاهرة

القاهرة

1

1033

Cairo Branch

Cairo

In the next post I will continue the talk about the data layer

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: