I have country, region, county, town data and I'm currently deciding between 2 schema designs (if there's a better one, do tell).
我有国家、地区、县、镇的数据，我目前在两种模式设计之间做决定(如果有更好的模式，请务必告诉我)。
I first thought
我的第一个念头
-
Country
国家
- Id
- Id
- Name
- 的名字
-
Region
地区
- Id
- Id
- CountryId
- CountryId
- Name
- 的名字
-
County
县
- Id
- Id
- RegionId
- RegionId
- Name
- 的名字
-
Town
小镇
- Id
- Id
- CountyId
- CountyId
- Name
- 的名字
Does the job however to get all towns in a country you have to 3 inner joins to do the filtering. I guess this could be ok but potentially expensive?
但是要得到一个国家的所有城镇你必须有3个内部连接来做过滤。我猜这可能还可以，但可能会很贵?
The other design was:
其他的设计是:
-
Country
国家
- Id
- Id
- Name
- 的名字
-
Region
地区
- Id
- Id
- Name
- 的名字
-
County
县
- Id
- Id
- Name
- 的名字
-
Town
小镇
- Id
- Id
- CountryId
- CountryId
- RegionId
- RegionId
- CountyId
- CountyId
- Name
- 的名字
This way all hierarchical data so to speak is at the bottom and you can go back up however if you want all regions in a country you're a bit screwed which makes we wonder whether the first design is best.
这样，所有的分层数据都在底部，你可以回到上面，但是如果你想要一个国家的所有地区你有点搞砸了，这让我们怀疑第一个设计是不是最好的。
What do you think is the best schema design?
您认为最好的模式设计是什么?
1 个解决方案
#1
2
The best database design depends on how the data is being used.
最好的数据库设计取决于如何使用数据。
If this is pretty static data that will all be updated at one time and external references are all to towns, then I would probably go for a denormalized dimension. That is, store the information all in one row:
如果这是非常静态的数据，每次都会更新，外部引用都是指向城镇的，那么我可能会选择非规范化维度。也就是说，将所有的信息都存储在一行中:
- Town Id
- 镇Id
- Town name
- 小镇的名字
- County name
- 县的名字
- Region name
- 地区名称
- Country name
- 国家的名字
Under the above scenario, the ids for county, region, and country are not necessary (by assumption).
在上述情形下，县、地区和国家的id不是必须的(假设)。
If the data is being provided as separate tables with separate ids, and these tables can be updated independently or row-by-row, then a separate table for each makes sense. Putting all the ids into the towns table may or may not be a good idea. You will have to verify and maintain the hierarchies when data is inserted and updated.
如果数据是作为具有独立id的独立表提供的，并且这些表可以独立更新或逐行更新，那么每个表都有单独的表是有意义的。将所有id放入town table可能是个好主意，也可能不是。当插入和更新数据时，必须验证和维护层次结构。
If ids for each level are necessary for your, then you should have appropriate table structure for declaring foreign key constraints. But, this can get complicated. Will an external entity have a "geography" attribute that can be at any level? Will an external always know what level it is going to refer to as?
如果每个级别的id对于您来说都是必需的，那么您应该有合适的表结构来声明外键约束。但是，这可能会很复杂。一个外部实体是否具有任何级别的“地理”属性?一个外在的人会永远知道他要达到什么程度吗?
In other words, you need to know how the data is going to be used in order to define an appropriate data model.
换句话说，您需要知道如何使用数据来定义一个合适的数据模型。
#1
2
The best database design depends on how the data is being used.
最好的数据库设计取决于如何使用数据。
If this is pretty static data that will all be updated at one time and external references are all to towns, then I would probably go for a denormalized dimension. That is, store the information all in one row:
如果这是非常静态的数据，每次都会更新，外部引用都是指向城镇的，那么我可能会选择非规范化维度。也就是说，将所有的信息都存储在一行中:
- Town Id
- 镇Id
- Town name
- 小镇的名字
- County name
- 县的名字
- Region name
- 地区名称
- Country name
- 国家的名字
Under the above scenario, the ids for county, region, and country are not necessary (by assumption).
在上述情形下，县、地区和国家的id不是必须的(假设)。
If the data is being provided as separate tables with separate ids, and these tables can be updated independently or row-by-row, then a separate table for each makes sense. Putting all the ids into the towns table may or may not be a good idea. You will have to verify and maintain the hierarchies when data is inserted and updated.
如果数据是作为具有独立id的独立表提供的，并且这些表可以独立更新或逐行更新，那么每个表都有单独的表是有意义的。将所有id放入town table可能是个好主意，也可能不是。当插入和更新数据时，必须验证和维护层次结构。
If ids for each level are necessary for your, then you should have appropriate table structure for declaring foreign key constraints. But, this can get complicated. Will an external entity have a "geography" attribute that can be at any level? Will an external always know what level it is going to refer to as?
如果每个级别的id对于您来说都是必需的，那么您应该有合适的表结构来声明外键约束。但是，这可能会很复杂。一个外部实体是否具有任何级别的“地理”属性?一个外在的人会永远知道他要达到什么程度吗?
In other words, you need to know how the data is going to be used in order to define an appropriate data model.
换句话说，您需要知道如何使用数据来定义一个合适的数据模型。