home

beginners menu


 

Databasics II: Creating your first database

This tutorial guides you through building a simple, single-file database. In a single-file database, also known as a flat-file database, you put all your information into a single table. This is the simplest form of database to create, but it has some limitations and disadvantages. The most important of these limitations are that single-table databases are incapable of representing some real-world data and they create more work when it comes to data entry.

Imagine trying to create a single-table database to track inventory and sales. It simply wouldn’t work. You’d need at least two tables and, in fact, many more (inventory, suppliers, sales, customers, and so on) to do the job.

Single-table inadequacies

When it comes to data entry, single-file databases often create work for you. For instance, if you create a flat-file database to catalogue your CDs, you have to put all the details, including the artist information, into one table. Say you want to include information such as the artist/band’s recording label, band members, a discography and artist notes. How’s that going to work? Your table structure might look something like this:

CD name
CD date
genre
tracks
artist/band name
band members
recording label
discography
notes

For each Beatles CD you own, you’ll have to type in all those details. That means you’ll have to type the names of all of the Beatles’ releases repeatedly.

Multi-table flexibility

On the other hand, if you use a multi-file relational database, you can store the CD details (name, date, tracks and so on) in a CD table and store the artist details once in an Artist table. Your CD table will look like this:

CD name
CD date
genre
tracks
artist/band name

Your Artist table will look like this:

artist/band name
band members
recording label
discography
notes

You then link the two tables using the artist/band name field (that’s why it’s called a relational database – you define relationships between the tables) and enter the artist information once only. Each time you add a subsequent Beatles CD to your collection, you type The Beatles in the artist field and the database looks up the other details for you. It not only minimizes effort on your part, it also ensures consistency of information and minimizes the chance of introducing errors into the data.

When a single file will do

Having said all that, there are some applications where a single-file database is all you need. Also, if you’re dealing with small quantities of information, it may not be worth your effort to design a relational database. You may prefer to create a simple all-in-one table and put up with any additional typing this necessitates.

So, in this tutorial we’re going to focus on creating a flat-file membership database from scratch. It should be generic enough that you can use it as a basis for a membership application for your own organization or club.

You can use the copy of Lotus Approach 97 we included on last month’s cover CD to create your database or any other database program you like. The details will differ, but the principles are the same regardless of the program you use. I’ll also include instructions on how to create the Membership database using FileMaker Pro 5 and Access 2000.

Creating a membership database

Let’s get started by creating the table structure. We can then step back and analyze what we’ve done. I’ll step you through the process in three popular database programs, Lotus Approach 97, FileMaker Pro 5 and Microsoft Access 2000. If you have a recent version of any of these programs, you should find the following instructions will work pretty well for you.

Creating the table in Lotus Approach

Here’s how to proceed using Approach 97:

1. Start up Approach and, in the Welcome to Lotus Approach dialog, click the Create A New File Using A SmartMaster tab, select Blank Database and click OK.

2. In the New dialog, name the database Members and click Create.

Creating a membership table in Approach

Figure 1. Creating the table structure in Approach. (Click for a larger image.)

3. In the first row of the Creating New Database dialog, type MembershipNumber, press Tab twice, type 5 in the Size box and click Tab once more to move to the next row.

4. Continue adding the following fields (name, data type and size):
First name, Text, 25
Last name, Text, 25
MailingTitle, Text, 10
StreetAddress, Text, 30
Suburb, Text, 30
State, Text, 3
PostalCode, Text, 4
WorkPhone, Text, 20
HomePhone, Text, 20
ListWorkPhone, Boolean
ListHomePhone, Boolean
Email, Text, 60
Joined, Date
Active, Boolean
MembershipType, Text, 9
MembershipExpires, Date
FeesPaid, Boolean
AmountPaid, Numeric, 5.2
Committee, Text, 12
Skills, Text, 254

5. Highlight the MembershipNumber field you created, click the Options button, click the Validation tab and put a tick beside Unique. This will ensure that each member has a unique membership number. 

6. Click OK to create the database. Approach will display your fields on a data entry form titled Blank Database.

A standard data entry form

Figure 2. A standard data entry form in Approach. (Click for a larger image.)

A more user-friendly, customised data entry form

Figure 3. A customized data entry form in Approach. (Click for a larger image.)

