Designing the Database "On Paper"

From NeoWiki

(Difference between revisions)

Revision as of 21:31, 7 October 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.

1 First Questions
2 Planning Tables and Fields
3 Creating the Database Document
4 The Next Steps
5 Related External Links

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.

Guidlines for Field and Table names

Field names must begin with a letter and must only contain letters, numbers, and underscores. No spaces or other characters.
Table names can contain spaces, but the standard is to use underscores instead.
Field names should be easily understandable by anyone useing the database
Field names should be unique across the whole database. You can have a field called "ID" in each table, and the database will work (because Neo keeps track of what table it comes from), but it is more confusing for the User. It is better to name these fields Bird_ID, Sightings_ID, etc., so it is clear in the field name what table it relates to.

Note: For more guidelines for creating good tables, see the book SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL (2nd Edition) by John L. Viescas and Michael J. Hernandez and/or the Wikipedia article on Database Normalization. (The book is easier reading than the Wikipedia article).

Making a Table and Field List

Now make a list of the tables 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.).
Decide how the table will relate to each other. What are the common fields?
Double check to make sure you have followed the guidelines for naming conventions discussed above

Finished Planning List for the Birding Database

Unless otherwise noted, field type is text.

Birds Table (named AOU_Birdlist)

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 Bird_ID column/Field to use as the primary key. (integer field type)

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

Field_Trips Table

FieldTrip_ID (primary key; integer field type, autovalue)

Date (date field type)

Start_Time (time field type)

End_Time (time field type)

Field_Trip_Location (relates to Locations table)

Weather

Wind

Temp (as text so 60s, etc. is allowed)

Participants

Comments

Sightings Table

Sightings_ID (primary key; integer field type, autovalue)

Bird_Name (relates to Birds Table)

Sightings_Field_Trip_ID (relates to Field_Trips Table; integer field type)

Male (Yes/No field type)

Female (Yes/No field type)

Juvenile (Yes/No field type)

Voice (Yes/No field type) - bird identified by its voice

Sight (Yes/No field type) - bird identified by sight.

Comments (bird behavior, where cited, etc.)

(Note: if tracking sightings for a family of birders, add two yes/no fields for each family member, one for voice and one for sight. e.g. JaneVoice, JaneSight, John Voice, JohnSight)

Locations Table

Location_ID (primary key; integer field type, autovalue)

Location_Name (relates to Field_Trips table)

County

State/Province (default value to your state)

Country (default value to your country)

Terrain_Type

Water_Type

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

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.

 ===Finished Planning List for the Birding Database===
+Unless otherwise noted, field type is text.
 Birds Table (named AOU_Birdlist)
 :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 Bird_ID column/Field to use as the primary key.
+:I added an Bird_ID column/Field to use as the primary key. (integer field type)
 :(For more information on using this spreadsheet as the basis of a table, see [[Creating a Table From a Spreadsheet]].)
 Field_Trips Table
-:FieldTrip_ID
+:FieldTrip_ID (primary key; integer field type, autovalue)
-:Date
+:Date (date field type)
-:Start_Time
+:Start_Time (time field type)
-:End_Time
+:End_Time (time field type)
 :Field_Trip_Location (relates to Locations table)
 :Weather
 Sightings Table
-:Sightings_ID
+:Sightings_ID (primary key; integer field type, autovalue)
 :Bird_Name (relates to Birds Table)
-:Sightings_Field_Trip_ID (relates to Field_Trips Table)
+:Sightings_Field_Trip_ID (relates to Field_Trips Table; integer field type)
-:Male (Yes/No)
+:Male (Yes/No field type)
-:Female (Yes/No)
+:Female (Yes/No field type)
-:Juvenile (Yes/No)
+:Juvenile (Yes/No field type)
-:Voice (Yes/No) - bird identified by its voice
+:Voice (Yes/No field type) - bird identified by its voice
-:Sight (Yes/No) - bird identified by sight.
+:Sight (Yes/No field type) - bird identified by sight.
 :Comments (bird behavior, where cited, etc.)
 :('''Note:'''  if tracking sightings for a family of birders, add two yes/no fields for each family member, one for voice and one for sight. e.g. JaneVoice, JaneSight, John Voice, JohnSight)
 Locations Table
-:Location_ID
+:Location_ID (primary key; integer field type, autovalue)
 :Location_Name (relates to Field_Trips table)
 :County
-:State/Province
+:State/Province (default value to your state)
-:Country
+:Country (default value to your country)
 :Terrain_Type
 :Water_Type

Designing the Database "On Paper"

From NeoWiki

Revision as of 21:31, 7 October 2007

Contents

First Questions

Planning Tables and Fields

Guidlines for Field and Table names

Making a Table and Field List

Finished Planning List for the Birding Database

Creating the Database Document

The Next Steps

Related External Links

Views

Personal tools

Navigation

Search

Toolbox