Учитывая следующие таблицы / отношения

РЕДАКТИРОВАТЬ: Которая, если заполняется с использованием SQL ниже, должна возвращать это….

 SELECT     TOP (100) PERCENT dbo.Task.Name AS Expr1, dbo.Role.Name FROM dbo.Role INNER JOIN dbo.RoleTask ON dbo.Role.Id = dbo.RoleTask.RoleId INNER JOIN                       dbo.Task ON dbo.RoleTask.TaskId = dbo.Task.Id ORDER BY dbo.Task.Name, dbo.Role.Name
  

Цель

Я пытаюсь заполнить RoleGroup (ту, которая просто висит там!) группами ролей, определяемыми отношением «многие ко многим» между ролью и задачей, признавая, что некоторые из них уже могут быть в таблице RoleGroup.

РЕДАКТИРОВАТЬ: Итак, учитывая приведенные выше результаты примера, это то, что мне нужно увидеть в RoleGroup (я изменил это со времени исходного сообщения, чтобы, надеюсь, было понятнее, чего я пытаюсь достичь) …

 GroupId      RoleId
1            Plumber
2            Gardener
2            Topiary Guru
3            Electrician
4            Cleaner
4            Housekeeping Supervisor
4            Toilet Cleaning Specialist  
5            Housekeeping Supervisor
  

Объяснение результатов

В связи с тем, что роли были связаны с определенными задачами, группы ролей могут быть идентифицированы.

В моем примере «Уборщица, супервайзер по ведению домашнего хозяйства и специалист по уборке туалетов» были связаны с задачами «Уборка туалета». Поэтому я могу сказать, что это группа, и хотел бы извлечь эту информацию.

Аналогично, «Супервайзер по ведению домашнего хозяйства» был связан с задачами «Проверки туалета»; и никакие другие роли этого не делали. Это означает, что должна быть извлечена еще одна новая группа (2 — Супервизор по ведению домашнего хозяйства).

Если бы «Супервайзер по ведению домашнего хозяйства» был связан с другой задачей, и никаких других ролей не было, мне не нужно было бы создавать другую группу, поскольку она уже была распознана.

О, я пытаюсь достичь этого с помощью SQL в SQL Server 2008.

Приветствуются любые советы.

SQL

 USE TestDatabase
GO