Creating the table in FileMaker

To create the Members table in FileMaker Pro 5:

1. Start FileMaker and in the Open New or Existing File dialog box type Members in the File Name box and click Open. When FileMaker prompts you to create the file, click Yes.

2. In the Field Name box type MembershipNumber and press Enter. Unlike Approach and Access, you don’t need to specify the field length in FileMaker.

3. Add the following fields in similar fashion:
FirstName
LastName
MailingTitle
StreetAddress
Suburb
State
PostalCode
WorkPhone
HomePhone
ListWorkPhone
ListHomePhone
Email
Joined (click Date in the Type section before pressing Enter)
Active (reselect Text in the Type section before pressing Enter)
MembershipType
MembershipExpires (click Date in the Type section and press Enter)
FeesPaid (reselect Text in the Type section and press Enter)
AmountPaid (click Number in the Type section and press Enter)
Committee (reselect Text in the Type section)
Skills

4. Select MembershipNumber in the field list, click the Options button, select the Validation tab and put a tick beside Unique.

5. Click Done. FileMaker will display your fields in a default data-entry form.

Creating the table in Access

To create the Members table in Microsoft Access 2000 (the process is fairly similar in Access 97):

1. Open Access and in the initial dialog box, select Blank Access Database and click OK.

2. In the File New Database dialog, type Members in the File Name box and click Create. You’ll see a Members: Database window on your screen with Tables selected in the Objects panel.

3. Double-click Create Table In Design View.

4. Type MembershipNumber in the Field Name box and press Tab. In the Field Size box in the Field Properties section at the bottom of the window, replace the 50 with 5.

5. Click in the row beneath MembershipNumber and type FirstName, press Tab and change the Field Size value to 25.

6. Continue adding the following fields:
LastName, Text (data type), 25 (field size)
MailingTitle, Text, 10
StreetAddress, Text, 30
Suburb, Text, 30
State, Text, 3
PostalCode, Text, 4
WorkPhone, Text, 20
HomePhone, Text, 20
ListWorkPhone, select Yes/No in the Data Type column (a shortcut is to tab to the field and press Y), then Tab twice to move to the next row.
ListHomePhone, Yes/No data type
Email, Hyperlink data type
Joined, Date data type
Active, Yes/No
MembershipType, Text, 9
Membership expires, Date
FeesPaid, Yes/No
AmountPaid, Currency
Committee, Text, 12
Skills, Text, 255

7. Select the MembershipNumber field in the list and click the Primary Key icon (it looks like a little key) on the toolbar.

8. Click the Close box for the Table 1: Table window and, when prompted to save the changes, click Yes, type Members in the Table Name box and click OK.

9. Unlike the Approach and FileMaker, Access doesn’t automatically display a default data entry screen. You can do so by clicking Members in the Members: Database window and then clicking the New Object: AutoForm button on the toolbar (it looks like a form with a lightning bolt across it).

What have we done?

You now have a Members table ready for you to enter information. Admittedly, the default data entry forms created by the database programs aren’t much to look at and we haven’t taken advantage of any shortcuts and special features that make databases easy to use. But it’s a start.

Before we take the next steps – cleaning up the data entry form and thinking about improving the data structure – let’s analyze what we’ve done so far.

To create our table, we defined a field for each item of information we want to store. The choice of fields was mostly a commonsense matter, although a couple bear closer examination.

We broke each member’s name into two parts: first name and last name. This allows us to sort and search our database using either of those fields.

Similarly, we broke the address into component parts to give us a structure which lets us sort, search and group members by state, postcode and suburb. This division will also make it easy when we want to print address labels or envelopes. If you put the address all in one field, it becomes a very difficult task to create mailing labels which have the name on the first line, the address on the second line, and so on. It’s sometimes desirable to break the address down even further, and include two street address lines (address line 1 and address line 2). This allows for addresses such as:

Suite 123, 14th Floor
997 Banks Drive
Bluegum Ridge, VIC, 3999

This breaking down of data into usable component parts is a vital step in creating a useful database.

Data types

Each field we’ve included has its own data type. The data type defines the type of information which may be stored in a field. The majority of our fields are text data type. Text fields can hold alphanumeric information, including letters, numbers, spaces and punctuation marks.

