Take the 2-minute tour ×

I have a database with two tables: Users and Categories.

Users has these fields:

UserId        unique identifier
UserName      nvarchar
CategoryId    int

Categories has these fields:

CategoryId    int
CategoryName  nvarchar

At the moment, every user is in one category. I want to change this so that each user can be in any number of categories. What is the best way to do this?

My site does a lot of really expensive searches, so the solution needs to be as efficient as possible. I don't really want to put the list of categories each user has in a third table, as this means that when I pull back a search, each user will be represented in several rows at once (at least, this is what would happen if I implemented a search with my current, fairly crude, understanding of sql.)


If setting up a many-many relationship, is it possible to return only one row for each user?

For instance:

DECLARE @SearchUserID nvarchar(200) = 1;

FROM Users JOIN Categories JOIN CategoriesPerUser
WHERE UserId = @SearchUserID

This would return one row for each category the user belonged to. It is possible to have it only return one row?

share|improve this question
The third table is the solution to your problem. As for your queries you should have a look at exist and in. msdn.microsoft.com/en-us/library/ms177682.aspx msdn.microsoft.com/en-us/library/ms188336.aspx –  Mikael Eriksson Sep 2 '11 at 9:23
What would be the expected output of your query if you have more than one Category for a user? You are not using CategoriesPerUser in your where clause so the easiest thing to do would be to remove the table from the query. –  Mikael Eriksson Sep 2 '11 at 9:33
In your edited example, where a user is associated with multiple categories you need to make a call as to which one you want to return... perhaps as simple as SELECT TOP 1. But its not clear what you're trying to achieve. –  Jamiec Sep 2 '11 at 9:42
Another thought: If a user belongs to 1 "main" category, then its quite valid to keep the CategoryID field on User (as well has having the UsersToCategory linking table) and use this when wanting to "return 1 category". –  Jamiec Sep 2 '11 at 9:43
@Jamiec I suppose what I need is to have a query return one row per user, even if the user is in several categories, but to have all those categories represented in the row. When the data gets to my website, I am using each row of the query to generate one search result. So I want to generate a search result that lists to the page visitor all the categories to which user belongs, rather than having a separate search result for each category the user is in. –  Oliver Sep 2 '11 at 9:49

3 Answers 3

up vote 4 down vote accepted

At the moment you have a one-to-many relationship, that is to say category can be assocaited with many users, but a user can only be assocaited with one category.

You need to change this to a many-to-many relationship so that each user can be assocaited with many categories and each category can be assocaited with many users.

This is achieves by adding a table which links a userid and a category id (and removing categoryid from the user table)

Table: UserToCategory
UserId     int
CategoryId int

As for your last paragraph, this is the most efficient way of modelling your requirement. You should make a combination of UserId/CategoryId the PrimaryKey in this table to stop a user being associated with the same category twice. This stops the problem of a user returned twice for a particular category.

The SQL to find, for example, all users associated with a category would be

FROM Users u
INNER JOIN UserToCategory uc 
   ON u.UserId = uc.UserID
WHERE uc.CategoryId = 123

Edit after comments: If you have a query that finds a number of users, and you want a distinct list of categories associated with those users this could be done like

FROM Categories c
WHERE CategoryId IN
  SELECT uc.CategoryID
  FROM UserToCategory uc
    INNER JOIN Users u ON uc.UserId = u.UserID
  WHERE <some criteria here to filter users>
share|improve this answer
Thanks for your suggestion. This works great for when finding all the users in a category, but there are problems if I am trying to find all the users based on some other search parameter, and wanting to return all the categories the user belongs to. –  Oliver Sep 2 '11 at 9:31
@Oliver - see update. Also, anything is possible to find with the correct database structure. This is the correct database structure for your requirement. –  Jamiec Sep 2 '11 at 9:35

I would drop CategoryId out of Users and go for the 3d table:

- UserId
- CategoryId

If you want to search all the categories for a user you can use for example:

SELECT uc.CategoryId, c.CategoryName 
FROM UserCategories uc
JOIN Categories c ON uc.CategoryId = c.CategoryId
WHERE uc.UserId = @UserId
share|improve this answer

As this is an n-to-n Relationship (one category can have several user and one user can have several categories), the typical way to implement this would be to have a junction table.

But as you said, you don't want to create a third table for reasons of already implemented features, i guess you could also change the column "CategoryId" in the User table to "CategorieIds", which could then contain a text field. This text field could contain a list of integers, separated by a special character ("," for example). as far as i'm concerned, you should then do the split operation on your implementing code, since i don't know of any practical way to do this in sql (maybe someone could correct me here...).

You could also keep you categoryId Column then, if you wanted to implement something like a 'main' category per user.

Hope this helps and I hope to have suggested a correct way to implement this!

share|improve this answer
comma separated values in a text field makes baby jesus cry. –  Jamiec Sep 2 '11 at 9:27
Thanks for the comment, I thought of doing something like that, but it would probably be very inefficient to do a search over a string of IDs like that. –  Oliver Sep 2 '11 at 9:28
Yes, of course a third table would be the most performant option. I was just thinking of a different way to implement this if for some reason you really can't create a seperate table for the relationship - i definitely agree with Jamiecs comment! –  Chips_100 Sep 2 '11 at 9:30

Your Answer


