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、如果需要分页、或搜索职称、工序或车间名称时,虽能实现,但查询更加复杂。
...
综上所述,省去“链表” 的代价着实不小,不建议采用上述尝试的方式。
如果哪位大神针对省去 “链表” 有更好的设计和方案,欢迎评论区留言!