title: Star schema many-to-many for time series data
body: <p>I have a DataWarehouse / star schema modelling question.</p>
  
  <p>I have meter readings (facts) for different locations (dimensions).  Each location has multiple organisations in different roles.  I would like to model this in a data-warehouse schema.  Current plan is:</p>
  
  <pre><code>fact_meter_readings
     timestamp,
     location_id,
     meter_reading (number)
  
  dim_locations
     location_id
     name
  
  dim_location_organisations
     location_id
     organisation_id
     role_id
     organisation_name
     role_name
  
  Unique key (location_id, organisation_id, role_id)
  </code></pre>
  
  <p>The last table is the one in question.  Although the table has a unique constraint, the unique constraint is not shared to the fact table, which is the bit I'm unclear about.  </p>
  
  <p>I am wanting to use this schema to answer questions like <strong>sum the meter_reading grouped by organisation where they are in the role "operator"</strong> </p>
  
  <p>e.g.</p>
  
  <pre><code>select 
    lo.organisation_name,
    sum(meter_reading) 
  from fact_meter_readings m
  inner join dim_location_organisations lo
  on lo.location_id = m.location_id
  where lo.role_id = xyz
  group by lo.organisation_name
  </code></pre>
  
  <p>Is there any issue here as the dimension table does not have a unique key that is shared with the fact table?  Obviously queries would need to handled carefully to avoid double counting facts.</p>