SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Advanced group design


Advanced group design

Author
Message
Chrissy321
Chrissy321
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1232 Visits: 4698
Hi All

I ask you guidance on this design question because this seems to be one of those things where if you don't get it right and it gets implemented much suffering will follow...

I need to design some advanced group functionality. I am calling the three functions I need 'group of groups' and 'group minus group' and 'groups plus manual'

Given the group 'Blue and Yellow'. Clearly I can add all the individual blue and yellow stars to this group but I would like to define the 'Blue and Yellow' group as being comprised of the two groups: Bluestars and Yellowstars.

Given the group '10 Brightest No Supergiants'. I would like to define this as being composed of the members of the group '10 Brightest' minus the group 'Supergiants'.

The group 'Blue stars and my current favorite star' should be composed of the group Bluestars and one other star manually selected.

Any design ideas would be much appreciated.


CREATE TABLE #Stars (Star varchar(30))
INSERT INTO #Stars (Star) VALUES ('Sirius')
INSERT INTO #Stars (Star) VALUES ('Canopus')
INSERT INTO #Stars (Star) VALUES ('Rigil Kentaurus')
INSERT INTO #Stars (Star) VALUES ('Arcturus')
INSERT INTO #Stars (Star) VALUES ('Vega')
INSERT INTO #Stars (Star) VALUES ('Capella')
INSERT INTO #Stars (Star) VALUES ('Rigel')
INSERT INTO #Stars (Star) VALUES ('Procyon')
INSERT INTO #Stars (Star) VALUES ('Achernar')
INSERT INTO #Stars (Star) VALUES ('Betelgeuse')

CREATE TABLE #StarGroups (StarGroup varchar(45))
INSERT INTO #StarGroups (StarGroup) VALUES ('10 Brightest')
INSERT INTO #StarGroups (StarGroup) VALUES ('Supergiants')
INSERT INTO #StarGroups (StarGroup) VALUES ('Bluestars')
INSERT INTO #StarGroups (StarGroup) VALUES ('Yellowstars')

INSERT INTO #StarGroups (StarGroup) VALUES ('10 Brightest No Supergiants')
INSERT INTO #StarGroups (StarGroup) VALUES ('Blue and Yellow')

INSERT INTO #StarGroups (StarGroup) VALUES ('Blue stars and my current favorite star')

CREATE TABLE #StarGroupMembers (StarGroup varchar(15),Star varchar(30))
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Sirius')
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Canopus')
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Rigil Kentaurus')
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Arcturus')
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Vega')
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Capella')
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Rigel')
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Procyon')
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Achernar')
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('10 Brightest','Betelgeuse')

INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('Supergiants','Rigel')
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('Supergiants','Achernar')
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('Supergiants','Betelgeuse')

INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('Bluestars','Rigel')

INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('Yellowstars','Procyon')
INSERT INTO #StarGroupMembers (StarGroup,Star) VALUES ('Yellowstars','Capella')

SELECT * FROM #Stars
SELECT * FROM #StarGroups
SELECT * FROM #StarGroupMembers

DROP TABLE #Stars
DROP TABLE #StarGroups
DROP TABLE #StarGroupMembers
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14202 Visits: 12197
A lot depends on how frequently new groups are defined, how frequently group definitions change (whether by adding new members or removing existing members or adding new subgroups or removing existing subgroups), and how deeply nested are your groups. I imagine the nesting depth will be fairly small, so it's fairly easy to design a structure in which it is easy to update group membership and to introduce new groups, and easy to provide the three functions you mention.

Since groups are potentially very large (there are a lot of red stars, for example) and since a star can be a member of many groups, it's probably a good idea to use something shorter than names to make links within the data structure (but this, of course, should be made invisible to users). That just needs a table for each star/group which uses its name and provides its identifier, perhaps as a bigint (I can't remember whether there are enough stars to need that) which can be automatically generated

You will need an additional table in addition to those in the code you posted to represent the subgroup relationship.

It is then straightforward to build total group membership from subgroups and individual members using a recursive CTE, and creating groups which are built from other groups, with or without additional individually specified elemets, becomes trivial. Subtraction is trivial too.

Tom

Chrissy321
Chrissy321
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1232 Visits: 4698
Thank for for your response. I was using Stars as sample data, my actual data is much more mundane.

I expect my groups to contain at most 100 hundred members and the updates to be infrequent, perhaps dozens a month.

I have a new design below. I have not written a way to select group members from this structure but I expect that would be fairly easy.

