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'.
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,
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?
Thanks for your reply.
That's exactly what I want. A log entry per group, trek and trail. Not just group & trek. Thank you.
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?
Thanks for your reply.
I want to reuse the trek and its associated trails for multiple groups. Thank you very much for your help!
Can you provide an example of what you want your report to look like?