Designing the Database "On Paper"

From NeoWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 00:46, 16 July 2007 (edit)
Lorinda (Talk | contribs)
(continuing with article)
← Previous diff		 Revision as of 01:23, 16 July 2007 (edit) (undo)
Lorinda (Talk | contribs)
(→First Questions)
Next diff →
Line 10: Line 10:
*What do I want to do with the data? What kind of reports do I want to be able generate? *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: :: 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 when I visited Skokie Lagoons on +:::See the data for particular field trips. (e.g. what birds did I see at Skokie Lagoons on April 1, 2007?)
-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 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) :::Produce a life list, a list of all the birds I've seen over my lifetime (with each species of bird listed only once)
Line 22: Line 21:
:For the birders journal, the most natural groupings are: :For the birders journal, the most natural groupings are:
-::Birds+::Birds (one record for each species)
-::Field Trips+::Field Trips (one record for each trip)
-::Locations+::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:
 +#to store data essential to the purpose of your database
 +#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 [http://www.aou.org/checklist/index.php3 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 (List box in form)
 +:Weather (Combo box)
 +:Wind (Combo box)
 +:Temp (as text so 60s, etc. is allowed)
 +:Participants
 +:Comments
 + 
 +Sightings Table
 +:SightingsID
 +:Bird Name (list box from Birds table)
 +:Field Trip ID (list box)
 +:Male (Yes/No)
 +:Female (Yes/No)
 +:Juvinile (Yes/No)
 +:Comments (bird behavior, where cited, etc.)
 +{Note: if tracking sitings for a family a birders, add a yes/no field for each family member)
 + 
 +Locations Table
 +:Location
 +:County (From Combo Box/Table)
 +:State/Province (From Combo Box/Table)
 +:Country (USA Default)
 +:Terrain types (combo box)
 +:Water type

Revision as of 01:23, 16 July 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.

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 (List box in form)
Weather (Combo box)
Wind (Combo box)
Temp (as text so 60s, etc. is allowed)
Participants
Comments

Sightings Table

SightingsID
Bird Name (list box from Birds table)
Field Trip ID (list box)
Male (Yes/No)
Female (Yes/No)
Juvinile (Yes/No)
Comments (bird behavior, where cited, etc.)

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

Locations Table

Location
County (From Combo Box/Table)
State/Province (From Combo Box/Table)
Country (USA Default)
Terrain types (combo box)
Water type
Retrieved from "https://neowiki.neooffice.org/index.php/Designing_the_Database_%22On_Paper%22"
Personal tools