July 20, 2009 at 1:55 pm
Given the following structure:
City
Area
User
Every Area has 1 and only 1 City.
Every User has at least 1 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.
July 21, 2009 at 7:20 am
I suggest four tables: User, City, Area, and UserCityArea. I would put a city column in the User table. You will then have your 1 user to 1 city rule taken care of. I would do a similar thing with area by adding a city column to the Area table. Then for the UserCityArea table, I would add foreign keys from both the User table (on user and city) and the Area table (on area and city). As long as the city is the same for a user and an area, you can link them.
I haven't tested this but it might work.
July 22, 2009 at 12:59 pm
Hi Scott;
My article here http://qa.sqlservercentral.com/articles/Data+Modeling/61528/ may give you some background to help solve this problem.
HTH,
TroyK
July 23, 2009 at 11:22 am
Ok, maybe someone can make sense of this for me, but what kind of a heirachy are we dealing with here? If an Area can only have 1 City, and the User can only have 1 City, how on earth can a User have more than 1 Area? The logic leap necessary escapes me... Any query of such a system would easily be able to show that by virtue of the Area connections, the user has multiple cities, and yet, they're limited to 1. When you can explain that, please let me know...
I'm also wondering what real-world entities this model represents?
Steve
(aka smunson)
:-):-):-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 23, 2009 at 12:54 pm
Steve,
I think that the OP misstated the relationships. I believe that there is a 1:M relationship between City and User and between City and Area so City is at the top of the heirarchy. Anyway, that is how I interpreted it and what I based my suggestion on. If that isn't the case, then there does appear to be a problem with the situation as originally described. Maybe the OP will clarify the situation.
Jason
July 23, 2009 at 12:59 pm
Example:
A User can ONLY be in New York City.
Queens, Brooklyn, Manhattan, Long Island and Harlam are all areas, each of which belongs exclusively to "New York City".
A user can have multiple areas, but only within one city.
So a user can belong to "Queens, Brooklyn and Manhattan" or "Harlam, Bronx, Brooklyn", etc.
A user CANNOT belong to "Queens, Harlam and Newark" because the area "Newark" is in the city "New Jersey" and not "New York".
A user can belong to one or more areas, but each one of those areas must belong to the same city.
July 23, 2009 at 1:11 pm
Jason, you may be right, I'm not sure about the way I worded it. Let me just use plain english:
A "User" can belong to many "Areas". An "Area" can have many "Users".
An "Area" can belong to one "City". A "City" can have many "Areas".
A "User" can belong to one "City". A "City" can have many "Users".
For a specific example, please see my previous reply.
July 23, 2009 at 2:02 pm
Ok, now I see what you're saying. City is at the top of the heirarchy, with Area being the next level. The user has a relationship with only one city, but can relate to multiple areas within the city. How about this:
CREATE TABLE CITIES (
CITY_ID int IDENTITY(1,1),
CITY_NAME varchar(30) UNIQUE
)
ALTER TABLE CITIES
ADD PRIMARY KEY CLUSTERED (CITY_ID)
CREATE TABLE AREAS (
AREA_ID int IDENTITY(1,1),
AREA_NAME varchar(30) UNIQUE,
CITY_ID int REFERENCES CITIES(CITY_ID)
PRIMARY KEY CLUSTERED(AREA_ID)
)
CREATE TABLE USERS (
USER_ID int IDENTITY(1,1),
USER_NAME varchar(50) UNIQUE,
CITY_ID int FOREIGN KEY REFERENCES CITIES(CITY_ID)
PRIMARY KEY CLUSTERED (USER_ID)
)
CREATE TABLE USER_AREAS (
USER_ID int FOREIGN KEY REFERENCES USERS(USER_ID),
AREA_ID int FOREIGN KEY REFERENCES AREAS(AREA_ID),
CITY_ID int FOREIGN KEY REFERENCES CITIES(CITY_ID)
PRIMARY KEY CLUSTERED (USER_ID, AREA_ID)
)
CREATE UNIQUE NONCLUSTERED INDEX IX_USER_CITY
ON USER_AREAS (USER_ID, CITY_ID)
Steve
(aka smunson)
:-):-):-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 23, 2009 at 2:34 pm
Hi Steve,
That won't work for a couple reasons.
The first is that you can still end up with User_Areas that belong to a city that is NOT the same city as is referenced inside the User table. For example, a User can have city 1, but then inside the User_Area table there is nothing preventing the relationship between the User and City 2. The User can be placed on an area which is associated to city 2, and that is a violation of the city field inside the User table.
Secondly, if you add a unique index to the UserID, CityID columns of User_Areas, you are no longer able to place the User on more than one Area inside the same city.
July 23, 2009 at 4:10 pm
Hi JTS, I'm not sure I fully understand what you're talking about. As described, your scenario would still allow for a User to have 3 areas, each one being from a different city than the one listed in the User table.
Could you provide a sql script so that I can clear up any misunderstandings?
Thanks
July 23, 2009 at 6:21 pm
Scott,
You're quite right. I didn't have SSMS open and was in a bit of a time crunch and thought I had it straight, but now that I've had dinner and time to think, I believe I have it. Please do test and validate. Here's the code:
CREATE TABLE CITIES (
CITY_ID int IDENTITY(1,1) NOT NULL,
CITY_NAME varchar(30) UNIQUE NOT NULL
)
ALTER TABLE CITIES
ADD PRIMARY KEY CLUSTERED (CITY_ID)
CREATE TABLE AREAS (
AREA_ID int IDENTITY(1,1) NOT NULL,
AREA_NAME varchar(30) UNIQUE NOT NULL,
CITY_ID int NOT NULL REFERENCES CITIES(CITY_ID)
PRIMARY KEY CLUSTERED(AREA_ID, CITY_ID)
)
CREATE TABLE USERS (
USERS_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
USERS_NAME varchar(50) UNIQUE NOT NULL,
CITY_ID int NOT NULL FOREIGN KEY REFERENCES CITIES(CITY_ID)
CONSTRAINT USER_CITY UNIQUE (USERS_ID, CITY_ID)
)
CREATE TABLE USER_AREAS (
USERS_ID int NOT NULL,
AREA_ID int NOT NULL,
CITY_ID int NOT NULL
)
ALTER TABLE USER_AREAS
ADD FOREIGN KEY (USERS_ID, CITY_ID)
REFERENCES USERS (USERS_ID, CITY_ID)
CREATE UNIQUE CLUSTERED INDEX IX_USER_AREA_CITY
ON USER_AREAS(USERS_ID, AREA_ID, CITY_ID)
Steve
(aka smunson)
:-):-):-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 24, 2009 at 7:12 am
Scott,
Here's my code:
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])
I think my code is very similar to what Steve has last posted but I think I have an additional FK between the Area and UserCityArea tables.
HTH,
Jason
July 24, 2009 at 10:54 am
Sorry Steve, still doesn't validate properly.
As a test, you can try this:
Once you create a couple users, cities and areas, go into your User_Areas table and you'll notice that you can associate a User with an area and a city, but the area and city aren't correct.
So, with this model, I can add the cities "New York" and "New Jersey". Then I can add Areas "Bronx" to City "New York" and "Newark" to City "New Jersey".
Then I can erroneously add User "Steve" to Area "Newark" and City "New York". But "Newark" isn't in "New York". Steve's User record is in city New York, but Steve has been placed on an area that is not inside New York.
July 24, 2009 at 11:04 am
Thanks a lot to Jason and Steve for helping me to understand this concept.
Jason's code does what I was looking for. Steve, your code almost does. I think the problem is just the one extra foreign key that Jason mentioned in his post, which prevents you from adding a User_Area with an invalid Area_City combination.
The main limitation I was experiencing due to my lack of Schema knowledge was that I didn't realize I could create a foreign key relationship on two columns. So my foreign keys were always just one column and therefore I was never able to prevent the update anomalies that I described in these posts.
This is going to help solve a whole class of normalization issues that I have.
Thanks again you guys!
sk
July 24, 2009 at 12:03 pm
Yup, I just realized I had messed up because what I posted left out the other foreign key needed, and I had it in my code, but somewhere in the troubleshooting process I must have taken it out, then forgot to put it back in. Good Job to Jason, as he got it right. This was an interesting exercise, and I appreciated the opportunity to learn from it.
Steve
(aka smunson)
:-):-):-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply