Given the following structure:
City
Area
User
Every Area has 1 and only 1 City.
Every User has at least one but possibly multiple Areas.
Every User has 1 and only 1 City.
What is the most elegant way to model this?
Currently, I have:
User,
UserArea,
Area,
City
Where UserArea is a 1:M relationship w/ User, and Area is 1:1 with City.
The problem is this:
A user can have 3 or 4 Areas under the current model, but 2 of the Areas could be in City "1" and the other 2 Areas could be in City "2". This is a violation of business rules.
Should I just put in a constraint to prevent this sort of thing, or is a better approach to normalize further so that this type of paradox is not possible? If so, how does one model this system so that:
1 User = 1 City;
1 Area = 1 City;
1 User = M Areas;
Thanks for you insights.
This answer was provided to me from SQLServerCentral, and it does exactly what I was looking for. There is a redundancy (as rexum pointed out in this forum), but there is no possibility of anomolies.
I'm very interested in your comments and suggestions.
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])