Other common data types include numeric (also known as number), currency (a specialized form of numeric field), date/time, Boolean (also called Yes/No), hyperlink, memo (for storing large quantities of text) and picture/object. Not all database programs support all these data types and our simple data structure uses only four types: text, numeric, Boolean and date.

Boolean fields are logical fields which may contain either a 0 or 1, or another binary pair such as True/False or Yes/No. They’re useful when you want Yes/No answers to questions. We’ve used them in our database in the ListHomePhone, ListWorkPhone, active and FeesPaid fields to answer the questions “Should I list the member’s home/work number when printing reports?”, “Is this an active member?” and “Are the member’s fees up to date?”

Notice how we’ve used the text data type for both the phone numbers and postal codes. Why not use the numeric data type?

With phone numbers, the answer’s obvious: These numbers frequently contain non-numeric characters, such as parentheses and hyphens: (02) 4782-0000 for example. By using text data type we allow for such characters, as well as allowing for additional details such as ext 34 (although you could, if you wish, create an additional field called WorkExtension to handle extension numbers).

As for the postcode, although this field will contain only numbers, we don’t treat postcodes as numbers, that is, use them in numerical calculations. Because of this, and because of the way database sort and format numbers differently from text, always store this type of information in a text field.

Field sizes

Why is each field the size it is?

The most important thing about the size of your fields is that you make them big enough to accommodate the largest possible piece of information they will need to store.

With names and addresses, be generous. You may not be able to imagine a family name longer that 15 characters, but Ms Clarissa Worthington-Wettersley is going to be really annoyed when all her club correspondence is addressed to Ms Worthington-Wet.

As for fields where you’re not quite sure how much info you need to store, such as the Skills field we’ve included, one approach is to allow the maximum permissible size for a text field, which is usually around 254 or 255 characters. Another approach is to use a memo data type. This type of field allows for text of almost any length to be entered.

If you’re using FileMaker, you’ll notice it doesn’t even bother asking you about field sizes. FileMaker manages field size dynamically. You can store about 65,000 characters in any text field. When you’re designing your database, you can use formatting options on data entry forms (called Layouts in FileMaker) to limit data entry in fields.

Allowing for international variations

You may have noticed that the postal code field in our table allows for only four characters. That’s perfect for Australian postcodes, but won’t work for the United States and many other countries.

You’ll need to adjust the size depending on the requirements of your region. If you’re creating a table that must accommodate US zip codes, you must decide whether a 5-character field will do, or whether you need to make the field 10 characters wide to allow for five-four zips (include the extra character to allow for the hyphen).

With phone numbers you should allow for formatting characters (including parentheses, plus signs and hyphens), extensions, international dialing codes and so on. I chose 20 characters as it handles most of these concerns.

If your database may be used in more than one locale, keep in mind such regional differences. For instance, two-character state codes work for the US, but not for Australia and many other countries. The same goes for phone numbers: Be careful to avoid truncated and specially formatted phone fields that don’t work internationally.

A word about field names

You’ll notice that many of the field names are compound words, such as FirstName and MembershipType. Why is that so? Why not make them more readable by including spaces?

Well, although all three database programs allow you to create field names which contain spaces, if you end up using your database with scripting tools such as JavaScript or with advanced data access technologies such as ADO (ActiveX Data Objects), you’ll find spaces in fieldnames are unacceptable. So, even if you don’t think you’ll end up using such esoteric, it pays to allow for the possibility by eliminating spaces.

When it comes to creating data entry forms, you can always change the field name which is displayed above a data entry box by adjusting its caption, something we’ll look at later.

Formatting and validation

We’ve created our database with almost no validation or formatting. The only exception is that we’ve made the MembershipNumber unique (in Access, we’ve turned it into a key field, which has the same effect).

Each database program lets you limit and control the type of data entered into each field and check that it matches permissible values. You might like to explore some of the options available to you.

In Approach, select Field Definition from the Create Menu to modify your database structure and check out the options. In FileMaker, select Define Fields from the File Menu and in Access, click the Tables button in the Object pane then right-click the Members table and select Design View from the pop-up menu.

Much of the data validation and automated data entry can be handled via the data form itself or by transforming our flat-file table into a relational database. We’ll look at both of these options in coming tutorials.



top home beginners menu