Возникла у меня с коллегами дискурсия на тему, как правильно реализовать связь «многие-ко-многим» в базе данных. Для меня, сомнений нет, но вот коллега настаивает на своём варианте.
Условия задачи:
Есть две таблицы:
CREATE TABLE [dbo].[users](
[userid] [int] IDENTITY(1,1) NOT NULL,
[username] [varchar](50) NOT NULL,
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[userid] 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 TABLE [dbo].[roles](
[roleid] [int] IDENTITY(1,1) NOT NULL,
[rolename] [varchar](50) NOT NULL,
CONSTRAINT [PK_roles] PRIMARY KEY CLUSTERED
(
[roleid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Необходимо реализовать связь «многие-ко-многим» между этими таблицами.

Возможные варианты решения:
1) Стандартный вариант, везде описанный :
CREATE TABLE [dbo].[user_roles](
[userid] [int] NOT NULL,
[roleid] [int] NOT NULL,
CONSTRAINT [PK_user_roles] PRIMARY KEY NONCLUSTERED
(
[userid] ASC,
[roleid] ASC
))

GO
ALTER TABLE [dbo].[user_roles] WITH CHECK ADD CONSTRAINT [FK_role] FOREIGN KEY([roleid])
REFERENCES [dbo].[roles] ([roleid])
GO
ALTER TABLE [dbo].[user_roles] CHECK CONSTRAINT [FK_role]
GO
ALTER TABLE [dbo].[user_roles] WITH CHECK ADD CONSTRAINT [FK_user] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[user_roles] CHECK CONSTRAINT [FK_user]
GO

2) Вариант предложенный коллегой:
CREATE TABLE [dbo].[user_roles2](
[user_roles_id] [int] IDENTITY(1,1) NOT NULL,
[userid] [int] NOT NULL,
[roleid] [int] NOT NULL,
CONSTRAINT [PK_user_roles2] PRIMARY KEY CLUSTERED
(
[user_roles_id] ASC
))
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_unique_user_roles] ON [dbo].[user_roles2]
(
[userid] ASC,
[roleid] ASC
)
GO

ALTER TABLE [dbo].[user_roles2] WITH CHECK ADD CONSTRAINT [FK_user_roles2_roles] FOREIGN KEY([roleid])
REFERENCES [dbo].[roles] ([roleid])
GO
ALTER TABLE [dbo].[user_roles2] CHECK CONSTRAINT [FK_user_roles2_roles]
GO
ALTER TABLE [dbo].[user_roles2] WITH CHECK ADD CONSTRAINT [FK_user_roles2_user] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([userid])
GO
ALTER TABLE [dbo].[user_roles2] CHECK CONSTRAINT [FK_user_roles2_user]
GO

Аргумент против второго варианта – дополнительный индекс увеличивает время вставки записей.
Аргументы коллеги за второй вариант:
— я традиционно так делал, и ничего
— при редактировании записей в таблице user_roles2 поле user_roles_id можно использовать в разделе where оператора update .
Уважаемое сообщество, посоветуйте, как лучше убедить коллегу правильно реализовать связь «многие-ко-многим».