From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Rich Shepard <rshepard(at)appl-ecosys(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Schema Help Needed To Get Unstuck |
Date: | 2015-07-24 01:21:17 |
Message-ID: | CANu8FiyO21=OOzmsLd5zDgZO-6hXgNOvUppcE-56RfLFKS6Qiw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
);
From | Date | Subject | |
---|---|---|---|
Next Message | Rich Shepard | 2015-07-24 01:49:33 | Re: Schema Help Needed To Get Unstuck |
Previous Message | Adrian Klaver | 2015-07-24 00:44:45 | Re: Delete rule does not prevent truncate |