locked
Database tabels design for multiple countries and language RRS feed

  • Question

  • User-464009799 posted

    Hi every body

    I really would be thanksfull if you could you teach me how i can create a huge database for mutli countries and multilanguages too. I am planing to create a business directory for many countries so i am so confuse how the design of the database will look Cry

    Thursday, March 6, 2014 6:16 AM

Answers

  • User1140095199 posted

    Hi,

    msimo

    really would be thanksfull if you could you teach me how i can create a huge database for mutli countries and multilanguages too. I am planing to create a business directory for many countries so i am so confuse how the design of the database will look Cry

    There are few things you need to consider to write a Database that will support data from multiple language and locale. As data has to be provided from the front end application most of the job is to be done there. SQL Server's job is to store these data. Few guidelines are needed to be followed while creating the database so that there is no issues while data is being written to the SQL Server:

    Databases and database applications that use Transact-SQL statements will become more portable from one language to another, or will support multiple languages, if the following guidelines are followed:

    • Replace all uses of the char, varchar, and text data types with nchar, nvarchar, and nvarchar(max). By doing this, you do not have to consider code page conversion issues. For more information, see Working with Unicode Data and Server-Side Programming with Unicode.

    • When you perform month and day-of-week comparisons and operations, use the numeric date parts instead of the name strings. Different language settings return different names for the months and weekdays. For example, DATENAME(MONTH,GETDATE()) returns May when the language is set to U.S. English, returns Mai when the language is set to German, and returns mai when the language is set to French. Instead, use a function such as DATEPART that uses the number of the month instead of the name. Use the DATEPART names when you build result sets to be displayed to a user, because the date names are frequently more meaningful than a numeric representation. However, do not code any logic that depends on the displayed names being from a specific language.

    • When you specify dates in comparisons or for input to INSERT or UPDATE statements, use constants that are interpreted the same way for all language settings:

      • ADO, OLE DB, and ODBC applications should use the ODBC timestamp, date, and time escape clauses of:

        { ts'yyyy-mm-ddhh:mm:ss[.fff] '} such as: { ts'1998-09-24 10:02:20' }

        { d'yyyy-mm-dd'} such as: { d'1998-09-24'}

        { t'hh:mm:ss'} such as: { t'10:02:20'}

      • Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers, should use the unseparated numeric strings. For example, yyyymmdd as 19980924.

      • Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers should use the CONVERT statement with an explicit style parameter for all conversions between the time, date, smalldate, datetime, datetime2, and datetimeoffset data types and character string data types

    There are few more things to be followed if you are about to use diffrent languages such as Working with Unicode Data, Working with Special Character Encodings , Working with Collations  , Working with Languages in the User Interface etc

    For more information follow the link below:

    International Considerations for Databases and Database Engine Applications - http://msdn.microsoft.com/en-us/library/ms190245(v=sql.105).aspx

    Best Regards!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 10, 2014 5:04 AM

All replies

  • User1459534743 posted

    Too hard to tell with this level of info. Think about what kind of data you need to store - that is the different fields that you need and what kind of linking will the different fields have.

    Thursday, March 6, 2014 7:38 AM
  • User1140095199 posted

    Hi,

    msimo

    really would be thanksfull if you could you teach me how i can create a huge database for mutli countries and multilanguages too. I am planing to create a business directory for many countries so i am so confuse how the design of the database will look Cry

    There are few things you need to consider to write a Database that will support data from multiple language and locale. As data has to be provided from the front end application most of the job is to be done there. SQL Server's job is to store these data. Few guidelines are needed to be followed while creating the database so that there is no issues while data is being written to the SQL Server:

    Databases and database applications that use Transact-SQL statements will become more portable from one language to another, or will support multiple languages, if the following guidelines are followed:

    • Replace all uses of the char, varchar, and text data types with nchar, nvarchar, and nvarchar(max). By doing this, you do not have to consider code page conversion issues. For more information, see Working with Unicode Data and Server-Side Programming with Unicode.

    • When you perform month and day-of-week comparisons and operations, use the numeric date parts instead of the name strings. Different language settings return different names for the months and weekdays. For example, DATENAME(MONTH,GETDATE()) returns May when the language is set to U.S. English, returns Mai when the language is set to German, and returns mai when the language is set to French. Instead, use a function such as DATEPART that uses the number of the month instead of the name. Use the DATEPART names when you build result sets to be displayed to a user, because the date names are frequently more meaningful than a numeric representation. However, do not code any logic that depends on the displayed names being from a specific language.

    • When you specify dates in comparisons or for input to INSERT or UPDATE statements, use constants that are interpreted the same way for all language settings:

      • ADO, OLE DB, and ODBC applications should use the ODBC timestamp, date, and time escape clauses of:

        { ts'yyyy-mm-ddhh:mm:ss[.fff] '} such as: { ts'1998-09-24 10:02:20' }

        { d'yyyy-mm-dd'} such as: { d'1998-09-24'}

        { t'hh:mm:ss'} such as: { t'10:02:20'}

      • Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers, should use the unseparated numeric strings. For example, yyyymmdd as 19980924.

      • Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers should use the CONVERT statement with an explicit style parameter for all conversions between the time, date, smalldate, datetime, datetime2, and datetimeoffset data types and character string data types

    There are few more things to be followed if you are about to use diffrent languages such as Working with Unicode Data, Working with Special Character Encodings , Working with Collations  , Working with Languages in the User Interface etc

    For more information follow the link below:

    International Considerations for Databases and Database Engine Applications - http://msdn.microsoft.com/en-us/library/ms190245(v=sql.105).aspx

    Best Regards!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 10, 2014 5:04 AM