7 Replies Latest reply on Jul 22, 2009 3:47 PM by comment_1

    Related Tables

    Alex_Riva

      Title

      Related Tables

      Post

      Hello,

      I am building a database to monitor trail usage inside a park. The main tables are:

       

      1. Trekking (which are small trails)

      2. Trail (consisted of many trekkings)

      3. Trail Log (a log is created each time a group of people hikes a trail)

       

      Since a trail is consisted of many trekkings and one trekking can be in more than one trail (n to n relationship), I used another table to link them named 'Trail_Composition'. Aditionaly, since a log can have many trails and a trail can be in many logs, I also created another table to link them named 'Log_Composition'. The file is linked like this:

       

      Trekking ----< Trail_Composition > ---- Trail ------< Log Composition >----- Log

       

      I created a portal inside the Log layout in order to insert the trail information and everything is working fine.

       

      What I don't know how to do is how to create a report based on the Log table that shows all Trekkings that were used. I could easily create a report based on trail usage, or make the trakking information to be displayed inside a portal. But this is not the solution I'm looking for. Can you help me?

       

      Thank you,

        • 1. Re: Related Tables
          etripoli
             I'm not sure I follow your normalization of the data.  If you start at the end, the report of the log table, you should have a list of records with the Trek Name, the Trail Name, possibly a Group Name, and maybe a start and stop time.  From that, I see at least a second table for the trails, with an ID, name, location, difficulty, description, etc.  You may not need to normalize further, unless there is specific information related to a 'trek', or the 'groups'.
          • 2. Re: Related Tables
            Alex_Riva
              

            Yes, if I could only have 3 tables, the solution would be simple. I would only have:

             

            Trail ----< Log Composition >---- Log

             

            But the problem is that we have some trekkings being shared among several trails, and we want to monitor the trekking usage (through the Log table). How can we do it? Thank you,

            • 3. Re: Related Tables
              etripoli
                

              If I understand correctly, a trail can be defined on a map, with a start & stop point.  A trek, is a combination of trails, starting with one trail, ending with another.  In this case, for proper normalization, you'll create a table that contains one record per trek, and a trek_trails table, that contains the trek, the trail, and the order of the trail within the trek.

               

              Next question, do you want a log entry per group, trek, and trail?  Or just group & trek?

              • 4. Re: Related Tables
                Alex_Riva
                  

                Hello Etripoli,

                 

                Thanks for your reply.

                 

                That's exactly what I want. A log entry per group, trek and trail. Not just group & trek. Thank you.

                • 5. Re: Related Tables
                  etripoli
                    

                  So, with these tables:

                   

                  Treks - 1 record per trek, to be able to select a predefined trek for groups

                  Trails - 1 record per trail, with pertinent information

                  Trek Composition - 1 record per Trek & Trail combination, with an order field to be able to list the trails used in order

                  Log - 1 record per group, trek, and trail

                   

                  I have to say, having to create a record for each trail that a group traverses in a trek seems a bit redundant.  It would make sense if there were no standard 'treks', so maybe that's the case.  If so, I would start with a layout where you define a trek, name the group assigned to it, pick trails from a portal based on Trek Composition, and place fields from Log on the same portal, to enter information about how long it took the group to hike/walk/bike each trail.

                   

                  Then, your report would be based on the Log table, and you could use subsummaries to group the records together by Group, Trek, and Trail.  Is a group & trek synonymous, or do you plan on reusing treks (and their associated trails) for multiple groups?

                  • 6. Re: Related Tables
                    Alex_Riva
                      

                    Hi,

                     

                    Thanks for your reply.

                     

                    I want to reuse the trek and its associated trails for multiple groups. Thank you very much for your help!

                    • 7. Re: Related Tables
                      comment_1
                         Can you provide an example of what you want your report to look like?