CREATE TABLE [dbo].[Task](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Type] PRIMARY KEY CLUSTERED 
(
    [Id] 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

CREATE TABLE [dbo].[Department](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
    [Id] 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

CREATE TABLE [dbo].[RoleGroup](
    [Id] [int] NOT NULL,
    [RoleId] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Role](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [DepartmentId] [int] NOT NULL,
 CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED 
(
    [Id] 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

CREATE TABLE [dbo].[RoleTask](
    [RoleId] [int] NOT NULL,
    [TaskId] [int] NOT NULL,
 CONSTRAINT [PK_RoleTask] PRIMARY KEY CLUSTERED 
(
    [RoleId] ASC,
    [TaskId] 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
  

 /****** Object:  ForeignKey [FK_Role_Department]    Script Date: 05/20/2011 17:56:49 ******/
ALTER TABLE [dbo].[Role]  WITH CHECK ADD  CONSTRAINT [FK_Role_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([Id])
GO
ALTER TABLE [dbo].[Role] CHECK CONSTRAINT [FK_Role_Department]
GO

/****** Object:  ForeignKey [FK_RoleTask_Role]    Script Date: 05/20/2011 17:56:49 ******/
ALTER TABLE [dbo].[RoleTask]  WITH CHECK ADD  CONSTRAINT [FK_RoleTask_Role] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Role] ([Id])
GO
ALTER TABLE [dbo].[RoleTask] CHECK CONSTRAINT [FK_RoleTask_Role]
GO

/****** Object:  ForeignKey [FK_RoleTask_Task]    Script Date: 05/20/2011 17:56:49 ******/
ALTER TABLE [dbo].[RoleTask]  WITH CHECK ADD  CONSTRAINT [FK_RoleTask_Task] FOREIGN KEY([TaskId])
REFERENCES [dbo].[Task] ([Id])
GO
ALTER TABLE [dbo].[RoleTask] CHECK CONSTRAINT [FK_RoleTask_Task]
GO
  

 /** DATA **/

INSERT INTO [Department] ([Id], [Name]) VALUES (1, 'Housekeeping');
INSERT INTO [Department] ([Id], [Name]) VALUES (2, 'Security');
INSERT INTO [Department] ([Id], [Name]) VALUES (3, 'External Maintenance');
INSERT INTO [Department] ([Id], [Name]) VALUES (4, 'Internal Maintenance');

INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (1, 'Cleaner', 1);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (2, 'Housekeeping Supervisor', 1);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (3, 'Toilet Cleaning Specialist', 1);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (4, 'Security Guard', 2);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (5, 'Electrician', 4);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (6, 'Plumber', 4);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (7, 'Gardener', 3);
INSERT INTO [Role] ([Id], [Name], [DepartmentId]) VALUES (8, 'Topiary Guru', 3);

INSERT INTO [Task] ([Id], [Name]) VALUES (1, 'Toilet Clean');
INSERT INTO [Task] ([Id], [Name]) VALUES (2, 'Light Out');
INSERT INTO [Task] ([Id], [Name]) VALUES (3, 'Blocked Sink');
INSERT INTO [Task] ([Id], [Name]) VALUES (4, 'Toilet Inspection');
INSERT INTO [Task] ([Id], [Name]) VALUES (5, 'Leaky Tap');
INSERT INTO [Task] ([Id], [Name]) VALUES (6, 'Bush too bushy');
INSERT INTO [Task] ([Id], [Name]) VALUES (7, 'Mop Floor');

INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (1, 1);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (2, 1);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (3, 1);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (5, 2);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (6, 3);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (2, 4);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (6, 5);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (7, 6);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (8, 6);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (1, 7);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (2, 7);
INSERT INTO [RoleTask] ([RoleId], [TaskId]) VALUES (3, 7);
  

Я не уверен, что правильно понимаю ваши вопросы, но, как предположили комментаторы, у вас, вероятно, проблема с вашей схемой.

Во-первых, группа ролей — это роль, скрывающаяся под другим именем; единственное отличие, основанное на вашей схеме, заключается в том, что она не обязательно принадлежит отделу. Но опять же, вы могли бы сказать то же самое и о ролях: даже в компаниях большого размера часто бывает пользователь / роль, которая отчитывается перед несколькими начальниками и, следовательно, принадлежит нескольким отделам.

Итак, на что вы действительно смотрите, так это на иерархию групп. Если вы абстрагируетесь от происходящего в будущем, вы будете рады возможности назначить задачу нескольким ролям за один раз. Вы могли бы разумно поместить роли и группы ролей в одну таблицу roles вместе с таблицей role2role, которая позволяет связать их друг с другом: некоторые роли назначают другие роли, но обратное неверно; некоторые роли принадлежат нескольким группам ролей; а группы ролей имеют несколько групп; это ориентированный граф, первичным ключом которого будет a (ParentID, Id), оба из которых ссылаются на роли (Id).

Кроме того, ваша таблица RoleTask, по сути, уже отвечает на ваш запрос. Но вам все равно нужна новая таблица с именем RoleGroup. И если я получу объяснения, которые вы даете в комментариях к вашему вопросу, добавление новой задачи и новой связанной роли автоматически приведет к созданию новой группы ролей. Это инъективное свойство должно намекать на то, что задачи тоже являются ролями, скрывающимися под другим именем.

Кроме того, обратите внимание, что некоторые задачи могут быть разделены на несколько отдельных задач, и что эти отдельные подзадачи могут быть частью нескольких более крупных задач. Это также ориентированный граф.

В любом случае, это означает, что вам следует рассмотреть возможность объединения всех этих таблиц вместе. Чтобы иметь возможность легко добавлять внешние ключи, вы могли бы разделить их на три части, но если вы это сделаете, я бы посоветовал, чтобы задачи и ролевая группа были урезаны до поля Id, которое является как первичным ключом, так и внешним ключом к ролям (Id).

По тому же принципу вы также можете захотеть заглянуть в таблицу Department и применить там ту же логику. Для этого может потребоваться несколько дополнительных деталей, например, ManagerID, но, в конце концов, это роль и под другим именем: ее основная цель — группировать пользователей вместе, и, возможно, все пользователи в отделе должны получать некоторые задачи в некоторых случаях. Это вписывается прямо в ориентированный граф, описанный выше.

И последнее, но не менее важное: возможно, стоит отметить, что отдельные пользователи (которые не показаны на вашей диаграмме, но которые, несомненно, присутствуют в вашей схеме) также являются самостоятельными ролями. Вполне может быть, что задача конкретно назначена отдельному пользователю, а не роли или их группе. Кроме того, вы никогда не знаете, когда менеджер захочет делегировать свои полномочия своему секретарю, потому что он в отпуске в конце месяца.

Теперь, возможно, безусловно, можно управлять всей этой неразберихой, используя множество таблиц и связей между ними, точно так же, как вы пытаетесь сделать сейчас. Но вы могли бы также ввести две новые таблицы, скажем, Perm (как в разрешениях) и PermPerm (как в назначениях разрешений). И сделайте так, чтобы отдельные узлы (т. Е. Пользователь, отдел, роль, задача) наследовались от Perm. Это позволит вам управлять всем вашим графиком разрешений из одной таблицы: PermPerm.

О, и если у вас есть час, рассмотрите возможность просмотра этого видео: ACL мертв. В нем содержится интересная информация об управлении разрешениями: