SQL Server多对多关系 [英] SQL Server many-to-many relationship

问题描述

大家好！

我有一个多对多关系问题:

事实:

-一个州有许多直辖市.
直辖市属于一个县.
(一对一关系)
-一个州有很多城市.
一个城市属于一个自治市.
(一对一关系)
现在
-一个自治市可以拥有一个或多个城市.
-一个城市可以有一个或多个市政当局.
也就是说，市与市之间存在多对多的关系.

我想创建一个tblMunicipalityCity联结表来服务多对多关系:

Hi everybody!

I have a many-to-many relationship problem:

The facts:

- A Prefecture has many Municipalities.
A Municipality belongs to a single Prefecture.
(one-to-may relationship)
- A Prefecture has many Cities.
A City belongs to a single Municipality.
(one-to-may relationship)
Now
- A Municipality can have one or more Cities.
- A City can have one or more Municipalities.
That is, Municipalities and Cities have a many-to-many relation.

I think of creating a tblMunicipalityCity junction table to serve the many-to-many relationship:

                                       tblMunicipalityCity
                                       -------------------
[tblPrefecture] PrefectureID      ->   PrefectureID     
[tblMunicipality] MunicipalityID  ->   MunicipalityID 
[tblCity] CityID                  ->   CityID  

Would you think this is a good solution?

推荐答案

是的，请与您的联结表一起使用，并记住根据最常见的搜索来建立索引.您可能想要捕获一些搜索查询，并查看用户的实际操作.

我不后悔的一件事是在联结表中还添加了CreatedBy，CreateDate，LastUpdateBy和LastUpdateDate(或类似的)字段.当您有多个管理员/贡献者时尤其如此.

干杯.

Yes, go with your junction table, and remember to build your index according to the most common search. You might want to capture some search queries and see what your users are actually doing.

One thing I haven''t regretted is the addition of CreatedBy, CreateDate, LastUpdateBy and LastUpdateDate (or similar) fields in the junction table as well. This is especially true when you have multiple admins/contributors.

Cheers.

这篇关于SQL Server多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！