DDH Software, Inc Making a big world smaller






Return to Knowledgebase Home
Tutorial I: Designing and Using a Simple Database
Product: General HanDBase TopicArticle Number: 78Creation Date: 03/14/2000

Overview

This tutorial will walk you through the design of a database in a step-by-step fashion. If you follow along using the HanDBase application in the platform of your choice, you should be able to better create your own databases in the future.

Please Note: Where Windows Mobile is mentioned this means a Windows Mobile Classic/Professional (Pocket PC) Device with a Touchscreen. The current version of HanDBase for Windows Mobile Standard (Smartphone) devices, without a touchscreen, does not have the ability to create databases on the device.

Design

We will be designing a database to keep a list of Restaurants we might visit. This should make it easier to decide where to go out for dinner this weekend!

Layout

We would want to track the restaurant name, the location, the type of food they serve, whether it is fast food or sit-down, the phone number, whether they take reservations, and perhaps a 1 to 4 star rating.

Steps
1. Launch the 'HanDBase' application.
Palm OS: Press the 'Applications' button. This is the button to the top left of the Graffiti area. You should see a list of Applications available on your handheld. Search for the 'HanDBase' icon. Tap this with your stylus to start HanDBase.
Windows Mobile : Turn on the device. Tap on the Start Menu (Windows Logo) and choose Programs. Then scroll down and select 'HanDBase'.
Windows Desktop: Double-click on the 'HanDBase Desktop' icon in the desktop or go to Start - Programs/All Programs - HanDBase 4 and click the icon for HanDBase Desktop.

2. If this is the trial version of HanDBase, you may see a startup splash screen indicating that this is a trial version. Press OK to continue.
Palm & Windows Mobile: You will now be in the 'Choose Database' screen.
Desktop: You will now be in the main window.

3. Once you are in this screen, you may create a New database.
Palm: Press the 'New' button to create a New database.
Windows Mobile: Press the 'Create a new database' button, then tap on the 'General' button.
Desktop: Click on the first icon in the toolbar (from left to righ), then click on 'General'.

4. You will now see the 'General Settings' screen, where you can name your database.
Palm: You may also set its category and other various settings regarding this database.
Windows Mobile: Categories are not supported in this device.
Desktop: You may also set other various settings regarding this database (Categories are not supported in the desktop).

5. Name this database 'Restaurant List', by selecting the field next to 'Database Name" and entering the text.

6. Now lets begin defining the fields. We are now headed to the 'Edit Fields' screen.
Palm: Tap on the 'OK' button.
Windows Mobile: Tap on the 'OK' button, then the 'Fields' button.
Desktop: Tap on the 'OK' button, then the 'Fields' button.

7. Let's make the first field be the Restaurant Name. Select Field 1 by tapping or clicking on it. We will give the field a name by entering it next to 'Field Name', and writing 'Restaurant Name'.

8. Now this field will contain the text of each Restaurant, so we will want to make this a 'Text Field' type. Select the trigger to the right of 'Field Type', which should currently be 'Not-Used', and then select 'Text'.

