多对多关系? [英] Many to many relationship?
问题描述
伙计们，我正在努力为我的公司制作一个简单的票证生成系统，以吸引人.现在，我的MSSQL数据库中有一个名为tblTicket的表和另一个名为tblEngineer的表.
Guys I am trying to make a simple ticket generation system for my company as a favor. For now, I have a table called tblTicket and another table called tblEngineer in my MSSQL database.
我的应用程序是C#Windows窗体，因此在新的票证生成窗体上，我有许多文本框和一个tblEngineer填充的工程师.生成票证后，以这种形式输入的所有信息都与tblEngineer中的EngineerID一起存储在tblTicket中.
My application is in C# windows forms, so on the new ticket generation form, I have many textboxes and a comboBox for assigning engineer which is being populated by tblEngineer. Upon ticket generation, all the information entered in this form is stored in tblTicket along with EngineerID from tblEngineer.
工作得很好，但是随后我的客户要求我添加选项，以便可以在一张票证上分配3名工程师.
It was working great but then my client asked me to add option so that 3 engineers could be assigned on a single ticket.
将来，我将必须开发一个工程师模块"，在该模块中，工程师将只能看到分配给他的票证.它将包括用于身份验证的登录系统.因此，如果生成新票证并将其分配给3个工程师，则只有3个工程师应该能够看到该票证，而其他工程师则不能.
In the future, I will have to develop a "engineer module" in which engineer will be able to see tickets assigned to him only. It will include a login system for authentication purpose. So if a new ticket is generated and is assigned to 3 engineers, only that 3 engineers should be able to see the ticket and not the others.
我应该如何采用这种方法?如果只有一名工程师，那真的很容易.现在，我需要使用票证ID和多个工程师ID制作一个多对多表，例如tblAssignedEng吗?被称为外键?我对SQL没有太多的经验，所以我在这里有点挣扎，希望能得到任何帮助.
How should I go about this approach ? It was really easy if there was only one engineer. Now do I need to make a many-to-many table like tblAssignedEng with ticket id and multiple engineer ids? referenced as a foreign key? I haven't much experience with SQL so I am kinda struggling here and any help would be appreciated.
推荐答案
标准做法就是这样，例如...
Standard practice would be this, as an example...
您有一个"tblEngineer"表...
You have a "tblEngineer" table...
tblEngineer
-----------
(PK) EngineerId
EngineerName
还有一个"tblTicket"表...
And a "tblTicket" table...
tblTicket
---------
(PK) TicketId
TicketDetails
您现在添加了一个名为"tblEngineerTickets"(或类似名称)的链接表，该表引用了工程师及其票证的ID ...
You now add a link table called "tblEngineerTickets" (or similar) which references the Ids of both the Engineer and their tickets...
tblEngineerTickets
------------------
(PK) EngineerTicketId
(FK) EngineerId
(FK) TicketId
这样，您可以将所有故障单详细信息和工程师详细信息分开保存，并仅使用ID进行链接...链接表如下所示...
So that way, you keep all the Ticket Details and the Engineer details separately, and link them using ONLY the Ids... the link table would look something like this...
EngineerId | TicketId
------------+----------
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
2 | 3
这样，您可以将多个工程师分配给一张票证，和/或将多个票证分配给一个工程师.
This way, you can have multiple engineers assigned to one ticket, and/or multiple tickets assigned to an engineer.
这是最佳做法，它为您提供了最大的扩展机会.如果您只是向现有的工程师表中添加字段，例如"Ticket1"，"Ticket2"，"Ticket3"等，那么您实际上将对代码施加限制，并且可能必须继续进行下去添加代码以添加列.
This is best practice and it gives you the most opportunity for expansion. If you were to just add fields to your existing Engineer tables saying "Ticket1", "Ticket2", "Ticket3" etc... you would be effectively be placing a limit on the code, and potentially you'd have to keep going in to the code to add columns.
这篇关于多对多关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
