Database with mutli language data

Question

I have regiteration form in two languages arabic and english how can i insert regester date in enlish or arabic in database ..... does I need two tables or can it be done in the same table ?? and how it can be done ???

Vulpes · Answer

I've had a look on the Internet to see if I could find any examples of doing things this way and whilst I couldn't find one I did find this SO thread which suggests that it is at least feasible: http://stackoverflow.com/questions/181697/multiple-languages-in-one-database-sql-server-2005 So I think the best thing you can do is to simply try it using unicode strings (nvarchar) for those columns which could be in either language. You could record whether the record is stored in English or Arabic by adding this column to the database table: IsEnglish bit NOT NULL You could then pass 1 to this column if it's an English record or 0 if it's an Arabic record and filter on it if you need to separate the two sets of records for any reason. As mentioned in my first post I'd store the registration date for all records in the English format converting between that and Hijri as needed. If you're using SQL Server I'd also read through the following document which discusses in detail its Arabic language support and related issues. Even though it relates to Sql Server 2005 the information it contains should still hold good for later versions: http://technet.microsoft.com/en-US/library/cc295829(v=SQL.90).aspx

alaa · Answer

can i see an example ?

Vulpes · Answer

Sorry I misread the question. However as long as you use unicode character types (nvarchar nchar or ntext) you could still store English and Arabic names and addresses in the same table though you'd need to add a 'bit' column to indicate whether the record is stored in English or not. That would give you some of the benefits of having a single table though you'd obviously need to deal with the English and Arabic records separately if you were doing any ordering or manipulation of the string columns. Although it's not ideal it might be better than having two separate tables as you couldn't really join these in any meaningful way.

alaa · Answer

i mean register form where i have username and address it can be stored in enlisg or arabic depen on the user how can i store something like that

Vulpes · Answer

I'd have thought that one table should suffice and that it's probably best to store the date in Gregorian format as that's the default for most databases. If you're using .NET to write to or read from the database then you can easily convert between Hijri and Gregorian dates using classes in the System.Globalization namespace. The following is largely based on an example I found on MSDN: using System; using System.Globalization; public class SamplesHijriCalendar { public static void Main() { // Sets a DateTime to April 3 2002 of the Gregorian calendar. DateTime myDT = new DateTime( 2002 4 3 new GregorianCalendar() ); // Creates an instance of the HijriCalendar. HijriCalendar myCal = new HijriCalendar(); // Displays the values of the DateTime. Console.WriteLine( April 3 2002 of the Gregorian calendar equals the following in the Hijri calendar: ); DisplayValues( myCal myDT ); /* now convert in opposite direction */ // Sets a DateTime to 21/1/1423 of the Hijri calendar. DateTime myDT2 = new DateTime( 1423 1 21 myCal ); // Creates an instance of the Gregorian Calendar. GregorianCalendar myCal2 = new GregorianCalendar(); // Displays the values of the DateTime. Console.WriteLine( 21/1/1423 of the Hijri calendar equals the following in the Gregorian calendar: ); DisplayValues( myCal2 myDT2 ); } public static void DisplayValues( Calendar myCal DateTime myDT ) { Console.WriteLine(  Era: {0} myCal.GetEra( myDT ) ); Console.WriteLine(  Year: {0} myCal.GetYear( myDT ) ); Console.WriteLine(  Month: {0} myCal.GetMonth( myDT ) ); Console.WriteLine(  DayOfYear: {0} myCal.GetDayOfYear( myDT ) ); Console.WriteLine(  DayOfMonth: {0} myCal.GetDayOfMonth( myDT ) ); Console.WriteLine(  DayOfWeek: {0} myCal.GetDayOfWeek( myDT ) ); Console.WriteLine(); } } The output is: April 3 2002 of the Gregorian calendar equals the following in the Hijri calendar: Era: 1 Year: 1423 Month: 1 DayOfYear: 21 DayOfMonth: 21 DayOfWeek: Wednesday 21/1/1423 of the Hijri calendar equals the following in the Gregorian calendar: Era: 1 Year: 2002 Month: 4 DayOfYear: 93 DayOfMonth: 3 DayOfWeek: Wednesday If you're not using .NET then the chances are that the SQL dialect has a Hijri conversion built in. For example if you're using SQL Server the Convert function has styles of 130 and 131 to deal with Hijri: http://msdn.microsoft.com/en-us/library/ms187928.aspx