Postgres Pro
Downloads
Home   >   mailing lists

Thread: Schema Help Needed To Get Unstuck

Schema Help Needed To Get Unstuck

From
Rich Shepard
Date:
   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

Re: Schema Help Needed To Get Unstuck

From
"David G. Johnston"
Date:
On Thu, Jul 23, 2015 at 3:06 PM, Rich Shepard <rshepard@appl-ecosys.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.


Re: Schema Help Needed To Get Unstuck

From
Rich Shepard
Date:
On Thu, 23 Jul 2015, David G. Johnston wrote:

> 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,

   This is what I was working to achieve, but did not have the details
straight. I'll ponder this and make sure I set the proper FKs on the
appropriate tables.

Thanks,

Rich

Re: Schema Help Needed To Get Unstuck

From
Melvin Davidson
Date:
Does this help?

CREATE TABLE permits
(
permit_number bigint,
...,
other_columns <data_types>,
...,
CONSTRAINT permits_pk PRIMARY KEY (permit_number)
);

CREATE TABLE parameters
(
permit_number bigint,
parameter varchar(50),
...,
other_columns <data_types>,
...,
CONSTRAINT parameters_pk PRIMARY KEY (permit_number, parameter),
CONSTRAINT parameters_permit_fk FOREIGN KEY (permit_number)
  REFERENCES permits (permit_number)
);

CREATE TABLE locations
(
permit_number bigint,
location varchar(50),
...,
other_columns <data_types>,
...,
CONSTRAINT locations_pk PRIMARY KEY (permit_number, location),
CONSTRAINT locations_permit_fk FOREIGN KEY (permit_number)
  REFERENCES permits (permit_number)
);

CREATE TABLE conditions
(
permit_number bigint,
condition varchar(50),
location varchar(50),
frequency varchar(10),
start_time timestamp,
end_time timestamp,
...,
other_columns <data_types>,
...,
CONSTRAINT locations_pk PRIMARY KEY (permit_number, condition, location)
CONSTRAINT conditions_permit_fk FOREIGN KEY (permit_number)
  REFERENCES permits (permit_number),
CONSTRAINT conditions_location_fk FOREIGN KEY (permit_number, location)
  REFERENCES locations (permit_number, location)
);

CREATE TABLE monitoring
(
permit_number bigint,
monitor_date timestamp,
location varchar(50),
frequency varchar(10),
start_time timestamp,
end_time timestamp,
...,
other_columns <data_types>,
...,
CONSTRAINT locations_pk PRIMARY KEY (permit_number, condition, location)
CONSTRAINT monitoring_permit_fk FOREIGN KEY (permit_number)
  REFERENCES permits (permit_number),
CONSTRAINT monitoring_location_fk FOREIGN KEY (permit_number, location)
  REFERENCES locations (permit_number, location),
CONSTRAINT monitoring_parameter_fk FOREIGN KEY (permit_number, parameter)
  REFERENCES locations (permit_number, parameter)

);


Re: Schema Help Needed To Get Unstuck

From
Rich Shepard
Date:
On Thu, 23 Jul 2015, Melvin Davidson wrote:

> Does this help?

Melvin,

   Yep.

   After pondering David's response I recognized my error: I had the permit
table as the center of the relationships when it should be the conditions
table. As you wrote, permits, parameters, and locations all feed the
conditions table (where additional attributes are added), then a row from
that table is added to the monitoring table where quantities and other
attributes are entered.

   Sometimes we get so deep in a rut we can't see over the edge and find the
correct path.

Thanks very much,

Rich