Search code examples
database-designnormalizing

Database Design/Modeling Question - Constraints or No Constraints?


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.


Solution

  • 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])