People_db - database of people in Indian languages

People_db is a simple database application, which can be used to store the information about people in the database. Information about a person includes First Name, Last Name, Nick Name, Date of Birth, Description, Postal Address, Phone No., Email etc. You can also create important notes about a given person. The application usage is straight forward and hence self explanatory.

Features

• This application supports storage of information in Indian languages text. You can select the language in the Options dialog box.

• You can Add/Edit/Delete people in the system.

• You can query the database by providing a search string. The search string is a “substring" of certain fields, such as First Name, Last Name, Address, Description and Notes. For example, If you have "Search By" option as FistName = rAja, then you will get a list of all the people whose FirstName contains the string "rAja" in it - such as rAjaNNa, gururAja, etc.

• You can display the results in either ascending or descending order of certain fields, such as Person ID, First Name, Last Name, Nick Name, and Telephone No.

• For the currently selected person, you can add/edit/delete notes. A Note has a title, date and description. Notes are used to document important incidents that occurred w.r.t the currently selected person.

• You can generate reports in the form of a HTML file by using the Report menu. Two types of reports available: "All Address" report creates a HTML file containing all the addresses in the system. You can print this report to produce the address labels for mailing purpose. The "Selected Person" report will have all the details about the currently selected person.

Options Dialog Box

Options dialog box can be accessed by clicking the "Options" menu. In this dialog box, you can choose the following.

Language: Is the language in which you want to do the data-entry. Only those records that correspond to the selected language are displayed in the table.

Maximum Rows: If the database has a lot of records, then showing all of them in the table may take a lot of time. This setting specifies the maximum no. of records(rows) to be displayed in the table. You can also use the search option to limit the no. of records returned.

Keyboard Delay: Is the keyboard delay setting in milliseconds. Please refer to Baraha Direct help topic for more information.

Date Fields

The DOB field is a date field. The date can be entered as mm/dd/yyyy format (ex: 08/15/1947). You can also type the name of the month like "15, AgasT 1947". If you improperly type the name of the month, you will get "Invalid Date" error.

Requirements for running the sample application

The following components are required for running the above application. These components may be already installed in your system. If you get any errors running the application, then install the following components,

• You need Baraha 6.0 installed in your system.
• You need Visual Basic 6.0 runtime. You can download the same from Microsoft's website.
• You need MDAC 2.0 or later version installed in you system. You can download the same from http://www.microsoft.com/data/download.htm.
• You need MSFlexGrid (msflxgrd.ocx) and SSTab (tabctl32.ocx) controls installed in your system. You can download the same from here.

The following section is only for the programmers

Application Design:

As all of us know, database is a repository of data arranged in the form of various tables which may be related to one another. Required data is obtained from the database by running "database queries". The order in which the records are retrieved can be set by specifying the "sort order" in the query. The above rules are applicable to creating databases in Indian languages also. In broad terms, creating an Indian language database application is no different from creating the same in English language. But, a few special rules/techniques should be followed when implementing a such applications.

The Visual Basic sample people_db demonstrates the power and ease of use of the SDK in creating database applications. The database people_db.mdb  is created using MS Access. The program files and the source code for this application are available under sdk\vb\people_db directory when you extract brhsdk.zip file. You can extend this program to create your own programs.

The Database has two tables 1) tbl_people and 2) tbl_notes which are linked by PersonID.

The FirstName, LastName and NickName fields are stored in COLLCODE hex strings. This is because we want to be able to sort the records based on these fields. The data in the other fields are stored in the transliteration formats. (ex: KANTRANS for Kannada, TAMTRANS for Tamil, etc).

Choosing proper data format for the field:

Indian language text can be stored in the database in various formats. For a given field, the choice for the format is mainly decided by the factor, is the field a sortable field or not?. Database sorting in Indian language alphabetical order is possible by storing the fields in COLLCODE hex strings format. Data stored in COLLCODE format, when sorted in the binary order, will return the data sorted in the Indian language alphabetical order. But, binary sorting is not available in all the databases. For example, MS Access does not support binary sorting. In such cases, we should store the sortable fields in COLLCODE hex strings format. The COLLCODE hex strings format is nothing but the numeric representation of the COLLCODE data. Every Indian language character is represented using a 4 bytes representing the hex value of the code. The Databases support different types of sorting such as English Case Sensitive, English Case Insensitive, Binary e.t.c.. But, irrespective of the sorting mechanism used by the databases, the numeric strings (strings containing the numbers) are always sorted properly. Therefore,  COLLCODE hex strings format is useful when it comes to sorting Indian language strings in the database.

Storing Indian language text in COLLCODE hex strings has advantages and disadvantages. The advantage is that you don't have to use binary sort order for the database. Using binary sort order for the database affects the way in which English strings are sorted. When we use COLLCODE hex strings format for the data, we don't have to use binary sort order for the database. Instead, we can use English Case Sensitive, or English Case Insensitive sort orders. This way we can ensure proper sort order for both Indian language & English fields in the same database at the same time.

The disadvantage of COLLCODE hex strings is that it requires more space for storing the data. For example, the word "rAja" requires just 4 bytes when stored in KANTRANS format. Whereas, it requires 16 bytes when stored in COLLCODE hex strings format. Therefore, we should use COLLODE hex strings format only for the sortable fields. It is not necessary to use this format for the fields which are not used as a sort criteria.

In people_db application, we can sort people based on their First Name or Last Name. Therefore, we have used COLLCODE hex strings format for these fields and have allocated a large space (256 bytes) for storing the data. The other fields are stored in transliteration formats.

Saving/Retrieving fields:

In people_db, different fields are stored in different data formats. But, when it comes to displaying the field in the grid, or controls, or HTML report, we first convert them from their respective format into ANSI format. For example, the First Name, Last Name fields are in COLLCODE hex strings format. They are converted to ANSI format before displaying in the grid or controls. Similarly, before saving into the database, the text in the Description field is first converted from ANSI to transliteration format.

Searching Fields:

In people_db, search can be performed for the First Name, Last Name, Nick Name, Description fields. The search text entered into the Search Field is first converted to the proper data format, before using in the SQL. The transliteration formats use case sensitive English characters for representation. For this reason, we have to use case sensitive search. MS Access does not support case sensitive comparison at the database level. Therefore, we have used built-in MS Access functions such as StrComp, InStr in the SQL query.

Date Conversion facility:

The function ConvertDate(String) is very useful in VB applications for converting the Date/Time from English to Indian languages and vice versa. For Example, the string "Tuesday, June 25, 2002" will be converted to "maMgaLavAra, jUn 25, 2002" for Kannada. The same is converted to "maMgalavAra, jUna 25, 2002" for Devanagari.