treatments value constraints: needed cleanup and decisions

[gsrohde]

control

• [ ] Decide how to handle cases where control is NULL
• [ ] Add not NULL constraint
• [ ] Decide what other constraints apply to control
• [ ] Add them

Details

control is NULL in 190 rows.

From the Uniqueness portion of the constraints spreadsheet: "there must be a control for each (citation_id, site_id) tuple". "A control" means a treatment for which control = true. The meaning of this is not clear, however, since site_id and citation_id are not columns of this table.

[dlebauer]

The meaning of this is not clear, however, since site_id and citation_id are not columns of this table.

I think this should hold for the union of the following two tables:

select site_id, citation_id, treatment_id, control from traits join treatments on traits.treatment_id = treatments.id

select site_id, citation_id, treatment_id, control from yields join treatments on yields.treatment_id = treatments.id

For cases where control is NULL:

• if it is the only treatment for a given citation, set it to TRUE
• if there is another treatment for the citation that is TRUE, set this to false
• then we can clean up the rest manually.