9. You are now given a list of properties for this field. The 'Max Characters' parameter is currently set to 40, which should be enough for a Restaurant name. The 'Visible' field should be checked, which indicates that it will be a field that is visible in the Edit Record screen. The 'Print' field is checked by default, it indicates that we want the field to be printed on hardcopy or shown in our export when we do either activity. 'Encrypt' allows this field to be encrypted on a per-record basis, this is useful -for example- when we want to protect a password in a database but all other fields are OK to allow viewing. Pixels shown is the width of this column (in number of pixels (or dots on the screen) to show. This can range from 0 to 160, with 160 taking up the whole screen, and 0 being invisible. We will leave all of these values as is for now.

Press the OK button as we are finished with this field.

10. Now you will return to the 'Edit Fields' screen again. Select 'Field 2' from the list by tapping or clicking on it.

11. Let's make the next field be the type of food served. First we will give the field a name by tapping the area next to 'Field Name', and writing 'Type'.

12. We probably want the user to be able to select from a list of predefined types, such as 'American', 'Mexican', 'Chinese', etc. This field should mostly like be a 'Pop-Up' type for this reason. Select the trigger to the right of 'Field Type', which should currently be 'Not-Used', and then select 'Pop-Up'.

13. You are now given a list of properties for this field. Again we will leave all of these values the way they are for now.
Press the OK button as we are finished with this field.

14. Now you will return to the 'Edit Fields' screen again. Select 'Field 3' from the list by tapping or clicking on it.

15. Let's make the next field be the location of the restaurant. First we will give the field a name by tapping the area next to 'Field Name', and writing 'Location'.

16. Now this field will contain the location of each Restaurant, so we will want to make this a 'Text Field' type. Select the trigger to the right of 'Field Type', which should currently be 'Not-Used', and then select 'Text'.

17. You are now given a list of properties for this field. This time we will probably want to edit the 'Max Characters' setting, as 40 characters may not be enough for an address. Select the area to the right of 'Max Characters' and enter 120. That should be enough.

18. Press the OK button as we are finished with this field.

19. Now you will return to the 'Edit Fields' screen again. Select 'Field 4' from the list by tapping or clicking on it.

20. Let's make the next field be a Popup asking whether they are Fast Food or Sit-Down. First we'll give the field a name by tapping the area next to 'Field Name', and writing 'Service'.

21. We will want the user to be able to select from a list of predefined types, such as 'Fast Food', or 'Sit-Down'. This field should mostly like be a 'Popup' type for this reason. Select the trigger to the right of 'Field Type', which should currently be 'Not-Used', and then select 'Pop-up'.

22. You are now given a list of properties for this field. Again we will leave all of these values the way they are for now.
Press the OK button as we are finished with this field.

23. Now you will return to the 'Edit Fields' screen again. Select 'Field 5' from the list by tapping or clicking on it.

24. Let's make the next field be a Checkbox asking whether they take reservations. First we'll give the field a name by tapping the area next to 'Field Name', and writing 'Reservations?'.

25. We will want the user to be able to check whether they take reservations, so this field should be a 'Check-Box' type for this reason. Select the trigger to the right of 'Field Type', which should currently be 'Not Used', and then select 'Check-Box'.

26. You are now given a list of properties for this field. Again we will leave all of these values the way they are for now.

27. Press the OK button as we are finished with this field.

28. Now you will return to the 'Edit Fields' screen again. Select 'Field 6' from the list by tapping or clicking on it.

29. Let's make the next field be the Phone number field, which should also be a text field. First we'll give the field a name by tapping the line next to 'Field Name', and writing 'Phone Number'.

30. We will now make this field a 'Text' type, so select the trigger to the right of 'Field Type', which should currently be 'Not-Used', and then select 'Text'.

31. You are now given a list of properties for this field. Again we will leave all of these values the way they are for now.

32. Press the OK button as we are finished with this field. We are now back at the 'Edit Fields' screen. This shows all of the fields we have setup and their type. Notice the 'Quick Search' pop-up option, when we select a field for it, we may quickly search for a record by entering letters on the handheld while looking at the list of records. The search is based on the selected field. In this case the Restaurant Name is a good option, since we will quickly want to find our options while on the street.

33. Let's not add the rating just yet, and rather, go see what we have so far. We can always come back later and add, adjust, or take away fields. Press on the 'OK' button again, then again on 'OK', and the database will be created for you.

34. Now the database will be visible. This screen is called the 'List View' as it will list a summary of each record in the database. The name, 'Restaurant List' should show at the top, and three fields are partially visible, Restaurant Name, Type, and Location. Let's adjust these so that the Restaurant Name and Type fields are wider. Touch the area at the right-most of the 'n' in restaurant. You should see a vertical line and be able to drag your stylus to right, in order to widen the 'Restaurant Name' field. Drag it to just past the center of the screen and let go. The rest of 'Restaurant Name' should appear. If not try again and drag it further.

35. Let's also widen the 'Type' field. Touch a bit to the left of the 'L' in location- to the very right of the 'Type' field and widen this to the right edge of the screen by dragging to the edge and then releasing your stylus.
Desktop: Since the desktop has more real-estate than the handhelds, it will be probably unnecesary to this there.

36. Now the screen should look quite nice, only showing the 'Restaurant Name' and 'Type' fields on the screen.
Desktop: This will not be true on the desktop, since all fields will be showing already.

37. Now we are ready to try adding a record, and while doing so, we will set up the Popup lists as well.
Palm: Press the 'New' button at the bottom of the screen.
Windows Mobile: Tap on the 'New' (white page) icon on the screen.
Desktop: Click on the 'New Record' button.

38. The first thing you may notice is that the 'e' in Restaurant Name is cut off. We can alleviate that by decreasing the width of the actual field just a tad. Press the 'Menu' button, which is the button to the bottom left of the Graffiti area. A pull down menu should appear and some choices as well. Press the choice labeled 'Decrease Field Width'. This will decrease the width of right side of the screen, and increase the left. Now the whole 'Restaurant Name' field is visible. Had it not been, we could have selected the same option repeatedly until it was visible.
Windows Mobile: This can also be performed here by also going to the 'Record' menu, but it is not necessary in this particular case.
Desktop: Feature not available.

39. So now we can start defining the Popup Value fields. Select the field 'Type' by pressing the field name. This will bring up a Popup list with the current Popup choices. In this case, since we haven't defined any, the only choices are 'No Value' and 'Edit Popup List'. Select 'Edit Popup List' by tapping it.
Desktop: You must click on the button beside the 'Type' text.

40. We are now shown the Popup values for the 'Type' field. As you can see the screen is currently blank. Press the 'New' button to add a new popup.

41. Now write in the first type of restaurant food, perhaps 'Mexican'. Then press the 'OK' button.

42. You will now see the choice for 'Mexican' available. Let's add another by pressing the 'New' button again and writing in 'American'. Then press 'OK' again.

43. You should now see that Mexican is above American in the list. The order of the list, by default, is to put newer items to the bottom. It would probably be better to have this particular list alphabetized, so let's check the 'Sort Popups' option at the bottom of the list. Now the list should appear alphabetized.

44. Continue adding choices for 'Chinese', 'Italian', and 'Japanese'. You can do this by pressing the 'New' button, writing in the next value, and then pressing 'OK'. Repeat for each item you need to enter. When you are done you should have all of the choices shown alphabetized in the list. Press the 'OK' button to exit the 'Edit Popup List' screen.

45. Let's do the same for the 'Service' field. Press the field name 'Service' and select the 'Edit Popup List' choice.

46. Now lets add the two choices of 'Sit-Down', 'Fast-Food', and 'Take-Out'. Press the 'New' button and write in 'Sit-Down' and then press 'OK'. Repeat this process for the 'Fast-Food' and 'Take-Out' choices. Then select the 'Sort Popups' checkbox. When you are finished press the 'OK' button.

47. Now we should be ready to add some data to our first record. Let's enter our first record for 'McDonalds'. Go ahead and write in the name 'McDonalds'.
Palm: The cursor should already be blinking in the 'Restaurant Name' field as it is the topmost field of this screen.
Windows Mobile & Desktop: Click on the appropriate text area to begin entering the name.

48. Now select the type of restaurant this is from the 'Type' field popup list. Press the field name 'Type' and select 'American'.

49. Write in the location of your nearest McDonalds on the right of the 'Location' field.

50. Now select the type of service by selecting the 'Service' field name. Choose 'Fast-Food' for this example.

51. I don't believe McDonalds needs/accepts reservations so there is no need to check the 'Reservations?' box!

52. For Phone, you would most likely want to write something like 'N/A' since you probably won't need to call there too often. Otherwise, you could write in the number if you know it.

53. Now the record is complete. If we want to add another new record, we could press the 'New' button again. To return to the 'List View' we could press 'OK'. Let's add one more record before exiting this screen. Press the 'New' button.

54. Lets enter a record for Taco Bell. Write in the restaurant name and then select the 'Type' to be Mexican. Write in the nearest location to you, and select the service to be 'Fast-Food' also.

55. Now lets press the 'OK' button and return to the 'List View'. You should see both McDonalds and Taco Bell and the type of restaurant each of them are. Since there are more fields than visible on the screen here, there should be a right arrow button along the bottom. You may press that to scroll right and see the other fields. When there are fields to the left, the left arrow will show. When there are more than a screen's worth of records, up and down buttons will also be visible here. If we wanted to edit any of these existing records, we could select them by tapping them directly.

56. Lets assume that there were dozens of records already in this database, and that we want to choose a restaurant to eat at for Lunch. We discuss amongst ourselves and decide that we would really like some 'Mexican' food. It might be hard to search among the dozens of records to look for all of the Mexican restaurants that are out there, so lets try using a filter to limit the 'List View' screen.
Palm: Press the 'Tools' button, a popup menu shows up, now tap on the 'Filters' button.
Windows Mobile: Tap on the 'Actions' menu and then 'Filter...'
Desktop: Click on the 'Filters' button.

57. At this point only two unchecked boxes, 'Filter 1 Enabled' and 'Filter 2 Enabled', are visible. There are a total of six filters available per database, use the arrows on the screen to go up or down the filter list. We want to enable one of these filters so we can set it up. Select the check next to 'Filter 1 Enabled'.

58. Now we can see that the filter has a few parameters. There is a 'NOT' indicator to the top right which is not currently selected, and it asks for a field. Currently it is set to look at the 'Restaurant Name'. We would like to set it to filter on the type of restaurant, so select the trigger to the left of 'Restaurant Name' and select 'Type'.

59. Coincidentally, the 'Mexican' choice is already set. Had it not been (Windows Mobile and Desktop), we could have selected it by pressing the 'Value Must Be' selector and choosing from the list. Since it is already set, we can press 'OK' to return to the 'List View'.

60. Now only the Mexican restaurant is visible. This should make it easier to choose among the Mexican restaurants in our area. Notice that there is also a designator to the top right labeled 'Filtered', which is reminding us that we are looking at a filtered view of the data. Lets say that the next day we are all going out to eat again, and we're not sure where to go, but we know we don't want Mexican again. We can set the filter to show everything but 'Mexican'. Go to the 'Edit Filters' screen again.

61. Lets select the 'NOT' button to the top right. This will negate the filter, meaning 'show only the records which don't match this filter'. Press the 'OK' button to see the effects of setting this filter.

62. As you can see Taco Bell is no longer shown, but all of the non-Mexican food entries are visible (in this case, 'McDonalds'!).

63. So now that we have went through the basics of the Filters, lets go back and disable the filter that is set. On the 'Filters' screen select the 'Disable' button at the bottom. We should now see our full list of records again.

64. If our list were very long, we might want to search the list for a specific entry, perhaps to find the location information. Press the 'Find' button to do this.
Windows Mobile: Go to the 'Action' menu and then select 'Find'.

65. We should now see the 'Search Database' screen. Here we can select what to search for, which field(s) to search, and whether our search will disregard the uppercase/lowercase aspects of the text in the records. There is also a designator to specify that a field begins with the value we enter. In our case, we are just wanting to search for Taco Bell, so we can just write 'Taco Bell' in the 'Search For' field and press 'Go'.
Windows Mobile: Press on the 'OK' button to begin the search.

66. Now the Taco Bell entry should be visible and highlighted. We could then select the record with the stylus and see the detailed information for it. If you did select the record, press 'OK' to get back to the 'List View'.

67. Now lets go back and add the 'Rating' field that we omitted previously. This is done in the 'DB Properties'. From this screen you can set different parameters for the database, including alarms, security and encription options, record list views and data input/edit graphical forms. Select 'Fields' button as that's where we gain access to the field types.
Palm: Select the pull down menu (the button to the lower left of the Graffiti area) and select 'DB Properties'. There is also another way to get to these properties from the 'Choose Database' screen, via the 'Properties' mode button.
Windows Mobile & Desktop: Select the 'File' menu then 'DB Properties'.

68. This screen should look familiar as it is just where we left our database when we first created it. In this case, we want to add a new field, so we will have to scroll down the 'Edit Fields' list until we see one field that is not yet named. Select Field 7 from the list.

69. Now let us set up Field 7 to be the 'Rating' field. Write in the name 'Rating' in the 'Field Name' designator. For this field we will want to select between 1 and 4 stars, so we will make this a 'Pop-Up' field type. Select the 'Field Type' trigger and select 'Pop-Up'.

70. Press the 'Edit Popups' button to do so. Previous versions of HanDBase did not allow adding popup entries until the database was created, as of 3.0 it's possible to add entries at any point in time.

71. Lets add our four values by pressing 'New', writing in '*', and pressing 'OK'. Repeat for '**', '***', and '****'. Then press 'OK' to exit the 'Edit Popup' screen.

72. We now return to the 'Edit Field 7' screen. Let's set a default value for this field. This default value will be the starting value of all database records added in the future, but it will not effect the existing records we already have. Select the trigger to the left of the current value, in this case 'No Value'. Select one of the values as a default, perhaps the '**' rating. Then press the 'OK' button to exit this screen.

73. We are now finished editing our database so press the 'OK' button again to return to the 'List View'.

74. Lets go back to the McDonalds record by tapping it. We can now rate McDonalds on a scale of one to four stars. I'm going to choose two stars as my rating, which I will do by selecting the 'Rating' field name and selecting '**'.

75. You may notice a small '+' in the screen. If a '+' is visible, that means that you can advance to the next record by pressing the '+'. Lets do that.
Windows Mobile: There is a green > instead of the '+'.

76. Now you will notice that the plus is gone, and a '-' is there instead. A '-' means that there is a previous record to go to, which in this case is the McDonalds record. The '+' is not visible because there are no records after this record. If there were more records, both the '-' and '+' would be there. These serve as a shortcut to advancing forward and backward records while in the 'Edit Record' screen. Now lets set the rating for Taco Bell. In this example, we will set the rating to three stars, and then press 'OK'.
Windows Mobile: There is a green < instead of the '-'.

77. We are now back at the 'List View' screen. What if we wanted to show the records in this view in an order based on their rating?; We would want to use the sorting features of HanDBase to take advantage of this. Let's scroll to the right using the right arrow button at the bottom right of the screen until we see the rating in the view.
Windows Mobile: Use the scrollbar at the bottom of the screen.
Desktop: Use the scrollbar at the bottom of the window.

78. Now select the field name for 'Rating'. You will get a small popup list with the choices, 'Sort Forward', 'Sort Reverse', 'Field Properties', 'Run Report', and 'Hide Field'. In this case, we would want the highest rated restaurants at the top, so we would sort in reverse. Select the 'Sort Reverse' option and now Taco Bell should appear ahead of McDonalds, since it had a higher rating.
Palm: You may notice a 'Sort' button in the 'Tools' popup menu. This will let you set the same sorting parameters along with some secondary and tertiary sort parameters.
Windows Mobile: Selecting the 'Actions' menu, then 'Sort...' will show a dialog that will allow entry of secondary and teriary sort parameters.
Desktop: Clicking on the 'Sorting' button will allow you to set additional sort parameters.

79. We have now completed this tutorial, and you should have a good understanding of how to design a simple database and use it productively.

Exercises:
1. This database leaves lots of room for improvement!;; One improvement might be to use the link and linked fields to create a child database of various dishes ordered at each restaurant and how well you liked them (see Tutorial III).

2. Another improvement might be to change the 'Type' field to be a series of checkboxes. This would be advantageous because you may have a restaurant that has 'American' and 'Mexican' entrees, and now you would be able to select both.


This article has been viewed 14002 times.

Return to Knowledgebase Home