1. Reads is constantly inserted upon. It should never be updated or deleted.
2. I suppose I can, but that will make my insertion logic very complicated. I cannot guarantee the order of any of this data, so I might get reads at any time and also get assignments at any time (historical as well). I suppose I could do that, but I'd like to avoid it if at all possible.
3. 2 measurements can have the same date, and that is fine. The problem arises when the same device produces 2 reads at the same time and that isn't possible.
4. I agree that a lot of this is a mess, however MAX(max_read) OVER(PARTITION BY patient_id) AS latest_read seems necessary as using a group by clause forces me to group by all elements I'm selecting, which I don't want to do.