Tracking water use for many sites

[amabdallah]

How do we track water use reported by a water right owner for their right for all their sites?
The water right permit is for many sites, as in this example. Likely the owner will report water withdrawal as an aggregate from all the sites.
https://westdaat.westernstateswater.org/details/right/NDwr_WR977
See screenshot below

https://schema.westernstateswater.org/diagrams/2_SiteSpecificAmounts.html

I hope this does not require supporting * to * relationship in our schema

[rwjam]

Issue:
Trying to relate recorded water use timeseries data to a water right unfortunately has some major data architecture caveats. The underlining issue relates to the complex many-to-many-to-many relationship between water use records, water rights, and sites, which is heavily dependent on a data provider’s own water use record architecture. Data can come in either of the following structures...

• CASE 1: recorded water use is the summary specific to a single site, but the site’s total recorded water use cannot be disaggregated to multiple uses / water rights if the site is applied to more than one water right. This is not an issue of that site is specifically a one-to-one with a water right.
• CASE 2: recorded water use is the summary to multiple sites, and that total recorded water use cannot be disaggregated to any individual water right applied to the sites of interest.
• CASE 3: recorded water use could be a summarized report specific to a single water right, but that water right could be applied to many sites where it is not possible to disaggregate how much of that use comes from what site. This is not an issue of there is only a one-to-one relationship with a site and a water right.

Possible Solutions
For data architecture / API design / web development, in the event you have a many-to-many relationship the solution is to convert that relationship to a one-to-many relationship. In this context between water use timeseries data to a water rights, the following are possible solutions

1. Support Case 3 and build a data architecture that showcases water use to a water right, but cannot be disaggregated to multiple sites. At this time, this is the case that WaDE / WestDAAT supports.
2. Support CASE 1 and build a data architecture that showcases water use to a site, but cannot be disaggregated to multiple water rights. This would require a database schema update for us to support.
3. Support both cases, but provide a clear context between what case is being showcased. This is the hardest fix, as it would require database schema update for us to support, but also duplication of existing structure elements which we don't want to support.