Please freely critique this new structure.

You mention an additional table. What could that structure look like? Would that allow for nested subgroups?

CREATE TABLE Stars (Star varchar(30))
INSERT INTO Stars (Star) VALUES ('Sirius')
INSERT INTO Stars (Star) VALUES ('Canopus')
INSERT INTO Stars (Star) VALUES ('Rigil Kentaurus')
INSERT INTO Stars (Star) VALUES ('Arcturus')
INSERT INTO Stars (Star) VALUES ('Vega')
INSERT INTO Stars (Star) VALUES ('Capella')
INSERT INTO Stars (Star) VALUES ('Rigel')
INSERT INTO Stars (Star) VALUES ('Procyon')
INSERT INTO Stars (Star) VALUES ('Achernar')
INSERT INTO Stars (Star) VALUES ('Betelgeuse')

CREATE TABLE StarGroups (StarGroup varchar(45))
INSERT INTO StarGroups (StarGroup) VALUES ('10 Brightest')
INSERT INTO StarGroups (StarGroup) VALUES ('Supergiants')
INSERT INTO StarGroups (StarGroup) VALUES ('Bluestars')
INSERT INTO StarGroups (StarGroup) VALUES ('Yellowstars')

INSERT INTO StarGroups (StarGroup) VALUES ('10 Brightest No Supergiants')
INSERT INTO StarGroups (StarGroup) VALUES ('Blue and Yellow')

INSERT INTO StarGroups (StarGroup) VALUES ('Blue stars and my current favorite star')

CREATE TABLE StarGroupMembers (StarGroup varchar(50),Member varchar(30),MemberType varchar(30))
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('10 Brightest','Sirius','Star')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('10 Brightest','Canopus','Star')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('10 Brightest','Rigil Kentaurus','Star')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('10 Brightest','Arcturus','Star')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('10 Brightest','Vega','Star')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('10 Brightest','Capella','Star')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('10 Brightest','Rigel','Star')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('10 Brightest','Procyon','Star')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('10 Brightest','Achernar','Star')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('10 Brightest','Betelgeuse','Star')

INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('Supergiants','Rigel','Star')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('Supergiants','Achernar','Star')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('Supergiants','Betelgeuse','Star')

INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('Bluestars','Rigel','Star')

INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('Yellowstars','Procyon','Star')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('Yellowstars','Capella','Star')

INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('Blue and Yellow','Bluestars','GroupAdd')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('Blue and Yellow','Yellowstars','GroupAdd')

INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('10 Brightest No Supergiants','10 Brightest','GroupAdd')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('10 Brightest No Supergiants','Supergiants','GroupSubtract')

INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('Blue stars and my current favorite star','Bluestars','GroupAdd')
INSERT INTO StarGroupMembers (StarGroup,Member,MemberType) VALUES ('Blue stars and my current favorite sta','Procyon','Star')

SELECT * FROM Stars
SELECT * FROM StarGroups
SELECT * FROM StarGroupMembers

DROP TABLE Stars
DROP TABLE StarGroups
DROP TABLE StarGroupMembers
Chrissy321
Chrissy321
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1232 Visits: 4698
Revised DDL/DML.

The data structure seems reasonable but I don't have an additional table that Tom suggested I may need.

My initial attempt to write a select statement to solve a group compromised of one group minus a second group has failed miserably.

I will struggle on. Comments and suggestions are certainly welcome.



