How to effectively design dynamic hierarchies in a data schema model?

Question

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.

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:

User1|
User2| - group1
User3|
                 -> group3
User4|
User5| - group2
User6|

Additionally, I can still continue to have my city, state, county groups like this:

User1|
User4| - city1
User3|
                 -> county1
User4|
User5| - city2

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?

Please let me know If I am not being clear, I can more details to clarify.

Further clarification:

I want to track down a report for user clicks by country, and I can further drilldown 2 levels into state, and city.

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.

Answer 1

Your logic is in error. Cities, Counties, States are not hierarchical - a single city can exist in different counties, etc.

Answer 2

Take a look at nested sets or adjacency lists (also in the same article). They have their pluses and minuses....

Answer 3

This sounds similar to my question on user defined hierarchical data models, perhaps the answers there would be of help.