6 Replies Latest reply on Apr 27, 2009 4:34 PM by davidhead

    Setting up a relational database with existing tables (databases)



      Setting up a relational database with existing tables (databases)


      I am using FileMaker Pro 10.  I have already established 3 databases, each of them consisting of 1 table with hundreds of entries in each table.  Each of the 3 tables have one unique field that can serve as a primary key although it is not designated as such, at least that I know of.  Two of the tables have two fields in common. Two of the tables have 1 field in common.  What are the initial steps I take to set up a single relational database joining these three databases (tables)??  

        • 1. Re: Setting up a relational database with existing tables (databases)

          To get a good answer to this question, you really need to provide more specific detail about what you have and what you want to do. Saying "Two of the tables have two fields in common. Two of the tables have 1 field in common." is not very helpful.


          So what are the three tables you have? How do you see them related? What are the relationships?

          When you say "a single relational database", do you mean one file (with three tables)?


          The first step is going to be to import the data and tables into a single file. You can create a new table 'on-the'fly' when you import data. The next step is to establish relationships between the tables.


          I can't really be more specific than that. Sorry. 

          • 2. Re: Setting up a relational database with existing tables (databases)

            Step one, make sure you're reading some good books. 

            Step two, try to keep up with this forum -- can't understand why this wasn't available when I first bought filemaker


            Now, create a new file-sketch of the relational database you want. There's more than one way to approach it (and I wish there was a book that focused only on relationships)


            For starters, just use three tables in one file. Set up all your fields and relationships, et. Then import from each of your three existing files, into each of the newer tables, one at a time.


            Be prepared for things getting mucked up somewhere: if a t isn't crossed and an i isn't dotted, you'll have problems. Keep playing with this without destroying your three original files. Put the three original files in their own directory somewhere, along with an extra copy, and keep them safe and out of the way. Each time you iterate you newer three in one file, make a copy-backup first, and keep in a safe place. 

            • 3. Re: Setting up a relational database with existing tables (databases)

              I'm a biologist that does field surveys and the 3 databases are as follows: Database 1 - Site Records:  sites I survey that contain info on latitude, longitude, elevation, type habitat etc. .  Each site constitutes a record and has a unique site number referred to as the snum;  Database 2 - Field Records: information on the field situation at the time and day I visited a site, i.e. date, time, weather etc. Each time I visit a site, I record a new field record.  Each record has a unique field number referred to as the fnum, but also has the snum so it is linked to the site visited.  Database 3 - Species Records: information on the species found at the site during the survey.  Each species found constitutes a new record in the database and has a unique number associated with it - gnum.  Each species record also has the snum and fnum recorded so it can be linked to a field day record and site record.  

              What do I want to do?  I wish to link the three databases so that I can 1) sort the data in ways that will incorporate info from all three databases, i.e. find all of the sites where a certain species of animal is located or find the dates on which all observations of a species of animal was observed.  2) design a single layout report that shows for each field record (survey), the info for the field record, plus info for the site, plus info on the species observed.

              Actually, I had such a database already designed and working in ACCESS.  But then I switched to the MAC, can't get ACCESS for the MAC, decided to switch to FielMaker and that's where I'm at.  Thus, I have some experience with relational databases, but wasn't sure how to 'get started' with linking the tables in a relational database.  What info you have given me so far has been helpful.

              • 4. Re: Setting up a relational database with existing tables (databases)

                As a developer who works with both Access and FMP, I can point you to a few parallels to help you with the transition.


                Did you ever use the Relationships tool in Access? It's quite similar to using Manage | Database | Relationships in FMP. Both tools enable you to create relationships between tables by using the mouse to drag from the key field in one table to the key field in another.


                In Access, you use the Query Manager to create the record source for a form or report to pull together data from multiple related tables. In FMP, you specify one table for a given layout, but can add fields to that layout from any related or un-related table. The relationship graph from Manage | Database will control how data from each table combines and displays on your layout. In an Access database, you often specify search criteria in your record source's query to limit the data to a particular set of records. In FMP, you perform a find to select a particular set of matching records for a given table and layout. This combination of a "found set" and the relationship graph gives you the same capabilities to select and display data in a form or report that you are likely familiar with when using MS Access.


                • 5. Re: Setting up a relational database with existing tables (databases)
                     Thanks very much.  I think I can work thru it now.  
                  • 6. Re: Setting up a relational database with existing tables (databases)

                    Thank you for sticking with this. Your explanation about what you want o do is excellent. It really does help to help you.


                    The relational structure I see is this:


                    SITE ---< FIELD ---< SPECIES 


                    Where SITE is a site record, FIELD is a field visit record and SPECIES is a Species Seen record.


                    IMHO, you should not need the snum recorded in a species record since this will be linked via the field visit record. But there is no harm in doing so.


                    I think this could be drawn one table further:



                    SITE ---< FIELD ---< OBSERVATION >--- SPECIES


                    Where OBSERVATION is a record of observations of a species at a site; and SPECIES is a species record (species name, common name, image, etc). This way you can look at a species record and see a related list (portal) of all observations of the species, and also a list of all sites where that species has ever been observed. It also removed the repetitive entry of species name data.


                    So where now? First thing is to get all the tables into one file. This will make things a lot easier going forward.