Database with mutli language data

alaa

12y1.3k.NET

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 ???

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
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
alaa12y ago
can i see an example ?
Vulpes12y ago
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.
alaa12y ago
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
Vulpes12y ago
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