model organisation has been integrated; label information has been defined; distinguishing between modelled and measured data is well-defined

still open questions in new database schema:

• model organisation is not integrated into the schema (this should have been implemented into the role schema)
• label information has to be defined (latest decision was provider -- but rejected during last discussion)
• distinguishing between modelled and measured data has to be well-defined

changed due date to June 05, 2020

added .doing label

model organisation and model person should be combined into one table (contact).
This will now be realised with table contact, which holds the foreign keys of both tables.
Every entry in contacts will then either hold a person's information or an organisation's information.
This will result in having a "None"-person (id = 0) for organisations and a "None"-organisation (id = 0) for persons.
This approach will save space in avoiding filling the not used columns in every entry.

CREATE TABLE contacts (
  id SERIAL PRIMARY KEY,
  person_id INTEGER REFERENCES persons(id),
  organisation_id INTEGER REFERENCES organisations(id),
  UNIQUE (person_id, organisation_id),
  CONSTRAINT CK_contact CHECK ((person_id > 0 AND organisation_id = 0) OR (person_id = 0 AND organisation_id > 0))
);

INSERT INTO persons(id,name,email,phone,isprivate) VALUES (0,'','','',True);
INSERT INTO organisations(id,name,longname,kind,city,postcode,street_address,country,homepage) VALUES (0,'','',7,'','','','','');

Now fill table contacts with entries from tables persons and organisations:

• for person_entry: INSERT INTO contacts(person_id,organisation_id) VALUES (person_entry_id,0);
• for organisation_entry: INSERT INTO contacts(person_id,organisation_id) VALUES (0,organisation_entry_id);

After this, change the database column names and foreign keys:

ALTER TABLE stationmeta_roles
  RENAME COLUMN person_id TO contact_id;

ALTER INDEX stationmeta_roles_person_id
  RENAME TO stationmeta_roles_contact_id;

ALTER TABLE timeseries_roles
  RENAME COLUMN person_id TO contact_id;

ALTER INDEX timeseries_roles_person_id
  RENAME TO timeseries_roles_contact_id;

ALTER TABLE stationmeta_roles          
  DROP CONSTRAINT stationmeta_roles_person_id_fk_persons_id;

ALTER TABLE stationmeta_roles
  ADD CONSTRAINT stationmeta_roles_contact_id_fk_contacts_id FOREIGN KEY (contact_id) REFERENCES contacts(id) DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE timeseries_roles
  DROP CONSTRAINT timeseries_roles_person_id_fk_persons_id;

ALTER TABLE timeseries_roles
  ADD CONSTRAINT timeseries_roles_contact_id_fk_contacts_id FOREIGN KEY (contact_id) REFERENCES contacts(id) DEFERRABLE INITIALLY DEFERRED;

Finally make sure, roles point to the correct entries within table contacts!

mentioned in commit 8fbb8ffc

mentioned in commit f9fc4d1b

added .done label and removed .doing label

closed

mentioned in commit eff7421a

added enhancement label