Need Suggestions to create custom family groupings

• ambicion

  Grasshopper

  Points: 11

  More actions

  September 19, 2014 at 3:52 pm

  #145227

  Hi,

  I have a task, that for the life of me, i don't seem to find any good way to approach. I need to group members of families that need to be sub-grouped two ways, which I refer to as "Grouping Templates", each of the two ways has it's own set of "rules" to group the members that can be changed by the end user. Currently I have 3 tables that contain the base data:

  /****** Object: Table [dbo].[tbl_People] ******/

  CREATE TABLE [dbo].[tbl_People](

  [Person_ID] [int] NOT NULL,

  [Family_ID] [int] NOT NULL,

  [FirstName] [varchar](50) NOT NULL,

  [LastName] [varchar](50) NOT NULL,

  [Sex] [char](1) NOT NULL ,

  [Age] [tinyint] NOT NULL,

  CONSTRAINT [PK_tbl_People] PRIMARY KEY CLUSTERED

  (

  [Person_ID] ASC

  )

  ) ON [PRIMARY]

  GO

  CREATE TABLE [dbo].[tbl_PeopleRelationship](

  [Person_ID_From] [int] NOT NULL,

  [Relationship_ID] [smallint] NOT NULL CONSTRAINT [DF_tbl_PeopleRelationship_Relationship_ID] DEFAULT ((22)),

  [Person_ID_To] [int] NOT NULL,

  CONSTRAINT [PK_tbl_PeopleRelationship_1] PRIMARY KEY CLUSTERED

  (

  [Person_ID_From] ASC,

  [Person_ID_To] ASC

  )

  ) ON [PRIMARY]

  GO

  CREATE TABLE [dbo].[tbl_Relationships](

  [Relationship_ID] [smallint] NOT NULL,

  [RelationshipName] [varchar](50) NULL,

  [OpposingRelationship_ID] [smallint] NULL,

  CONSTRAINT [PK_tbl_Relationships] PRIMARY KEY CLUSTERED

  (

  [Relationship_ID] ASC

  )

  ) ON [PRIMARY]

  GO

  INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (1, 1, N'Paul', N'Duncan', N'M', 66)

  GO

  INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (2, 1, N'Paula', N'Duncan', N'F', 65)

  GO

  INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (3, 1, N'Michael', N'Smith', N'M', 61)

  GO

  INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (4, 1, N'Michelle', N'Smith', N'F', 61)

  GO

  INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (5, 1, N'Oliver', N'Duncan', N'M', 40)

  GO

  INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (6, 1, N'Olivia', N'Smith', N'F', 39)

  GO

  INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (7, 1, N'Wade', N'Duncan', N'M', 19)

  GO

  INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (8, 1, N'Wendy', N'Duncan', N'F', 16)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 3, 2)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 21, 3)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 21, 4)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 1, 5)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 10, 6)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 5, 7)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 5, 8)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 3, 1)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 21, 3)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 21, 4)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 1, 5)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 10, 6)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 5, 7)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 5, 8)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 21, 1)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 21, 2)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 3, 4)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 10, 5)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 1, 6)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 5, 7)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 5, 8)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 21, 1)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 21, 2)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 3, 3)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 10, 5)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 1, 6)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 5, 7)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 5, 8)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 2, 1)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 2, 2)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 11, 3)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 11, 4)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 19, 6)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 1, 7)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 1, 8)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 11, 1)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 11, 2)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 2, 3)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 2, 4)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 19, 5)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 1, 7)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 1, 8)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 6, 1)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 6, 2)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 6, 3)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 6, 4)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 2, 5)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 2, 6)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 4, 8)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 6, 1)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 6, 2)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 6, 3)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 6, 4)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 2, 5)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 2, 6)

  GO

  INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 4, 7)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (1, N'Parent', 2)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (2, N'Child', 1)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (3, N'Spouse', 3)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (4, N'Sibling', 4)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (5, N'GrandParent', 6)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (6, N'GrandChild', 5)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (7, N'Cousin', 7)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (8, N'Uncle/Aunt', 9)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (9, N'Nephew/Niece', 8)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (10, N'Parent-in-law', 11)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (11, N'Child-in-law', 10)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (12, N'Sibling-in-law', 12)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (13, N'Step-Parent', 14)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (14, N'Step-Child', 13)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (15, N'Step-Sibling', 15)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (16, N'Half-Sibling', 16)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (17, N'Adoptive Parent', 18)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (18, N'Adoptive Child', 17)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (19, N'Couple', 19)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (20, N'Other Blood', 20)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (21, N'Other Non-Blood', 21)

  GO

  INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (22, N'Unknown', 22)

  GO

  The tbl_People contains the basic information for each individual, tbl_Relationships has the possible relationships that one person can have with another and lastly tbl_PeopleRelationship is a many to many join of the previous tables. If you query them you'll get how each person relates to each other.

  SELECT P1.Person_ID PidF ,

  P1.FirstName ,

  P1.LastName ,

  P1.Sex ,

  P1.Age ,

  PR1 RelationToRight ,

  PR2 RelationToLeft ,

  P2.Person_ID PidT ,

  P2.FirstName ,

  P2.LastName ,

  P2.Sex ,

  P2.Age

  FROM dbo.tbl_PeopleRelationship PR

  INNER JOIN dbo.tbl_People P1 ON PR.Person_ID_From = P1.Person_ID

  INNER JOIN dbo.tbl_People P2 ON PR.Person_ID_To = P2.Person_ID

  INNER JOIN ( SELECT r1.Relationship_ID ,

  R1.RelationshipName AS PR1 ,

  R2.RelationshipName AS PR2

  FROM tbl_Relationships AS R1

  INNER JOIN tbl_Relationships AS R2 ON R1.Relationship_ID = R2.OpposingRelationship_ID

  ) Rels ON PR.Relationship_ID = Rels.Relationship_ID

  Possibly i'll have to create a table to save the "rules" each of the "Grouping Templates" similar to this one:

  CREATE TABLE [dbo].[tbl_FamilyGroup](

  [FamilyGroup_ID] [int] NOT NULL,

  [Template_ID] [int] NOT NULL,

  [Relationship_ID] [smallint] NOT NULL,

  [GroupNum] [tinyint] NOT NULL,

  [MinAge] [tinyint] NOT NULL,

  [MaxAge] [tinyint] NOT NULL,

  [MaxMembers] [tinyint] NOT NULL,

  [Priority] [tinyint] NOT NULL,

  [Void] [bit] NOT NULL,

  CONSTRAINT [PK_tbl_FamilyGroup] PRIMARY KEY CLUSTERED

  (

  [FamilyGroup_ID] ASC

  )

  )

  This is a graphic representation of the sample family:

  

  With Template A the family would be grouped this way:

  

  And with Template B they would group like this:

  

  Basically in this example the rules for groupings in this example are:

  In Template A is to group any marriages and underage children together with their parents.

  Paul/Paula -----> Married

  Michael/Michelle -----> Married

  Oliver -----> Not Married

  Olivia/Wendy -----> Not Married, Underage Child Defaults to Mother

  Wade -----> Over 18, Not Married

  In Template B we can group marriages and any underage children they have or couples with any underage children they have so you get:

  Paul/Paula -----> Married

  Michael/Michelle -----> Married

  Oliver/Olivia/Wendy ------->Not Married couple with underage children in common

  Wade -----> Over 18, Not Married

  So finally i need to have a query or process that would give me this result or something similar:

  FirstName LastName Sex Age Template Group

  Paul Duncan M 66 A 1

  Paula Duncan F 65 A 1

  Michael Smith M 61 A 2

  Michelle Smith F 61 A 2

  Oliver Duncan M 40 A 3

  Olivia Smith F 39 A 4

  Wendy Duncan F 16 A 4

  Wade Duncan M 19 A 5

  Paul Duncan M 66 B 1

  Paula Duncan F 65 B 1

  Michael Smith M 61 B 2

  Michelle Smith F 61 B 2

  Oliver Duncan M 40 B 3

  Olivia Smith F 39 B 3

  Wendy Duncan F 16 B 3

  Wade Duncan M 19 B 4

  This would be less difficult if the "rules" were fixed but the end user want to be able to modify grouping parameters like maybe change the age limit for underage children or maybe allow a married couple to group with the nephew if a parent is not in the family. Right now I have no real solution for this, any help with this would be greatly appreciated.