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
Add comment May 15, 2008
