Designing the Database "On Paper"

From NeoWiki

(Difference between revisions)

Current revision

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. [What follows is a rather informal description by someone who has been essentially self-taught when it comes to database design. For a discussion of database design that covers the formal theory as well as offering an example, see the Sum-it Database Design Course]

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, 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 you 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 variety of ways.

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

Guidelines 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 using 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 AOU_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).
Note: Field naming in this tutorial is not entirely consistent with these guidelines. I began the birding database project before I was aware of them. I have tried to make corrections where possible, but I have not been entirely consistent. In most cases I have replaced spaces with underscores, but in a few cases (e.g. EnglishName) I simply deleted the space. If you encounter discrepancies in particular field names, please send a pm to Lorinda at trinity or, if you have editing privileges on this wiki, leave a note on her talk page.

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?
If certain fields will usually (but not always) have a certain value (e.g. State, Country, etc.) make note of a default value to be entered when defining fields and creating the table.
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 AOU_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, JohnVoice, JohnSight. I also discovered that it was handy to have a GuestVoice and a GuestSight field. That way if I am birding with a group I can add sightings by other members of the group. This is helpful for tracking information about birds seen at particular locations, for example.)

Locations Table

Location_ID (primary key; integer field type, autovalue)

Location_Name (relates to Field_Trips table)

City

County

StateOrProvince (default value to your state)

Country (default value to your country)

Terrain_Type

Water_Type

Comments (memo(longvarchar))

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.

 {{TBBA Header}}
-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.
+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.  [What follows is a rather informal description by someone who has been essentially self-taught when it comes to database design.  For a discussion of database design that covers the formal theory as well as offering an example, see [http://www.sum-it.nl/cursus/dbdesign/english/index.php3 the Sum-it Database Design  Course]]
 ==First Questions==
 *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, 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)
 *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.
+: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 you 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 variety 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.
+:Figuring 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  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:
 ::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.  Some things to keep in mind with Field and Table names:
+Once you have given thought to these questions, you are ready to beginning planning what tables and fields you need.
+===Guidelines 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 easily understandable by anyone using 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 BirdID, SightingsID, etc., so it is clear in the field name what table it relates to.
+*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 AOU_ID, Sightings_ID, etc., so it is clear in the field name what table it relates to.
-===Planning Essential Tables===
+<br>'''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 [http://en.wikipedia.org/wiki/Database_Normalization Database Normalization].  (The book is easier reading than the Wikipedia article).
-Make a the tables you need.  Include the names of all the fields that need to be included in each table.
+<br>
-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.
+'''Note:''' Field naming in this tutorial is not entirely consistent with these guidelines.  I began the birding database project before I was aware of them. I have tried to make corrections where possible, but I have not been entirely consistent.  In most cases I have replaced spaces with underscores, but in a few cases (e.g. EnglishName) I simply deleted the space.  If you encounter discrepancies in particular field names, please send a pm to [[User:Lorinda|Lorinda]] at [http://trinity.neooffice.org trinity] or, if you have editing privileges on this wiki, leave a note on her [[User talk:Lorinda|talk page]].
-For each field, decide what type of field it needs to be (text, date, number, yes/no, etc.).
-Once you have your initial list, ask yourself these questions decide how the tables will relate to each other.  Where are the common fields between the tables?
+===Making a Table and Field List===
-'''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 [http://en.wikipedia.org/wiki/Database_Normalization Database Normalization].  (The book is easier reading than the Wikipedia article).
+Now make a list of the tables you need.
+:*Include the names of all the fields that need to be included in each table.
-Here is the planning list for the birder's journal's essential tables:
+:*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?
+:*If certain fields will usually (but not always) have a certain value (e.g. State, Country, etc.) make note of a default value to be entered when defining fields and creating the table.
+:*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 [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.
+:I added an AOU_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]].)
+:(For more information on using this spreadsheet as the basis of a table, see [[Creating a Table From a Spreadsheet]].)
-Field Trips Table
+Field_Trips Table
-:FieldTripID
+: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
-:SightingsID
+:Sightings_ID (primary key; integer field type, autovalue)
-:Bird Name (relates to Birds Table)
+: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)
+:('''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, JohnVoice, JohnSight.  I also discovered that it was handy to have a GuestVoice and a GuestSight field.  That way if I am birding with a group I can add sightings by other members of the group.  This is helpful for tracking information about birds seen at particular locations, for example.)
 Locations Table
-:LocationName (relates to Field Trips table)
+:Location_ID (primary key; integer field type, autovalue)
+:Location_Name (relates to Field_Trips table)
+:City
 :County
-:State/Province
+:StateOrProvince (default value to your state)
-:Country
+:Country (default value to your country)
-:Terrain_type
+:Terrain_Type
-:Water_type
+:Water_Type
+:Comments (memo(longvarchar))
-''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.''
+<br><br>
+'''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.
 <!-- Removed because I learned a better way to do this
 ==Related External Links==
+* [http://www.sum-it.nl/cursus/dbdesign/english/index.php3 the Sum-it Database Design Course]
 * [http://www.oooforum.org/forum/viewtopic.phtml?p=108553#108553 Books on database design] from OOoForum.org<br>
 * [http://www.oooforum.org/forum/viewtopic.phtml?t=60484 OpenOffice.org Forum discussion on planning a database]<br>

Designing the Database "On Paper"

From NeoWiki

Current revision

Contents

First Questions

Planning Tables and Fields

Guidelines 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