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 serious 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.

There are many tasks for which single-file databases are completely inadequate. An inventory and sales tracking system, for example, needs 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
notes

If you have a collection of many of the Beatles CDs, you’ll have to type in all those details for each CD you own. That’s a whole lot of repetition.

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
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 minimises effort on your part, it also ensures consistency of information and reduces the chance of introducing errors into the data.

When a single file will do

Having said all that, there are some applications, such as a simple membership list, 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 organisation or club.

You can use any database program you like to create this database. While the details will differ, the principles are the same regardless of the program you use. I’ve included detailed instructions on how to create the Membership database using Microsoft Access 2000 and using Access 2010.

Creating a membership database in Microsoft Access

Let’s get started by creating the database and table structure. We can then step back and analyse what we’ve done.

I’m going to give you detailed directions for creating the membership table using Microsoft Access 2000 and Microsoft Access 2010. The procedures are similar in many ways, with a few variations. You should find it pretty easy to adapt these instructions to any database management program, whether it’s another version of Microsoft Access or another database system altogether, such as FileMaker or Alpha Five.

To create the Members database and table in Microsoft Access 2000:

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. At this point, you’ve created an empty database application. The next step is to add the single table we require for this example.
3. Double-click Create Table In Design View.
4. Type MembershipID 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 MembershipID 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
MembershipExpiry, Date
FeesPaid, Yes/No
AmountPaid, Currency
Committee, Text, 12
Skills, Text, 255

7. Select the MembershipID 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 tblMembers in the Table Name box and click OK.
9. Unlike 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).

To create the membership database and table in Access 2010:

1. Open Access, and in the Available Templates section click Blank Database.
2. In the File Name box, type Members and click Create. You’ll see a Members: Database window on your screen with an automatically generated table (Table 1) listed in the navigation pane on the left with its very basic table structure displayed on the right. We’re going to take this auto-generated table and modify it for our own purposes.
3. Click the View button at the left of the ribbon to switch to Design View.
4. In the Save As dialog box which is displayed, give your table the name tblMembers and click OK. Access creates an empty table and highlights the first field.
5. In the Field Name column, replace ID with MembershipID, then press Tab to move to the Data Type column and select AutoNumber. Hit tab twice more to move down to the next row to create your second field.
6. Type FirstName and leave the Data Type as Text. If you look down in the Field Properties box at the bottom of the window, you’ll see the field size has been set to 255. That’s more than we need, so change the value to 25.
7. Continue adding the fields listed in Step 6 of the instructions for Access 2000, above.
8. Click the MembershipID field in the list and click the Primary Key icon (it looks like a key) on the ribbon. The primary key is a field, or combination of fields, whose value uniquely identifies each record in the table. You might think you should use the member’s name (FirstName plus LastName) as the primary key, but you’ll run into problems if two of your members have the same name, because there’s no way to distinguish those members’ records from one another. Instead, we’re using an auto-numbered MembershipID field; the auto-numbering guarantees a unique ID for each member.
9. Click the Save icon on the Quick Access Toolbar.
10. Click the View icon to switch back to Datasheet View. Datasheet displays a tabular view of your table. You can enter data in this view, but it’s not very convenient or visually appealing (unless you’re an Excel junkie, I guess). Instead, have Access whip up a simple data entry form by clicking the Create tab and then clicking Form. The form will be created in Layout View; to add data, click the View button to switch to Form View. (Note how the View button is context sensitive – when you’re working with a form, it provides form views; when you’re working with a table, it provides table views; and so on.)
11. To save this default form so you can reuse it, click the Save button in the Quick Access Toolbar and name the form frmMembers.

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 analyse 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 (zip) 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, 13th Floor

997 Banks Drive

Bluegum Ridge, VIC, 3999

This breaking down of data into usable components 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 kind 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 specialised form of numeric field), date/time, Boolean (also called Yes/No or True/False), hyperlink, memo (for storing large quantities of text) and pciture/object. Not all database programs support all these data types and our simple data structure uses only four types: text, numeric (including an auto-number field for the key field), 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 postal codes as numbers; that is, we don’t use them in numerical calculations. Because of this, and because of the way databases 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 to create your database, you’ll notice it doesn’t even bother asking you about field sizes. FileMaker manages field size dynamically. 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 dialling 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 or your members/customers may come from more than one country, 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.

Formatting and validation

We’ve created our database with almost no validation or formatting. That is, there are no tests to check whether the data entered in a particular field is the correct sort of data for that field. The only exception is that we’ve made the MembershipID 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 Access 2000, click the Tables button in the Object pane then right-click the Members table and select Design View from the pop-up menu, and then explore the Field Properties displayed at the bottom as you click each field. In Access 2010, close frmMembers (if it’s still open), then right-click tblMembers and select Design View.

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 later articles.

Read the next article in this series: Databasics III: Data entry design