Designing the Database "On Paper"

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 01:13, 24 August 2007 (edit)
Lorinda (Talk | contribs)
(→Related External Links - add links)
← Previous diff		 Revision as of 00:39, 12 September 2007 (edit) (undo)
Lorinda (Talk | contribs)
(→Planning Essential Tables - correct sightings fields)
Next diff →
Line 62: Line 62:
Sightings Table Sightings Table
:SightingsID :SightingsID
-:Bird Name (relates to Birds Table)+:Bird Name
 +:Bird ID (relates to Birds Table)
:Field Trip ID (relates to Field Trips Table) :Field Trip ID (relates to Field Trips Table)
:Male (Yes/No) :Male (Yes/No)

Revision as of 00:39, 12 September 2007

This article is part of the To Base and Back Again tutorial series.

Before actually creating a new Database document in NeoOffice, it is important to think through what sorts of data you will be storing and how you need to access it. Planning out your database on paper ahead of time will save you hours of frustration and restructuring later.

Contents

First Questions

Begin planning by asking yourself these questions:

  • What kind of data do I need to store?
For the birder's journal, the data we need includes birds, dates of sightings, locations, information about field trips, etc.
  • What do I want to do with the data? What kind of reports do I want to be able generate?
For the birder's journal, the we want to be able to:
See the data for particular field trips. (e.g. what birds did I see at Skokie Lagoons on April 1, 2007?)
Produce a list of all the birds seen at a particular location over time. (e.g. all the birds I've ever seen at Skokie Lagoons)
Produce a life list, a list of all the birds I've seen over my lifetime (with each species of bird listed only once)
Perhaps even filter by location and weather conditions.
  • How should the data be grouped
Simple linear databases (like the database in ClarisWorks/Appleworks, for example) only allowed you to work with one table or group of data in a data base. This limited what kind of data to could store and how it could relate. Relational databases like Base (and File Maker Pro and Helix RADE for Classic), allow you to work with multiple tables of relatively independent data that can relate to each other in a variet of ways.

F:iguring out how to group your data, or what tables you need, is an essential. Think carefully about what data is closely tied together, and what data needs to be independent of other groups (or tables) of data. In a simple database to catalog music, for example, you might want to three groups: Albums (Name, date, etc.), Songs, and Composers. These should be separate because more an Album contains more than one song, and Composers(or Artists) produce more than one Album.

For the birders journal, the most natural groupings are:
Birds (one record for each species)
Field Trips (one record for each trip)
Locations (one record for each location)
Sightings (one record for each bird seen)


Planning Tables and Fields

Once you have given thought to these questions, you are ready to beginning planning what tables and fields you need. Tables serve at least two different purposes:

  1. to store data essential to the purpose of your database
  2. to make consistent data entry easier.

For the purposes of this tutorial, I have broken down tables in to two categories: "Essential Tables," those tables containing the data essential to the purpose of your database, and "Convenience Tables," those tables whose existence assists in consistent and easier data entry. These terms are my creation, so you are unlikely to find them used elsewhere.

Planning Essential Tables

Make a list of each Essential Table you need. Include the names of all the fields that need to be included in each table. Make sure to include a "Primary Key" in each table. Primary Keys are fields whose entry is unique for each record. Most often, it is best for the Primary Key to be an number (interger) that Base sets itself. But sometimes it makes sense for the primary key to be something else. For each field, decide what type of field it needs to be (text, date, number, yes/no, etc.).

Once you have your initial list, decided how the tables will relate to each other. Where are the common fields between the tables?

Here is the planning list for the birder's journal's essential tables:

Birds Table

I used the AOU list available as an xls file from the American Ornithologists' Union's website Fields are determined by the column headers.
I added an ID column/Field to use as the primary key.

(For more information on using this spreadsheet as the basis of a table, see Creating a Table From a Spreadsheet.)

Field Trips Table

FieldTripID
Date
Start Time
End Time
Location (relates to Locations table)
Weather
Wind
Temp (as text so 60s, etc. is allowed)
Participants
Comments

Sightings Table

SightingsID
Bird Name
Bird ID (relates to Birds Table)
Field Trip ID (relates to Field Trips Table)
Male (Yes/No)
Female (Yes/No)
Juvenile (Yes/No)
Comments (bird behavior, where cited, etc.)

{Note: if tracking sightings for a family of birders, add a yes/no field for each family member)

Locations Table

Location (relates to Field Trips table)
County
State/Province
Country
Terrain type
Water type

Note that there is no direct relationship between the Field Trips Table and the Birds Table nor is there a direct relationship between the Locations Table and the Sightings Table or the Locations Table and Birds Table. But these tables can be related together in Queries, as long as the Query includes third table that does relate to the 'unrelated' tables. For example, while the Sightings Table and the Locations Table do not have a direct connection, the Field Trips table can act as an intermediary, as it relates to both of these tables.

Planning Convenience Tables

I am calling "Convenience Tables" those tables whose purpose is primarily to make data entry easier and more consistent. Once forms have been created, these tables can be used to create Combo Boxes or List Boxes, allowing users to choose, for example, the terrian type from a pre-defined list.

For the birder's journal, the following "convenience tables" are planned. In this example, the tables contain only one field (plus, potentially, a primary key):

Weather
Wind
Terrain Types
States/Provinces
Water Types
Counties/Provinces
Country


Creating the Database Document

It is finally time to create a new database document.

Under the File menu, choose New and then Database
In the Database Wizard window, make sure the Create a new database radio button is selected.
Click Next
Make sure that the Yes, register the database for me and Open the Database for Editing settings are selected.
Click Finish

The Next Steps

You are now ready to begin creating your tables. Table creation is covered in the following articles:

Creating a Table From a Spreadsheet
Creating a Table in Design View

Be sure to keep your notes about the design of your database. More decisions will need to be made later. It will be easier to make those decisions if you can refer to this list of tables and fields.

Related External Links


This article in other languages: Français Italiano
Retrieved from "https://neowiki.neooffice.org/index.php/Designing_the_Database_%22On_Paper%22"
Personal tools