I'm trying to create a proper database layout for a project I'm working on, however I can't seem to work out which is best.
我正在尝试为我正在进行的项目创建一个正确的数据库布局,但是我似乎无法解决哪个是最好的。
Basically the "app" is something where a User can be assigned to many Products, and a Product can be have many Customers.
基本上,“app”可以将用户分配给许多产品,而产品可以拥有许多客户。
From here, each Customer has a Service, which is specific to that Customer and Product.
从这里开始,每个客户都有一个特定于该客户和产品的服务。
A Service can have many Incidents, but an Incident can only be assigned to one Service.
服务可以有许多事件,但事件只能分配给一个服务。
A User can also have Incidents, but an Incident can only have one User.
用户也可以有事件,但事件只能有一个用户。
Here is the two designs I have made for this:
这是我为此做的两个设计:
As you can see, the left design has a table specific tables for the Many-Many relationships, where as the right one has a overall pivot table for them all.
正如您所看到的,左侧设计具有针对Many-Many关系的特定于表的表,其中右侧的表具有针对它们的整体数据透视表。
I see both of these methods working (in my head) - however since I'm not the best with this, are there any downsides to either of these methods? And do you see any problems I'll run into, in the future?
我看到这两种方法都起作用(在我的脑海中) - 但是由于我不是最好的,这些方法中是否有任何缺点?你看到我将来遇到的任何问题吗?
Also, is the right one even a proper way of doing it?
而且,正确的方法是正确的吗?
I'm also going to be using the Eloquent ORM.
我也将使用Eloquent ORM。
1 个解决方案
#1
3
Look up 'Third Normal Form'. This gives rules on how to design tables. You could take one or other of your current designs and apply the three rules to see where you get to.
查找“第三范式”。这给出了如何设计表的规则。您可以采用您当前的一个或另一个设计并应用这三个规则来查看您的位置。
I would say the one on the right is wrong: too much duplicate information, and unclear relationships. The one on the left is OK, but the two Many-Many tables are redundant. You know a Product and Customer are linked because it's in the Service table. You know a User and Product are linked because its in the Incident + Service tables.
我会说右边的那个是错的:重复的信息太多,关系不清楚。左边的那个是好的,但是两个很多桌子都是多余的。您知道产品和客户是链接的,因为它位于服务表中。您知道用户和产品已链接,因为它位于Incident + Service表中。
Cheers -
After clarification, a User belongs to a Customer. In that case, add CustomerID to the User table - this is important data about the user and should be included. It will allow you to stop the User raising Incidents on Products not associated with the Customer. This also will enable you to list the Products associated with the User via the Customer the User is associated with. Further, the Customer should have it's ID on Product, and Service should have the ProductId, not the CustomerID, as the Service is associated with a Product and only with a Customer via the Product it is associated with.
澄清后,用户属于客户。在这种情况下,将CustomerID添加到User表 - 这是关于用户的重要数据,应该包含在内。它将允许您停止用户在与客户无关的产品上引发事件。这也使您能够通过与用户相关联的客户列出与用户关联的产品。此外,客户应在产品上拥有它的ID,而服务应具有ProductId,而不是CustomerID,因为服务与产品相关联,并且仅与客户通过与之关联的产品相关联。
#1
3
Look up 'Third Normal Form'. This gives rules on how to design tables. You could take one or other of your current designs and apply the three rules to see where you get to.
查找“第三范式”。这给出了如何设计表的规则。您可以采用您当前的一个或另一个设计并应用这三个规则来查看您的位置。
I would say the one on the right is wrong: too much duplicate information, and unclear relationships. The one on the left is OK, but the two Many-Many tables are redundant. You know a Product and Customer are linked because it's in the Service table. You know a User and Product are linked because its in the Incident + Service tables.
我会说右边的那个是错的:重复的信息太多,关系不清楚。左边的那个是好的,但是两个很多桌子都是多余的。您知道产品和客户是链接的,因为它位于服务表中。您知道用户和产品已链接,因为它位于Incident + Service表中。
Cheers -
After clarification, a User belongs to a Customer. In that case, add CustomerID to the User table - this is important data about the user and should be included. It will allow you to stop the User raising Incidents on Products not associated with the Customer. This also will enable you to list the Products associated with the User via the Customer the User is associated with. Further, the Customer should have it's ID on Product, and Service should have the ProductId, not the CustomerID, as the Service is associated with a Product and only with a Customer via the Product it is associated with.
澄清后,用户属于客户。在这种情况下,将CustomerID添加到User表 - 这是关于用户的重要数据,应该包含在内。它将允许您停止用户在与客户无关的产品上引发事件。这也使您能够通过与用户相关联的客户列出与用户关联的产品。此外,客户应在产品上拥有它的ID,而服务应具有ProductId,而不是CustomerID,因为服务与产品相关联,并且仅与客户通过与之关联的产品相关联。