Thanks again David. I think that's all making sense to me now, except I want to follow up on your last point:
Yes, un-nesting can make the problem go away though it too is unusual. For the most part either use relations/sets or use arrays (for a specific component of the schema). Your example mixes the two which makes using that part of the schema difficult.
I'm not sure exactly what you're saying here, but it's important to me because I've recently had to do a lot with arrays, and continue to have to do so. What I'm working with is similar to the example I gave you, but let me be more concrete.
I'm working with a social service agency. Every time they see a client they fill out an "encounter" record. Part of what is tracked is what kind of services were provided, which is stored as an array of "service codes" within the encounter. The encounter also has a date.
So I frequently have to provide information like "what were all the types of services this client received during the last quarter?" or "show me all the clients who received service X last year." I've learned enough to use ANY, array_agg and unnest to get through these queries, but if I'm going about this wrong or there's a better way to do it I'd love to know about it!
