0

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.

Srikar Doddi
  • 15,499
  • 15
  • 65
  • 106

3 Answers3

3

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

Jason S
  • 184,598
  • 164
  • 608
  • 970
  • Nested sets can become complex to maintain if not well documented, but I agree with you. That can be a possible solution. +1 – Srikar Doddi Apr 18 '09 at 01:59
  • Is this still relevant now we have common table expressions? Or rather are the cons of using the adjacency list mitigated by CTEs and therefore the maintenance of nested sets makes them less useful. – BenCr Sep 27 '13 at 10:29
0

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

0

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

Community
  • 1
  • 1
ninesided
  • 23,085
  • 14
  • 83
  • 107
  • did you implement any of the proposed models? – Srikar Doddi Apr 17 '09 at 23:42
  • I went with Adam Robinson's suggestion (the accepted answer), it seems to be working well so far although I appreciate it might be a little different to what you're looking for – ninesided Apr 18 '09 at 11:15