Think about the following two tables:

User
 - UserID
 - UserName

Group
 - GroupID
 - GroupName

The apparent association is the fact that Customers come in Groups. This alone is a straightforward many-to-many join situation, so allows give a 3rd table:

UserGroup
 - UserID
 - GroupID

Under this textbook schema, I'm able to easily incorporate a specific User inside a specific Group by Placing a brand new record in to the UserGroup table. Prior to going any more I wish to explain which i recognize this because the optimal situation in database design.

However, I wish to also have the ability to make Groups which include all Customers automatically unless of course they're particularly excluded in some way. Realistically, I have damaged this lower into two "modes" that an organization should be in either:

  • Include Mode: Every User is excluded unless of course particularly incorporated.
  • Exclude Mode: Every User is incorporated unless of course particularly excluded.

What exactly is the easiest method to design this type of relationship? You can include posts, add tables, and also have fancy join conditions and WHERE constraints. No triggers or s'procs, please.

Add area to Group table, 'Mode' - =Include, 1=Exclude

Then, Customers in connecting table to have an 'Exclusive' Group could be put in a "NOT IN ()" list or "EXCEPT" clause when you are performing your queries whereas 'Inclusive' Groups could be queried normally.

See http://msdn.microsoft.com/en-us/library/ms188055.aspx for EXCEPT syntax/usage.

EDIT: oh, somebody published before me... hopefully this really is still useful!

A Choose query to have an 'Exclusive' Group would go something similar to this:

SELECT UserID
FROM Users
EXCEPT
SELECT UserID
FROM UserGroup
WHERE GroupID = X

To really make it more dynamic, just determine the audience-mode before building the query, then replace 'EXCEPT' with 'INTERSECT' when the Group-mode is 'Inclusive'.

That one had me thinking for a bit regarding how to easily write the ultimate query to obtain a listing of groups that the particular user is within, but here you go. In either case, setting a characteristic within the Group table is the easiest method to determine whether the audience is definitely an "Include" or "Exclude" group. Including all the create and place claims employed for testing it.

CREATE TABLE MUser (UserID INT, UserName VARCHAR(64))
GO
CREATE TABLE MGroup (GroupID INT, GroupName VARCHAR(64), GroupTypeID INT)
GO
CREATE TABLE MGroupType (GroupTypeID INT, GroupTypeName VARCHAR(64))
GO
CREATE TABLE MUserGroup (UserID INT, GroupID INT)
GO

INSERT INTO MGroupType VALUES(1, 'Include')
INSERT INTO MGroupType VALUES(2, 'Exclude')

INSERT INTO MGroup VALUES(1, 'Group 1a', 1)
INSERT INTO MGroup VALUES(2, 'Group 1b', 1)
INSERT INTO MGroup VALUES(3, 'Group 2a', 2)
INSERT INTO MGroup VALUES(4, 'Group 2b', 2)

INSERT INTO MUser VALUES (1, 'User1')
--included in 1a, 1b; excluded from 2a, 2b
INSERT INTO MUserGroup VALUES(1, 1)
INSERT INTO MUserGroup VALUES(1, 2)
INSERT INTO MUserGroup VALUES(1, 3)
INSERT INTO MUserGroup VALUES(1, 4)

INSERT INTO MUser VALUES (2, 'User2')
--included in 1a; implicitly included in 2b
INSERT INTO MUserGroup VALUES(2, 1)
INSERT INTO MUserGroup VALUES(2, 3)

INSERT INTO MUser VALUES (3, 'User3')
--implicitly included in 2b
INSERT INTO MUserGroup VALUES(3, 3)

--SELECT ALL GROUPS FOR A PARTICULAR USER
DECLARE @UserID INT
SET @UserID = 3

SELECT g.GroupName
FROM MGroup g WITH(NOLOCK)
LEFT JOIN (
    		SELECT *
    		FROM MUserGroup WITH(NOLOCK)
    		WHERE UserID = @UserID
    	) ug ON g.GroupID = ug.GroupID
WHERE (g.GroupTypeID = 1 AND ug.UserID IS NOT NULL)
OR (g.GroupTypeID = 2 AND ug.UserID IS NULL)

--SELECT ALL USERS FOR A PARTICULAR GRUP
DECLARE @GroupID INT
SET @GroupID = 4

SELECT u.UserName
FROM MUser u WITH(NOLOCK)
JOIN (SELECT * FROM MGroup WITH(NOLOCK) WHERE GroupID = @GroupID AND GroupTypeID = 2) g ON NOT EXISTS (SELECT * FROM MUserGroup ug WITH(NOLOCK) WHERE u.UserID = ug.UserID AND g.GroupID = ug.GroupID)
UNION
SELECT u.UserName
FROM MUser u WITH(NOLOCK)
JOIN MUserGroup ug WITH(NOLOCK) ON u.UserID = ug.UserID
JOIN MGroup g WITH(NOLOCK) ON ug.GroupID = g.GroupID
WHERE g.GroupID = @GroupID
    AND g.GroupTypeID = 1