Help on many-many relationship database solution
I am writing a solution for navigational purposes and I am very new to databases and FileMaker. I need to construct a series of “routes” that is made up of multiple “waypoints”. This is a fairly complex many-many relationship and I am having trouble getting my head around it. I am sure the solution is obvious but I just can’t see it yet. I could create a table for each route but I would end up with hundreds of tables. I would much rather create one table of routes where each route is a record within the table. What I was thinking of using was a portal in each route where the waypoints can be added, deleted and created. The basic information in each table is as per below. Any help would be appreciated.
The Routes Table
· The main parts of the route are a unique identifier, the start and end locations, and a description of the route.
· Each route has multiple waypoints
The Waypoints Table
· Each waypoint has a unique identifier a name, the latitude, longitude and a brief description.
· Each waypoint has data that is normally fixed but, it may be changed at a later time.
· When the waypoint data changes, it must change in all routes.
· Each waypoint will be used within multiple routes.