How to build a single file 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.

When a single file will do

Having said all that however, there are some applications where a single-file database is all you need. For example, 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.

---

In this tutorial we’re going to use Access to create a flat-file membership database from scratch.

Creating the table

1. Open Access and in the initial dialog box, select Blank Access Database and click OK. (Clicking the other option will give you the opportunity to utilize some of the premade databases that Access offers - you can use these as are or modify them to suit your needs)

2. In the File New Database dialog, type Members in the File Name box (this is where you name your database file). Navigate to where you want to store your database and then click Create.

You’ll then see a Members: Database window on your screen with Tables selected in the Objects panel.

3. Double-click Create Table In Design View which will allow you to build your table from scratch by precisely defining fields. (So what are the other options for? Create table by using wizard gives you access to an interactive wizard which will ask you some questions and then create your table for you, Create table by entering data opens a spreadsheet like window into which you can enter data automatically. Both of these options will require you to then view your table in Design View to define your fields more precisely. This will be further explained later on.)

4. We will now start creating the fields for our table. Click in the first box under Field Name. Type MembershipNumber in the Field Name box. Either hit the tab key or use your mouse to click in the next box under Data Type. Choose Text from the pull down menu. In the Field Size box in the Field Properties section at the bottom of the window, replace the 50 with 5.

5. Follow the same procedure above to add the next field. Click in the row beneath MembershipNumber and type FirstName, in the next field under data type, choose text. Change the Field Size value to 25.

6. Continue adding the following fields:

7. Select the MembershipNumber field in the list and right click. Choose "Primary Key". Or select the field and choose the Primary Key icon (it looks like a little key) from the toolbar. This makes MembershipNumber the unique field for this table which means that each record that you enter in your table must have a different MembershipNumber and you will get an error message if you try to enter two records with the same MembershipNumber or enter a record without a MembershipNumber. The primary key is used by the database program to positively identify a record. It is part of the relational characteristic of a database.

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

Access will automatically develop a data entry screen for you for the Members table. Now you can start populating your table by filling out the fields from the data entry screen.

When you close the data entry screen you will be prompted to save the form. Type members in the pop up window and click ok.

So where are these things that we just created?

We just created a table called Members and a data entry form called Members. You can easily close the windows for these objects and reopen them later.

All objects that you create will be available from the same screen. Your window database:members is your link to your database. Closing the database:members window will close your entire database file.

From the database:members window, click on tables in the object column and you will see your Members table listed. Double clicking on the table name will reopen the Members table.

The same is true for the data entry form. Clicking on Forms from the objects column of the database:members window will show you a list of all the forms you have in your database. Double clicking on members will reopen the data entry form that we just saved above.

But my table looks different when I reopen it.

Access gives you different ways to view your objects. Remember when we created the Members table - we had several options on how to create the table. Access still gives you these options for accessing your objects even after they are created.

Design view gives you the screen that we used initially to enter our fields for our table. After you finish creating your table you can go back to viewing the table in design view in order to change the table, ie. add another field, make a field size larger, redefine a field's data type, etc.

Datasheet view gives you your table in a spreadsheet format. This is useful when you want to view many records from your database at the same time. Some also find it useful for entering data into your table instead of using a data entry form.

You can easily and quickly switch between these two views by opening the View pull down menu from the tool bar (shown below).

What have we done?

You now have a Members table ready for you to enter information.

Before we take the next steps – cleaning up the data formatting 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 common sense, 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 province, postal code and city/town. 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.

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 phone numbers. Why not use the numeric data type? The simple rule of thumb when choosing numeric field types to remember is - am I going to be doing calculations with these numbers.

With phone numbers, the answer’s obvious. Besides the fact that phone numbers are not standard numbers, that is they may contain spaces and often have non-numeric characters such as ")" or "-". Also, you would never think of doing numeric calculations on phone numbers such as adding them together.

What about American Postal Codes - text or numeric? Although this field will contain only numbers, we don’t treat postal codes 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.

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 the major 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, you’ll find spaces in fieldnames are unacceptable. So, even if you don’t think you’ll end up using such complex resources, 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.

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 by making it a primary key field. In an upcoming tutorial we will go through how to apply formatting aids to this database.