LINQ to SQL Advanced Series (ii) M:M relationship

In the LINQ to SQL Advanced Series (i), we talked about two basic relationships 1:m and 1:1 in a database. And in the real world, there is a m:m relationship. For example, a teacher can have more than one student, and a student can have multiple teachers. The relationship between teacher and student is a many-to-many relationship. How are these relationships reflected in the database?

In the C#3.0 Starter series (10)-Join operation, we mentioned the join operation in the m:m relationship. Oh, the original, m:m relationship in the database, is still through 1:m to reflect. For example, within a domain, a user can be added to multiple group, and a group can contain more than one user. User does not have a direct relationship with group, but rather through a third table useringroup. The relationship between the user and Useringroup is 1:m, the relationship key is UserID, and group and Useringroup are 1:m, and the relationship key is GroupID, so that we pass the third table, so that user and group have a relationship. Their relationship is m:m.

The scripts for these three tables are as follows:

SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
IF not EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ User] and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
BEGIN
CREATE TABLE [dbo]. [User] (
[UserId] [NCHAR] (ten) Not NULL,
[UserName] [NCHAR] (a) NULL,
CONSTRAINT [Pk_user] PRIMARY KEY CLUSTERED
(
[UserId] Asc
) on [PRIMARY]
) on [PRIMARY]
End
Go
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
IF not EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Group] and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
BEGIN
CREATE TABLE [dbo]. [Group] (
[GroupId] [NCHAR] (ten) Not NULL,
[GroupName] [NCHAR] (a) NULL,
CONSTRAINT [Pk_group] PRIMARY KEY CLUSTERED
(
[GroupId] Asc
) on [PRIMARY]
) on [PRIMARY]
End
Go
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go
IF not EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Useringroup] and OBJECTPROPERTY (ID, N ' isusertable ') = 1)
BEGIN
CREATE TABLE [dbo]. [Useringroup] (
[UserId] [NCHAR] (ten) Not NULL,
[GroupId] [NCHAR] (ten) Not NULL,
CONSTRAINT [Pk_useringroup] PRIMARY KEY CLUSTERED
(
[UserId] Asc
[GroupId] Asc
) on [PRIMARY]
) on [PRIMARY]
End
Go
IF not EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].) [Fk_useringroup_group] ') and type = ' F ')
ALTER TABLE [dbo].　 [Useringroup] With CHECK ADD CONSTRAINT [Fk_useringroup_group] FOREIGN KEY ([GroupId])
REFERENCES [dbo]. [Group] ([GroupId])
Go
ALTER TABLE [dbo]. [Useringroup] CHECK CONSTRAINT [Fk_useringroup_group]
Go
IF not EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].) [Fk_useringroup_user] ') and type = ' F ')
ALTER TABLE [dbo].　 [Useringroup] With CHECK ADD CONSTRAINT [Fk_useringroup_user] FOREIGN KEY ([UserId])
REFERENCES [dbo]. [User] ([UserId])
Go
ALTER TABLE [dbo]. [Useringroup] CHECK CONSTRAINT [Fk_useringroup_user]