While designing the schema for a new application have become high-centered and stuck relating some many-to-many tables. Fresh eyes and suggestions are needed on how to create many-to-many association tables among these five.
Table 'Permits': Contains information about each permit, PK is permit number.
Table 'Parameters': Contains all parameters (physical, chemical, biological, operational) by name and other attributes. This is a record of all parameters over the life of the permit. (Many parameters, many permits.)
Table 'Locations': Contains details about each monitoring location. This is a record of all locations over the life of the permit. (Many locations, many permits.)
Table 'Conditions': Contains permit numbers and which paramenters are to be monitored at which locations and at what frequencies and over what time period. I'm thinking one row per permit that reference the permit number, parameters, locations, frequencies, and active dates. Each location has multiple parameters, and each parameter is monitored at multiple locations. FK references permit number. (Many parameters, many locations, many frequencies for each of many permits.)
Table 'Monitoring': Contains columns for date, location, parameter, quantity, and other attributes. FK references permit number. (Many rows for each permit.)
While you may require additional modelling tables to support your user interface (i.e., picking valid combinations of PeLoPa when creating a new condition) the fundamental concept is that each permit+location+parameter combination needs to be monitored during a given period (temporal) at a given frequency in that period. Every time that combination generates a number you combine the "Condition" with the event (data + time) and add the result to the monitoring table.
Active combinations of permits, locations, and parameters can be directly derived from the conditions table. The temporal fields facilitate history generation.
