Re: [GENERAL] Schema Help Needed To Get Unstuck

[Melvin Davidson]

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)

);