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.)
TIA,
Rich