Re: Schema Help Needed To Get Unstuck

On Thu, Jul 23, 2015 at 3:06 PM, Rich Shepard <rshepard(at)appl-ecosys(dot)com>
wrote:

> 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.)
>
>
​Conditions <- (Permit + Location + Parameter + Frequency + Temporal (from,
until))
Monitoring <- (Condition + Event)

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.

David J.