如何有效地设计数据架构模型中的动态层次结构？ [英] How to effectively design dynamic hierarchies in a data schema model?

问题描述

要澄清，让我们说我的数据库中有用户和用户统计信息，我还有与这两个表相关联的城市，州，县和地区等信息。在这种情况下，我可以通过隔夜或增量过程轻松地将市场，州，县和地区级别的用户统计信息卷起来。

有固定的组织，如城市，州，县，但是我希望某些超级用户能够灵活地创建自己的组，并以下列形式手动绑定用户：

  User1 | 
 User2 | -  group1 
 User3 | 
  - > group3 
 User4 | 
 User5 | -  group2 
用户6 | 
  

此外，我仍然可以继续拥有我的城市，州，县组织：

  User1 | 
 User4 | -  city1 
 User3 | 
  - >县1 
 User4 | 
 User5 | -  city2 
  

是否有一种已知的数据库模式可用于有效地将相关数据汇总到这些模式中各种桶？某些标记模式？

请让我知道如果我不清楚，我可以提供更多的细节来澄清。

进一步澄清：

我想按国家/地区跟踪用户点击的报告，我可以进一步向下钻取2级进入状态和城市。 p>

现在让我们说我有一个设施来创建自己的分组（通过用户界面），现在我将能够创建具有来自不同州的城市的新区域。所以现在我的报告可以显示我的用户点击次数，然后我可以深入到自定义区域（城市群）。所有的分析，如用户点击，各种avgs现在应该根据新的组重新计算。

解决方案

看看嵌套集或邻接列表（也在同一篇文章中）。他们有自己的优点和缺点....

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.

解决方案

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

这篇关于如何有效地设计数据架构模型中的动态层次结构？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！