title: How to effectively design dynamic hierarchies in a data schema model?
body: <p>To clarify, Let us say I have users and userstats in my database and I also have information like city, state, county, and region associated with these two tables. In this scenario I can effectively roll up my userstats to city, state, county and region levels easily by a overnight or incremental process.</p>
  
  <p>The alternate scenario where I don't have fixed groups like city, state, county but I want certain super users to have the flexibility to create their own groups and tie the users manually in the following form:</p>
  
  <pre><code>User1|
  User2| - group1
  User3|
                   -&gt; group3
  User4|
  User5| - group2
  User6|
  </code></pre>
  
  <p>Additionally, I can still continue to have my city, state, county groups like this:</p>
  
  <pre><code>User1|
  User4| - city1
  User3|
                   -&gt; county1
  User4|
  User5| - city2
  </code></pre>
  
  <p>Is there a known database pattern that can be used to effectively roll up the associated data into these various buckets? Some kind of tagging pattern?</p>
  
  <p>Please let me know If I am not being clear, I can more details to clarify.</p>
  
  <p>Further clarification:</p>
  
  <p>I want to track down a report for user clicks by country, and I can further drilldown 2 levels into state, and city.</p>
  
  <p>Now let us say I have a facility to create my own groupings (via User interface) and now I will be able to create new regions that have cities from different states. So now my report can show me user clicks by country, then I can drill down into custom regions (group of cities). All the analytics like user clicks, various avgs should now be recalculated based on the new groups.</p>