4 Replies Latest reply on Jan 5, 2010 12:15 PM by daybreak

    creating a new database



      creating a new database


      I am a beginner in databases…I would like to build a database that keeps track of my insect survey work.  I will have different surveys.  Each survey will have different locations that I visit numerous times and I want a list of the dragonfiles or butterflies I observe.  I could see many species on a given day.  I’m confused on how many tables I need.  One for surveys, observations and species?  And then if I list the species in a portal how do I get the list of species out of a portal into a report?   Thanks for listening!  I have FileMaker 10.

        • 1. Re: creating a new database

          You'll probably want to invest in a book on Filemaker or run some tutorials. We'll be glad to help you all we can, but such resources will help you out a lot as you get started.


          You'll need a number of related tables and only you the user/designer can make the final call on exactly how many tables you need. It may help to picture writing down this information in a series of lists with each list limited to a specific type of information. Each such list could become a table in your database.


          I think you'd need at least the following tables for recording observations:


          Species (1 record = 1 insect species)

          Include fields to record names of species and any other species specific info you need.

          Add a speciesID serial number field to make it easier to link a record from this table to a records in a related table.


          Surveys (1 record = 1 specific survery)

          include fields for date of survey and any other pertinent data that describes the entire survey.

          SurveyID (serial number)


          Locations (1 record = 1 location surveyed)

          Include fields to document the specific location. You may find that you need to specify a new location everytime you start a survey, or you may be able to refer to the same location for different surveys. You are the only one that can make that call.

          LocationID (serial number)

          (Note you may be able to use a single table for Surveys and Locations, again that's a call you'd have to make. Either way, the concept here is that a group of location records defines the locations visited during a given survey.)

           Survey_Locations (1 record = 1 location visited during a given survey)

          This is a join table linking locations and Surveys. It needs at least two fields, SurveyID and LocationID. You would use this table if you want to refer to the same location record in more than one survey.

          Observations (1 record = 1 species observed during a given survey)

          ObservationID(serial number)




          Plus any additional fields you need to document the specific observation.

          "if I list the species in a portal how do I get the list of species out of a portal into a report?"

          Each time you define a table, Filemaker adds a layout for that table to your database. You can also create additional layouts and select the table they refer to. Either way, you can create a report based on your portal's table and build the report there. You can perform finds to pull up just the "observation" records you need for your report and display data from related tables' records by adding those fields to your layout.

          • 2. Re: creating a new database

            I have invested in a book and videos and I'm learning as fast as I can! :smileyhappy:


            Thank you very much for your suggestions.  I'm a little confused on why I would have a Survey Location table when I have a Location table?  Sorry for the lack of knowledge here.  It's not as clear as it should be but I'm learning!!



            • 3. Re: creating a new database

              This "join" table may or may not be necessary.


              Here's why you might need it.


              Let's say you want to do a survey for certain Biome.


              You survey 5 locations in one survey

              Alpine meadow #1, GPS coordinates ....

              Manzanita scrub #2, GPS coordinates ....



              In a second survey you might visit some of the same locations, but for a different survey.


              Your survey table documents each survey.

              You might record the date of the survery, the stated purpose, etc.


              Your location table documents each location, where it is, what it is, etc.


              The survey_Location table lists all the locations used for a given survey. It's records might look like this:

              SurveyID       LocationID

              1                  1

              1                  4

              1                  5

              2                  1

              2                  6

              2                  7


              Which would tell you that the location record with LocationID = 1 was used in both survey 1 and survey 2. Your database can use the ID numbers in this table to pull up the details from each of the two tables survey and location.


              This is a fairly advanced concept for a beginner but is a very logical structure for the purpose you specify in your original post.


              A simpler approach that is not as flexible is to asign each location record to one and only one survey record, then there is no need for a Join table. If locations specified in a given survey are rarely the same exact location, this may even be the best approach. The trade off here, is that if you do use the same exact location for more than one survey, you have to make duplicate location records and this will raise issues that the Join table approach avoids.


              Example: If "location 1" is duplicated so that one record is related to Survey 1 and the duplicate is linked to Survey 2, any changes or updates to the data documenting this location will have to be made twice--one to each such record. Such duplication wastes your time and greatly magnifies the chance of data-entry errors.

              • 4. Re: creating a new database

                I understand now...I have definitely surveyed the same location for a different survey, so I will use your suggestion.  I may have more questions but I'll stop now with the questions.  Thank you so much for your help.