Вопрос проектирования / моделирования базы данных - ограничения или нет ограничений?

Учитывая следующую структуру:

City
Area
User

В каждой области есть 1 и только 1 город.
У каждого пользователя есть хотя бы одна, но, возможно, несколько областей.
У каждого пользователя есть 1 и только 1 город.

Какой самый элегантный способ смоделировать это?

В настоящее время у меня есть:

User,
UserArea,
Area,
City

Где UserArea - отношение 1:M с пользователем, а Area - 1:1 с City.

Проблема заключается в следующем:

Пользователь может иметь 3 или 4 Области под текущей моделью, но 2 Области могут быть в Городе "1", а другие 2 Области могут быть в Городе "2". Это нарушение бизнес-правил.

Должен ли я просто ввести ограничение, чтобы предотвратить подобные вещи, или это лучший подход для дальнейшей нормализации, чтобы этот тип парадокса был невозможен? Если да, то как можно смоделировать эту систему так, чтобы:

1 пользователь = 1 город;
1 район = 1 город;
1 пользователь = M Области;

Спасибо за ваши идеи.

Источник

7 ответов

Решение

Этот ответ был предоставлен мне от SQLServerCentral, и он делает именно то, что я искал. Существует избыточность (как указал rexum на этом форуме), но нет возможности аномалий.

Мне очень интересны ваши комментарии и предложения.

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])
Источник

У меня была бы таблица для пользователей, областей и городов, а затем четвертая таблица с столбцами "Пользователь" (FK), "Города" (FK) и "Районы" (FK), где "Пользователи и города" (в комбинации) ограничены, чтобы быть уникальными. Тогда всякий раз, когда вставляется комбинация User-Area, он не допускает неуникальный Город.

Источник

USER_AREAS требуются только следующие столбцы:

  • USER_ID (рк, фк для USERS.USER_ID)
  • AREA_ID (рк, фк для AREA.AREA_ID)

Область связана с одним городом в таблице AREAS; Вы знаете, какие города связаны с пользователем, сворачивая из таблицы AREAS:

AREA

  • AREA_ID (Рк)
  • CITY-ID (ФК для CITY.CITY_ID)

Ввод CITY_ID в USER_AREAS таблица является избыточной. Во-вторых, размещение CITY_ID в USER_AREAS таблица не гарантирует, что AREA_ID в этой записи на самом деле связано с CITY_ID в таблице AREA. Ограничение CHECK только усиливает целостность домена, ограничивая значения, принимаемые столбцом, и не может ссылаться на столбцы в других таблицах, кроме функции, определенной пользователем.

Вы не можете обеспечить соблюдение бизнес-правила для областей пользователя, когда-либо принадлежащих только одному городу в базе данных. Это должно быть сделано на уровне приложения (независимо от того, что sproc управляет вставкой / обновлением USER_AREAS Таблица).

Источник

Я думаю, что ваш подход "пользователь, пользователь, область, город" правильный. Положитесь на ограничения и бизнес-логику, чтобы предотвратить нарушения.

Источник

Я не уверен, что вы подразумеваете под "областями".

Я считаю, что городское деление выглядит следующим образом:

У планеты есть страны. В стране есть регионы (штаты, провинции и т. Д.) В регионах есть районы (города, поселки и деревни). В районах (если они достаточно большие) могут быть районы.

Пользователь => Страна + Регион / Район + Город (+ Район)

Не могли бы вы уточнить, какие области?

Источник

Единственное, что я могу придумать, это:

Дайте таблице Area составной альтернативный ключ CityID и AreaID. Сделать AreaID первичным (чтобы он мог иметь только один город).

Используйте этот альтернативный ключ (AK1) для формирования отношения FK между Area и UserArea.

Дайте пользовательской таблице составной альтернативный ключ UserID и CityID. Сделать UserID первичным.

Используйте этот альтернативный ключ (AK2) для формирования отношения FK между User и UserArea.

Таким образом, ваша таблица UserArea будет выглядеть так:

UserID CityID AreaID

Внешний ключ на основе AK2 заставит вас выбрать город, соответствующий домашнему городу пользователя, а внешний ключ на основе AK1 заставит вас выбрать область, которая принадлежит этому городу. По сути, внешние ключи AK1 и AK2 будут накладываться друг на друга, вызывая то, что вы хотите.

Источник

Можете ли вы предоставить более подробную информацию о том, что такое район? Позвольте мне высказать свои предположения:
Пользователь живет в городе.
В каждом городе есть районы.
Район может попасть только в один город.
Пользователь может жить только в одном городе
Учитывая эти условия, у вас, кажется, есть следующие функциональные зависимости в вашей спецификации проекта:
Площадь -> Город
Пользователь -> Город
Ваша бизнес-модель предполагает, что пользователь может иметь несколько адресов в одном городе, но не может иметь адрес в двух разных городах. Это реалистичное ограничение дизайна? Если у меня может быть несколько адресов, то почему не в разных городах?
Если вы хотите сохранить все области данного пользователя, вам нужна третья таблица (как вы предложили). Стол будет выглядеть так
UserArea(идентификатор пользователя,Areaid). Вы должны реализовать бизнес-логику, используя триггер или хранимую процедуру.

Источник
Другие вопросы по тегам