SQL数据库表多对多关系设计--省去链表的尝试

概述

之前遇到一个MES需求，需要创建一个基础资料表 "人员信息表" ，用于其它业务的人员信息关连，因为这些人员并非系统登录帐号，所以单独进行维护。人员表涉及到字段包括：ID，工号，姓名，所属工序，所属职称，所属车间。其中，所属工序、所属职称、所属车间 为多选。例：一位人员，比如 "班长" ，可能管理多个工序，多个车间，本身扮演班长职能，操作人员不足时，也会参与操作员工作，所以职称会是 ”班长“、”操作员“ 等... ...，最后查询出来的效果：

正常的情况下，表设计，应该是 "人员表"，”工序表“、”职称表“，”车间表“，”人员工序链接表“，”人员职称链接表“，”人员车间链接表“。但有同事却觉得增加 “链表” 多一层关连，影响查询效率，下面我们就来看看换另一种设计，查询效率是否更高？

SQL中多对多关系，不用链表的设计

尝试省掉 3张链接表（“人员工序链接表”，“人员职称链接表”，“人员车间链接表”），看看是否可行。思路是在 所属工序、所属职称、所属车间 字段，多选时存入选中的ID，并用逗号隔开。

USE [master]
GO
/****** Object:  Table [dbo].[Test_车间表]    Script Date: 2024-07-26 15:55:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test_车间表](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[车间名称] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Test_车间表] 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
/****** Object:  Table [dbo].[Test_工序]    Script Date: 2024-07-26 15:55:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test_工序](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[工序名称] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Test_工序] 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
/****** Object:  Table [dbo].[Test_人员表]    Script Date: 2024-07-26 15:55:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test_人员表](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[工号] [varchar](50) NOT NULL,
	[姓名] [varchar](50) NOT NULL,
	[所属工序] [varchar](200) NULL,
	[所属职称] [varchar](200) NULL,
	[所属车间] [varchar](200) NULL,
 CONSTRAINT [PK_Test_人员表] 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
/****** Object:  Table [dbo].[Test_职称]    Script Date: 2024-07-26 15:55:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Test_职称](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[职称] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Test_职称] 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
SET IDENTITY_INSERT [dbo].[Test_车间表] ON 

INSERT [dbo].[Test_车间表] ([ID], [车间名称]) VALUES (1, N'成型一车间')
INSERT [dbo].[Test_车间表] ([ID], [车间名称]) VALUES (2, N'成型二车间')
INSERT [dbo].[Test_车间表] ([ID], [车间名称]) VALUES (3, N'成型三车间')
INSERT [dbo].[Test_车间表] ([ID], [车间名称]) VALUES (4, N'烧结车间')
INSERT [dbo].[Test_车间表] ([ID], [车间名称]) VALUES (5, N'研磨一车间')
INSERT [dbo].[Test_车间表] ([ID], [车间名称]) VALUES (6, N'研磨二车间')
INSERT [dbo].[Test_车间表] ([ID], [车间名称]) VALUES (7, N'分检车间')
SET IDENTITY_INSERT [dbo].[Test_车间表] OFF
GO
SET IDENTITY_INSERT [dbo].[Test_工序] ON 

INSERT [dbo].[Test_工序] ([ID], [工序名称]) VALUES (1, N'成型')
INSERT [dbo].[Test_工序] ([ID], [工序名称]) VALUES (2, N'烧结')
INSERT [dbo].[Test_工序] ([ID], [工序名称]) VALUES (3, N'研磨')
INSERT [dbo].[Test_工序] ([ID], [工序名称]) VALUES (4, N'分检')
SET IDENTITY_INSERT [dbo].[Test_工序] OFF
GO
SET IDENTITY_INSERT [dbo].[Test_人员表] ON 

INSERT [dbo].[Test_人员表] ([ID], [工号], [姓名], [所属工序], [所属职称], [所属车间]) VALUES (1, N'001', N'李某', N'1', N'2,3', N'4,5,7')
INSERT [dbo].[Test_人员表] ([ID], [工号], [姓名], [所属工序], [所属职称], [所属车间]) VALUES (2, N'002', N'张魁', N'3', N'1,3', N'2,3,5')
INSERT [dbo].[Test_人员表] ([ID], [工号], [姓名], [所属工序], [所属职称], [所属车间]) VALUES (3, N'003', N'王生辉', N'3', N'1', N'2,6')
SET IDENTITY_INSERT [dbo].[Test_人员表] OFF
GO
SET IDENTITY_INSERT [dbo].[Test_职称] ON 

INSERT [dbo].[Test_职称] ([ID], [职称]) VALUES (1, N'操作员')
INSERT [dbo].[Test_职称] ([ID], [职称]) VALUES (2, N'班长')
INSERT [dbo].[Test_职称] ([ID], [职称]) VALUES (3, N'主任')
SET IDENTITY_INSERT [dbo].[Test_职称] OFF
GO

各表数据如下图：

根据上述表及结构，查询语句如下：

--创建个表值函数，用于分割拼接字符

CREATE FUNCTION dbo.SplitString
(
    @List NVARCHAR(MAX),
    @Delimiter CHAR(1)
)
RETURNS @Table TABLE (ID INT)
AS
BEGIN
    DECLARE @XML XML
    SET @XML = N'<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>'

    INSERT INTO @Table(ID)
    SELECT
        CAST(T.Item.value('.', 'NVARCHAR(MAX)') AS INT)
    FROM @XML.nodes('i') T(Item)

    RETURN
END
GO

--输出查询
Select A.ID,A.工号,A.姓名,B.工序名称,A.所属职称,C.职称,A.所属车间,D.车间名称 From  Test_人员表 A
LEFT JOIN Test_工序 B ON B.ID=A.所属工序
LEFT JOIN(
Select A.ID AS ID,STRING_AGG(C.职称,',') AS 职称 from Test_人员表 A
CROSS APPLY dbo.SplitString(A.所属职称, ',') AS SplitIds
--LEFT JOIN Test_工序 B ON B.ID=A.所属工序
LEFT JOIN Test_职称  C ON C.ID=SplitIds.ID
GROUP BY A.ID
)C ON C.ID=A.ID
LEFT JOIN (
Select A.ID AS ID,STRING_AGG(D.车间名称,',') AS 车间名称 from Test_人员表 A
CROSS APPLY dbo.SplitString(A.所属车间, ',') AS AP
--LEFT JOIN Test_工序 B ON B.ID=A.所属工序
LEFT JOIN Test_车间表  D ON D.ID=AP.ID
GROUP BY A.ID
) D ON D.ID=A.ID

尝试C# 使用 LinQ查询

同样的需求，尝试在 C# 代码中实现。

人员表实体类模型：

  1 /// <summary>
  2 /// 操作员信息
  3 /// </summary>
  4 public class BS_Worker: IKeyID
  5 {
  6     [Key]
  7     public int ID { get; set; }
  8 
  9     [Display(Name = "员工编号")]
 10     [StringLength(50)]
 11     [Required]
 12     public string WKNum { get; set; }
 13 
 14     [Display(Name = "姓名")]
 15     [StringLength(100)]
 16     [Required]
 17     public string WKName { get; set; }
 18 
 19     [Display(Name = "所属工序")]
 20     [StringLength(100)]
 21     public string StationID {  get; set; }
 22 
 23     [Display(Name = "所属车间")]
 24     [StringLength(100)]
 25     public string WkShopID { get; set; }
 26 
 27     //[Display(Name = "所属线别")]
 28     //public int? PLineID { get; set; }
 29     //public A01_Station Station { get; set; }
 30 
 31     [Display(Name = "职称")]
 32     [StringLength(100)]
 33     public string TitleID { get; set; }
 34 
 35 
 36     [Display(Name = "备注")]
 37     [StringLength(200)]
 38     public string Remark { get; set; }
 39 
 40 
 41 
 42     [Display(Name = "创建人")]
 43     public int CreateUserID { get; set; }
 44     public User CreateUser { get; set; }
 45 
 46     [Display(Name = "创建时间")]
 47     public DateTime CreateTime { get; set; }
 48 
 49     [Display(Name = "系统备注")]
 50     [StringLength(200)]
 51     public string SysRemark { get; set; }
 52 
 53     [Display(Name = "状态")]
 54     public int StateID { get; set; }
 55     public Sys_Dictionary State { get; set; }
 56 
 57     [Display(Name = "最后修改时间")]
 58     public DateTime? ModifyTime { get; set; }
 59 
 60     [Display(Name = "最后修改人")]
 61     public int? ModifyUserID { get; set; }
 62     public User ModifyUser { get; set; }
 63 
 64     [Display(Name = "帐套ID")]
 65     public int? AccountIng { get; set; }
 66 
 67 
 68 
 69 
 70 
 71     /// <summary>
 72     /// 标记是否启用；
 73     /// </summary>
 74     [NotMapped]
 75     [Display(Name = "状态")]
 76     public bool Enable
 77     {
 78         get
 79         {
 80             if (StateID == 1001)    //对应数据字典1001为用启用状态；
 81             {
 82                 return true;
 83             }
 84             else
 85             {
 86                 return false;
 87             }
 88         }
 89     }
 90 
 91     #region 职称显示
 92     [NotMapped]
 93     [Display(Name = "职称ID")]
 94     public List<int> TitleList
 95     {
 96         get
 97         {
 98             var a= TitleID?.Split(',').ToList()?? new List<string>();
 99             List<int> intList = a.ConvertAll(p=>int.Parse(p));
100             return intList;                
101         }            
102     }       
103     #endregion
104 
105     #region 所属工序
106     [NotMapped]
107     [Display(Name = "所属工序ID")]
108     public List<int> StationList
109     {
110         get
111         {
112             var a = StationID?.Split(',').ToList() ?? new List<string>();
113             List<int> intList = a.ConvertAll(p => int.Parse(p));
114             return intList;
115         }
116     }
117     
118     #endregion
119 
120     #region 所属车间
121     [NotMapped]
122     [Display(Name = "所属车间ID")]
123     public List<int> WkShopList
124     {
125         get
126         {
127             var a = WkShopID?.Split(',').ToList() ?? new List<string>();
128             List<int> intList = a.ConvertAll(p => int.Parse(p));
129             return intList;
130         }
131     }
132 
133     #endregion
134 
135 }

人员表View 模型：

public class BS_WorkerViewMode
{
    [Key]
    public int ID { get; set; }

    [Display(Name = "员工编号")]
    [StringLength(50)]
    [Required]
    public string WKNum { get; set; }

    [Display(Name = "姓名")]
    [StringLength(100)]
    [Required]
    public string WKName { get; set; }

    [Display(Name = "所属工序")]
    [StringLength(100)]
    public string StationID { get; set; }

    [Display(Name = "所属车间")]
    [StringLength(100)]
    public string WkShopID { get; set; }

    [Display(Name = "职称")]
    [StringLength(100)]
    public string TitleID { get; set; }


    [Display(Name = "备注")]
    [StringLength(200)]
    public string Remark { get; set; }



    [Display(Name = "创建人")]
    public int CreateUserID { get; set; }
    public User CreateUser { get; set; }

    [Display(Name = "创建时间")]
    public DateTime CreateTime { get; set; }

    [Display(Name = "系统备注")]
    [StringLength(200)]
    public string SysRemark { get; set; }

    [Display(Name = "状态")]
    public int StateID { get; set; }
    public Sys_Dictionary State { get; set; }

    [Display(Name = "最后修改时间")]
    public DateTime? ModifyTime { get; set; }

    [Display(Name = "最后修改人")]
    public int? ModifyUserID { get; set; }
    public User ModifyUser { get; set; }

    [Display(Name = "帐套ID")]
    public int? AccountIng { get; set; }

    #region 职称显示
    [NotMapped]
    [Display(Name = "职称")]
    public string TitlesN { get; set; }
    #endregion

　　.... ...
}

查询方法（为方便，只对职称进行查询，如果把工序、车间都引入进行查询更加复杂）：

 1 private async Task<IEnumerable<BS_WorkerViewMode>> GetDataAsync(PagingInfoViewModel pagingInfo, string ttbSearchMessage, string rblEnableStatus)
 2 {
 3     IQueryable<BS_Worker> q = DB.BS_Workers;
 4 
 5     string searchText = ttbSearchMessage?.Trim();
 6     if (!string.IsNullOrEmpty(searchText))
 7     {
 8         q = q.Where(u => u.WKNum.Contains(searchText) || u.WKName.Contains(searchText) || u.Remark.Contains(searchText));
 9     }
10 
11     // 过滤启用状态
12     if (rblEnableStatus != "all")
13     {
14         q = q.Where(u => u.StateID == (rblEnableStatus == "enable" ? 1001 : 1002));
15     }
16 
17     // 获取总记录数（在添加条件之后，排序和分页之前）
18     pagingInfo.RecordCount = await q.CountAsync();
19 
20     // 排列和数据库分页
21     q = SortAndPage(q, pagingInfo);
22 
23     var wokers= await q.ToListAsync();  // 人员表，触发数据库查询，实体化数据
24     var titles=await DB.Sys_Dictionaries.Include(p=>p.DictGroup).Where(p=>p.DictGroup.Group==180).ToListAsync(); //职称表
25 
26     //平面摊开职称的记录
27     var workerView = (from woker in wokers
28               from titlelist in woker.TitleList
29               join tt in titles on titlelist equals tt.Code
30               select new BS_WorkerViewMode 
31               { 
32                   ID = woker.ID,
33                   WKNum= woker.WKNum,
34                   WKName= woker.WKName,
35                   StateID=woker.StateID,
36                   TitlesN=tt.Name,
37                   Remark =woker.Remark,
38                   CreateUserID=woker.CreateUserID,
39                   CreateTime=woker.CreateTime,
40               }
41              ).ToList();
42 
43     //分组，合并职称名称
44     var result= workerView.GroupBy(p=>p.ID).Select(g=>new BS_WorkerViewMode
45     {
46         ID=g.Key,
47         WKNum= g.Select(p => p.WKNum).First(),
48         WKName = g.Select(p => p.WKName).First(),
49         StateID=g.Select(p => p.StateID).First(),
50         CreateUserID=g.Select(p => p.CreateUserID).First(),
51         CreateTime=g.Select(p => p.CreateTime).First(),
52         Remark=g.Select(p => p. Remark).First(),
53         TitlesN = g.Select(p => p.TitlesN) // 选择每个组中的 TitlesN
54                          .Aggregate((current, next) => current + "," + next), // 将 TitlesN 连接成一个字符串
55     }).ToList();
56 
57     return result;
58 }

结论

这种表设计，虽然省去 “链表”，但查询变得更加复杂，且缺点明显：
1、未采有外键约束（关系型数据库的典型功能），当职称、工序、车间表发生删除时，人员表发生关系引用失效。

2、人员表的关系字段，先拆、再查、后重拼，整个效率低下，且不能应用索引功能。

3、如果需要分页、或搜索职称、工序或车间名称时，虽能实现，但查询更加复杂。

...

综上所述，省去“链表” 的代价着实不小，不建议采用上述尝试的方式。

如果哪位大神针对省去 “链表” 有更好的设计和方案，欢迎评论区留言！

posted @ 2024-07-26 23:13 谁的谁是谁阅读(11) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

谁的谁是谁

SQL数据库表 多对多关系设计--省去链表的尝试

概述

SQL中多对多关系，不用链表的设计

尝试C# 使用 LinQ查询

结论

公告

SQL数据库表多对多关系设计--省去链表的尝试