I'm trying to reconstruct a solution that was built by a colleague. Far too many entities were built into the same table that have led to the (now) obvious limitations.

This solution aims to manage route risks for train drivers on railway routes in the UK. The main table contains 10000 + records. At the moment I have to create a script to find all the route risks that apply to a particular route. There are many scripts and I'm unsure of the total number of scripts that you can have in scriptmaker before you eventually hit the limit. Instead of creating and running a script for each route I would like to have a table with each route as seperate record and then see the required data through subsequent related tables.

I have started to seperate the data into tables:

Route

Route Sections

Locations

Each (Route) can have multiple (Route Sections) and each (Route Sections) can have multiple (Route), each (Route Sections) has multiple (Locations).

As the relationship between (Route) and (Route Sections) is many to many do I need a join table between them?

I could duplicate a (Route Section) record for every occasion it is required to relate to a (Route) record but I'm feel I'm right in thinking that this isn't necessary.

Apologies for the simple help request but I'm anxious to start on the right foot with firm foundations.

As the relationship between (Route) and (Route Sections) is many to many do I need a join table between them?

As a rule, yes. In some cases, you can get by without one - for example, you could have a checkbox field in Routes to select the sections that make up the route. But this shortcut has many limitations. Here, you won't even be able to tell the correct order of the selected sections.