--Create Groups table
CREATE TABLE [dbo].[Groups](
[GroupCode] [varchar](12) NOT NULL,
[GroupName] [varchar](40) NULL,
[GroupType] [varchar](12) NOT NULL,
[GroupDescription] [varchar](240) NULL,
CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED
(
[GroupCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


--Create GroupMembers table
CREATE TABLE [dbo].[GroupMembers](
[GroupCode] [varchar](12) NOT NULL,
[Member] [varchar](50) NOT NULL,
[MemberType] [varchar](12) NOT NULL,
CONSTRAINT [PK_GroupMembers] PRIMARY KEY CLUSTERED
(
[GroupCode] ASC,
[Member] ASC,
[MemberType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


ALTER TABLE [dbo].[GroupMembers] WITH CHECK ADD CONSTRAINT [FK_GroupMembers_Groups] FOREIGN KEY([GroupCode])
REFERENCES [dbo].[Groups] ([GroupCode])
GO

ALTER TABLE [dbo].[GroupMembers] CHECK CONSTRAINT [FK_GroupMembers_Groups]
GO

ALTER TABLE [dbo].[GroupMembers] WITH NOCHECK ADD CONSTRAINT [CK_GroupMembers_MemberType] CHECK (([MemberType]='GroupDelete' OR [MemberType]='GroupAdd' OR [MemberType]='Delete' OR [MemberType]='Add'))
GO

ALTER TABLE [dbo].[GroupMembers] CHECK CONSTRAINT [CK_GroupMembers_MemberType]
GO

--Create Stars table
CREATE TABLE [dbo].[Stars](
[Star] [varchar](30) NOT NULL,
CONSTRAINT [PK_Stars] PRIMARY KEY CLUSTERED
(
[Star] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--Populate sample data into stars table
INSERT INTO Stars (Star) VALUES ('Sirius')
INSERT INTO Stars (Star) VALUES ('Canopus')
INSERT INTO Stars (Star) VALUES ('Rigil Kentaurus')
INSERT INTO Stars (Star) VALUES ('Arcturus')
INSERT INTO Stars (Star) VALUES ('Vega')
INSERT INTO Stars (Star) VALUES ('Capella')
INSERT INTO Stars (Star) VALUES ('Rigel')
INSERT INTO Stars (Star) VALUES ('Procyon')
INSERT INTO Stars (Star) VALUES ('Achernar')
INSERT INTO Stars (Star) VALUES ('Betelgeuse')

--Insert groups
INSERT INTO Groups (GroupCode,Groupname,GroupType,GroupDescription) VALUES ('10B','10 Brightest','Star',NULL)
INSERT INTO Groups (GroupCode,Groupname,GroupType,GroupDescription) VALUES ('SG','Supergiants','Star',NULL)
INSERT INTO Groups (GroupCode,Groupname,GroupType,GroupDescription) VALUES ('BS','Bluestars','Star',NULL)
INSERT INTO Groups (GroupCode,Groupname,GroupType,GroupDescription) VALUES ('YS','Yellowstars','Star',NULL)
INSERT INTO Groups (GroupCode,Groupname,GroupType,GroupDescription) VALUES ('10BNSG','10 Brightest No Supergiants','Star',NULL)
INSERT INTO Groups (GroupCode,Groupname,GroupType,GroupDescription) VALUES ('BYS','Blue and Yellow','Star',NULL)
INSERT INTO Groups (GroupCode,Groupname,GroupType,GroupDescription) VALUES ('BSFAVE','Blue stars and my favorite star','Star',NULL)
--Insert groups members


INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('10B','Sirius','Add')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('10B','Canopus','Add')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('10B','Rigil Kentaurus','Add')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('10B','Arcturus','Add')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('10B','Vega','Add')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('10B','Capella','Add')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('10B','Rigel','Add')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('10B','Procyon','Add')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('10B','Achernar','Add')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('10B','Betelgeuse','Add')

INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('SG','Rigel','Add')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('SG','Achernar','Add')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('SG','Betelgeuse','Add')

INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('BS','Rigel','Add')

INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('YS','Procyon','Add')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('YS','Capella','Add')

INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('10BNSG','10B','GroupAdd')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('10BNSG','Supergiants','GroupDelete')

INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('BYS','BS','GroupAdd')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('BYS','YS','GroupDelete')

INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('BSFAVE','BS','GroupAdd')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('BSFAVE','Procyon','Add')


DECLARE @GroupCode [varchar](12)
--SET @GroupCode= 'BSFAVE'
--SET @GroupCode= '10B'
SET @GroupCode= '10BNSG'
--SET @GroupCode= 'BS'
--SET @GroupCode= 'BYS'
--SET @GroupCode= 'SG'
--SET @GroupCode= 'YS'



SELECT
@GroupCode,
GroupCode,
Member
FROM GroupMembers
WHERE
GroupCode IN
(
SELECT
Member
FROM GroupMembers GM
WHERE
GM.GroupCode = @GroupCode AND
MemberType = 'GroupAdd'
)
/*
AND GroupCode NOT IN
(
SELECT

GroupCode

FROM GroupMembers
WHERE
GroupCode IN
(
SELECT
Member
FROM GroupMembers GM
WHERE
GM.GroupCode = '10BNSG' AND
MemberType = 'GroupAdd'
)
)
*/
--SELECT * FROM Stars
--SELECT * FROM Groups
SELECT * FROM GroupMembers WHERE GroupCode = @GroupCode



DROP TABLE GroupMembers
DROP TABLE Groups
DROP TABLE Stars
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search