数据库设计/建模问题 - 有约束还是无约束？

Question

给出以下结构：

City
Area
User

每个区域有 1 个且只有 1 个城市。
每个用户至少有一个但也可能有多个区域。
每个用户有 1 个且仅有 1 个城市。

建模最优雅的方式是什么？

目前，我有：

User,
UserArea,
Area,
City

其中 UserArea 与 User 是 1:M 关系，而 Area 与 City 是 1:1 关系。

问题是这样的：

在当前模型下，用户可以拥有 3 或 4 个区域，但其中 2 个区域可能位于城市“1”，另外 2 个区域可能位于城市“2”。 这是违反商业规则的行为。

我是否应该施加一个约束来防止此类事情发生，或者是否有更好的方法来进一步规范化，以使这种类型的悖论成为不可能？ 如果是这样，如何对该系统进行建模以便：

1 用户 = 1 城市；
1 个区域 = 1 个城市；
1 个用户 = M 个区域；

感谢您的见解。

Answer 1

我将为用户、区域和城市各有一个表，然后有第四个表，其中包含用户（FK）、城市（FK）和区域（FK）列，其中用户和区域 城市（组合起来）必须是独特的。 然后，每当插入用户区域组合时，它都不会允许非唯一的城市。

Answer 2

我唯一能想到的就是：

为 Area 表提供一个由 CityID 和 AreaID 组成的复合备用键。 将 AreaID 设置为主（这样它只能有一个城市）。

使用此备用密钥 (AK1) 在 Area 和 UserArea 之间形成 FK 关系。

为 User 表提供一个由 UserID 和 CityID 组成的复合备用键。 将 UserID 设置为主。

使用此备用密钥 (AK2) 在 User 和 UserArea 之间形成 FK 关系。

所以你的 UserArea 表将如下所示：

UserID
城市ID
AreaID

基于 AK2 的外键将强制您选择与用户的家乡城市相匹配的城市，而基于 AK1 的外键将强制您选择属于​​该城市的区域。 本质上，AK1 和 AK2 外键会重叠，强制执行您想要的操作。

Answer 3

我认为您的“用户、用户区域、区域、城市”方法是正确的。 依靠约束和业务逻辑来防止违规。

Answer 4

您能否提供有关什么是区域的更多详细信息？ 让我陈述一下我的假设：
用户居住在一个城市。
每个城市都有区域。
一个地区只能属于一个城市。
一名用户只能居住在一个城市
考虑到这些条件，您的设计规范中似乎具有以下功能依赖性：
面积-> 城市
用户-> 城市
您的业​​务模型建议用户可以在同一城市内拥有多个地址，但不能在两个不同城市内拥有一个地址。 这是现实的设计限制吗？ 如果我可以有多个地址，为什么不在不同的城市呢？
如果您想存储给定用户的所有区域，您需要第三个表（就像您所建议的那样）。 该表看起来像
UserArea(userID,AreaID)。 您需要使用触发器或存储过程来实现业务逻辑。

Answer 5

USER_AREAS 仅需要以下列：

• USER_ID (pk, fk for USERS.USER_ID)
• AREA_ID (pk, fk for AREA.AREA_ID)

一个区域与 AREAS 表中的一个城市相关联； 您可以通过从 AREAS 表中汇总来了解哪些城市与用户关联：

AREA

• AREA_ID (pk)
• CITY-ID（fk 为 < code>CITY.CITY_ID）

将 CITY_ID 放入 USER_AREAS 表中是多余的。 其次，将 CITY_ID 放入 USER_AREAS 表中并不能保证该记录中的 AREA_ID 实际上与 CITY_ID 关联> 在 AREA 表中。 CHECK 约束仅通过限制列接受的值来强制域完整性，并且不能引用其他表中的列（必须小于用户定义的函数）。

您无法强制执行仅属于数据库中单个城市的用户区域的业务规则。 它必须在应用程序级别完成（无论存储过程管理插入/更新USER_AREAS表）。

Answer 6

我不确定你所说的“区域”是什么意思。

我认为城市的划分是这样的：

地球上有国家。
一个国家有多个地区（州、省等）
地区有区域（城市、城镇、村庄等）
区域（如果足够大）可以有区。

用户=> 国家 + 地区/地区 + 城市（+区）

您能详细说明一下地区吗？

Answer 7

这个答案是 SQLServerCentral 提供给我的，它正是我所寻找的。 存在冗余（正如雷克萨姆在本论坛中指出的那样），但不存在异常的可能性。

我对您的意见和建议非常感兴趣。

---

CREATE TABLE [dbo].[Cities](
    [CityID] [int] IDENTITY(1,1) NOT NULL,
  [CityName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED
(
   [CityID] ASC
)
)
CREATE TABLE [dbo].[Users](
   [UserID] [int] IDENTITY(1,1) NOT NULL,
  [UserName] [varchar](50) NOT NULL,
  [CityID] [int] NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
  [UserID] ASC
)
)

ALTER TABLE [dbo].[Users]  WITH CHECK ADD  CONSTRAINT [FK_Users_Cities] FOREIGN KEY([CityID])
REFERENCES [dbo].[Cities] ([CityID])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Cities]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_UsersCity] ON [dbo].[Users]
(
   [UserID] ASC,
   [CityID] ASC
)

CREATE TABLE [dbo].[Areas](
    [AreaID] [int] IDENTITY(1,1) NOT NULL,
  [AreaName] [varchar](50) NOT NULL,
  [CityID] [int] NOT NULL,
 CONSTRAINT [PK_Areas] PRIMARY KEY CLUSTERED
(
  [AreaID] ASC
))


GO
ALTER TABLE [dbo].[Areas]  WITH CHECK ADD  CONSTRAINT [FK_Areas_Cities] FOREIGN KEY([CityID])
REFERENCES [dbo].[Cities] ([CityID])
GO
ALTER TABLE [dbo].[Areas] CHECK CONSTRAINT [FK_Areas_Cities]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_AreasCity] ON [dbo].[Areas]
(
 [AreaID] ASC,
   [CityID] ASC
)
GO
CREATE TABLE [dbo].[UserCityArea](
   [UserID] [int] NOT NULL,
    [CityID] [int] NOT NULL,
    [AreaID] [int] NOT NULL,
 CONSTRAINT [PK_UserCityArea] PRIMARY KEY CLUSTERED
(
   [UserID] ASC,
   [CityID] ASC,
   [AreaID] ASC
)
)

GO
ALTER TABLE [dbo].[UserCityArea]  WITH CHECK ADD FOREIGN KEY([UserID], [CityID])
REFERENCES [dbo].[Users] ([UserID], [CityID])
GO
ALTER TABLE [dbo].[UserCityArea]  WITH CHECK ADD FOREIGN KEY([AreaID], [CityID])
REFERENCES [dbo].[Areas] ([AreaID], [CityID])