为以下内容设计架构的最佳方法是什么？
为以下内容设计架构的最佳方法是什么 要求？
需要存储Countrys、States、Counties、counties可分为 进入地区。然后，区域可以拥有具有各种数据点的人员 报告。
区域还可以进一步划分为分区 就像把人分组一样。所以Region 1可以有Division A，Division B 与每个部门的人员。各地区和部门分别有 与国家、州和县不同的不同元数据集。
报告将涉及与人员和类似人员相关的数据 谷歌分析，从国家/地区向下钻取 深入到地区和部门。
注：地区可以有10人，1个分区有4人，其余6人 不隶属于任何部门。
What is the best way to design the schema for the following
requirements?
Need to store Countries, States, Counties, and counties can be divided
into regions. Regions can then have people with various data points
to report on.
Regions can also be further divided into divisions which is
like grouping people. So Region 1 can have Division A, Division B
with people in each of these divisions. Regions and Divisions, each have
different set of meta data that are different from Countries, States and Counties.
Reports will be on the data associated with people and similar
to google analytics with drilling down from Country all the
way down to regions and divisions.
Note: Regions can have 10 peopel and 1 division with 4 people and remaining 6 people
are not tied to any division.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
听起来好像每个人只能拥有一个区域。
如果您正在进行事务处理（而不是数据挖掘/仓储），那么我会使用
RegionID外键将该人链接到该区域。至于（可选）部门，您可以使用链接表将人员链接到部门：
PersonID、DivisionID，或者如果您不介意NULLDivisionID，可以有外键。就地理区域的层次结构而言，在我更多地了解各国之间的限制以及这些结构代表什么之前，我会犹豫是否要对其进行建模。虽然认为一切总是会上升到下一个级别是件好事，但我已经处理过很多层次结构，其中级别被跳过，并且这些层次结构的建模方式非常不同。另外，许多国家（例如英国）通常没有州（除非您要使用英格兰、苏格兰、威尔士和北爱尔兰）。 法国的情况更加复杂。
对于报告/汇总方面（或者如果您只进行数据挖掘/仓储），我将转换为单独的维度模型，它将“锁定”其他事物作为属性，并使汇总变得更加容易。因此，星型模式将锁定事实不同级别的维度 ID。
It sounds like each person can have one and only one region.
If you are doing transactional processing (as opposed to data mining/warehousing), then I would link the person to the region with a
RegionIDforeign key.As far as the (optional) divisions, you can either link the person to a division with a link table:
PersonID,DivisionIDor if you don't mindNULLDivisionID, you can have a foreign key.As far as the hierarchy for the geographic regions, I would hesitate to model this until I knew more about the limitations across countries and what these structures represent. While it would be nice to think that everything always rolls up to the next level, I've dealt a lot with hierarchies where levels are skipped and these are modeled very differently. Plus, many countries like the United Kingdom typically would not have States (unless you are going to use England, Scotland, Wales, and Northern Ireland). France is even more complex.
For the reporting/rollup aspect (or if you are only doing data mining/warehousing), I would transform to a separate dimensional model, which would "lock in" the other things as attributes and make it far easier to do the roll ups. So the star schema would lock in dimensional IDs for the different levels to the facts.
我突然想到：
部门有对 Divisions_people 的 fk
Off the top of my head:
divisions has fk to divisions_people
国家表：
Country_id、country_name、population
州的表：
state_id、state_name、country_id、population
县的表：
County_id、county_name、state_id、population
区域表：
Region_id、region_name、county_id、population
分区表：
Division_id、division_name、region_id、population
在您的代码中或通过触发约束（取决于您的 RDBMS）验证您在只有 50 人的区域内没有 300 人的部门。如果一个地区有人口但不属于分区，那么您的地区人口将为 500，而其分区总和仅为 450（即一个地区有 50 人但没有分区）。
A table for countries:
country_id, country_name, population
A table for states:
state_id, state_name, country_id, population
A table for counties:
county_id, county_name, state_id, population
A table for regions:
region_id, region_name, county_id, population
A table for divisions:
division_id, division_name, region_id, population
Validate in your code or through a triggered constraint (depending on your RDBMS) that you don't have a division with 300 people inside a region with only 50 people. To have people in a region without being in a division, your region population would be 500 while the sum of its divisions would be only 450 (leaving you 50 people in a region but divisionless).