InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 06:56:09 GMT20http://www.sqlservercentral.com/Forums/Topic1385868-373-1.aspxYour design is fundamentally wrong. A star is an entity, which has attributes like color, etc. Why is the identifier, the name of the star (which should have been “star_name”, not just “star”) is a complete entity in itself? No other atributes! Groupings are a relationship (NOT an entity) among the stars. This design error is called “attribute splitting” and you have huge one. You spread the attributes of a star over multiple tables using a hand grenade! Then you try splice a properly design set back from parts with PK-FK. Your encoding schemes are sloppy, too. We like fixed length codes. Why so many NULL-able columns? Why so many vague data element names? Think about your groups; you have to design data and you have failed to do that. Look at :('SG', 'Supergiants') This is a valid encoding using a nominal scale. It shows no research, but it has the right semantics for an auxillary table. But you put queries in the same encoding, behold:('10B', '10 Brightest')and('10BNSG', '10 Brightest, No Supergiants') As the list changes, you will be re-computing these hidden queries over and over and over and over. Yuck!Let me give you another quote, from the Essay "The Analytical Language of John Wilkins" by Jorge Luis Borges to give you a feel for how awful this design is: "These ambiguities, redundancies, and deficiencies recall those attributed by Dr. Franz Kuhn to a certain Chinese encyclopedia entitled Celestial Emporium of Benevolent Knowledge. On those remote pages it is written that animals are divided into (a) those that belong to the Emperor, (b) embalmed ones, (c) those that are trained, (d) suckling pigs, (e) mermaids, (f) fabulous ones, (g) stray dogs, (h) those that are included in this classification, (i) those that tremble as if they were mad, (j) innumerable ones, (k) those drawn with a very fine camel's hair brush, (l) others, (m) those that have just broken a flower vase, (n) those that resemble flies from a distance." A quick look at Wikipedia would have got your the industry standard encoding for stars. It is called the Morgan_Keenan system and it has a nice regular expression. Most stars are currently classified using the letters O, B, A, F, G, K, and M, where O stars are the hottest and the letter sequence indicates successively cooler stars up to the coolest M class. According to informal tradition, O stars are called "blue", B "blue-white", A stars "white", F stars "yellow-white", G stars "yellow", K stars "orange", and M stars "red", even though the actual star colors perceived by an observer may deviate from these colors depending on visual conditions and individual stars observed. This letter is followed by a number from 0 to 9 indicating tenths of the range between two star classes and the is the luminosity class expressed by Roman numerals I, II, III, IV and V.For example, the Sun has the spectral type G2V, which might be interpreted as "a 'yellow' two tenths towards 'orange' main-sequence star". The apparently brightest star, Sirius, has type A1V. CREATE TABLE Stars(star_name VARCHAR(30) NOT NULL PRIMARY KEY, mk_code CHAR(6) NOT NULL REFERENCES Morgan_Keenan (mk_code) ON UPDATE CASCADE, CREATE TABLE Morgan_Keenan (mk_code CHAR(6) NOT NULL PRIMARY KEY CHECK (mk_code LIKE '[OBAFGKM][0-9]I' OR mk_code LIKE '[OBAFGKM][0-9]II' OR mk_code LIKE '[OBAFGKM][0-9]III' OR mk_code LIKE '[OBAFGKM][0-9]IV' OR mk_code LIKE '[OBAFGKM][0-9]V'), mk_description VARCHAR(100) NOT NULL);INSERT INTO Stars VALUES ('Sirius', 'A1V'), ('Sol', 'G2V'), etc; Now you can use a regular expression to get subsets. For example: CREATE VIEW Red_Stars (star_name)ASSELECT star_name FROM Stars WHERE mk_code LIKE 'M%'; The top ten is a little harder, but not hard:WITH Star_LuminosityAS(SELECT star_name, mk_code, (CASE SUBSTRING (mk_code, 3, 5) WHEN 'I' THEN 1 WHEN 'II' THEN 2 WHEN 'III' THEN 3 WHEN 'IV' THEN 4 WHEN 'V' THEN 5 END AS luminosity FROM Stars),Star_Lumen_Rank (star_name, luminosity, lumen_rank)AS(SELECT star_name, luminosity, DENSE_RANK() OVER (ORDER BY luminosity DESC) FROM Star_Luminosity)SELECT * FROM Star_Lumen_Rank WHERE lumen_rank <= @in_rank)The rest of the schema seems be another design flaw. You have actions in the database! NO! NO! It is called a “[b]data[/b] base” for a reason; it has only [i][b]data[/b].[/i] This is the foundations of all tiered architecture; data and processes are separated. Wed, 21 Nov 2012 09:17:18 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1385868-373-1.aspxRevised 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 tableCREATE 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 tableCREATE 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]GOALTER TABLE [dbo].[GroupMembers] WITH CHECK ADD CONSTRAINT [FK_GroupMembers_Groups] FOREIGN KEY([GroupCode])REFERENCES [dbo].[Groups] ([GroupCode])GOALTER TABLE [dbo].[GroupMembers] CHECK CONSTRAINT [FK_GroupMembers_Groups]GOALTER TABLE [dbo].[GroupMembers] WITH NOCHECK ADD CONSTRAINT [CK_GroupMembers_MemberType] CHECK (([MemberType]='GroupDelete' OR [MemberType]='GroupAdd' OR [MemberType]='Delete' OR [MemberType]='Add'))GOALTER TABLE [dbo].[GroupMembers] CHECK CONSTRAINT [CK_GroupMembers_MemberType]GO--Create Stars tableCREATE 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 tableINSERT 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 groupsINSERT 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 membersINSERT 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 GroupMembersWHERE 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 GroupsSELECT * FROM GroupMembers WHERE GroupCode = @GroupCodeDROP TABLE GroupMembersDROP TABLE GroupsDROP TABLE StarsTue, 20 Nov 2012 18:06:02 GMTChrissy321http://www.sqlservercentral.com/Forums/Topic1385868-373-1.aspxThank 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 StarsSELECT * FROM StarGroupsSELECT * FROM StarGroupMembersDROP TABLE StarsDROP TABLE StarGroupsDROP TABLE StarGroupMembersMon, 19 Nov 2012 16:32:41 GMTChrissy321http://www.sqlservercentral.com/Forums/Topic1385868-373-1.aspxA 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 generatedYou 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.Sun, 18 Nov 2012 19:24:26 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1385868-373-1.aspxHi AllI 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 #StarsSELECT * FROM #StarGroupsSELECT * FROM #StarGroupMembersDROP TABLE #StarsDROP TABLE #StarGroupsDROP TABLE #StarGroupMembersFri, 16 Nov 2012 14:58:51 GMTChrissy321