多对多关系桥表困境

问题描述

salesman
========
uId

salesGroupLinked
================
uId
groupId
//add performacesScore field here

group
======
groupId

我有3个表格形成多对多的关系，我将添加一个字段'performaces'（INT），以便每个销售人员可以在每个组中有一个分数。我相信它应该位于salesGroupLinked表。但是由于uId和groupId是FK，我不能插入/编辑数据（我使用phpmyadmin）。我不能让performacesScore字段唯一，因为它们可以是相同的值，例如一个销售员得到10和另一个得到相同。

I have 3 table above that formed many to many relationship, and I would add a field 'performaces' (INT) so that each salesman can have a score in each group. And I believe it should be located at salesGroupLinked table. But since uId and groupId is FK, I can't insert / edit the data (I'm using phpmyadmin). I can't make the performacesScore field unique since they can be same value for example a salesman get 10 and another get the same.

我得到了这个msg：

describe salesGroupLinked

describe salesGroupLinked

推荐答案

该工具只是告诉你可以有一个uId-groupId组合的几个条目。示例：

The tool is simply telling you that there can be several entries for a uId-groupId combination. Example:

uId  groupId  performacesScore
1    1        10
1    2        20
2    1        30
2    1        30
2    1        40
2    2        20

现在假设这些数据显示给您你先做2/1/30 a 2/1/50。工具发送到dbms的更新语句？

Now imagine this data is shown to you and you make the first 2/1/30 a 2/1/50. What update statement could the tool sent to the dbms?

update salesGroupLinked set performacesScore = 50
where uId = 2 and groupId = 1;

这将更新三条记录，而不是一条。

This would update three records instead of one.

update salesGroupLinked set performacesScore = 50
where uId = 2 and groupId = 1 and performacesScore = 30;

这将更新两个记录，而不是一个。

This would still update two records instead of one.

因此，为了正确更新和删除，必须告诉dbms什么使记录唯一。有四种可能性：

So in order to properly update and delete, you must tell the dbms what makes the records unique. There are four possibilities:

• 如果您不想更新或删除单个记录，请保留原样。


• 如果您希望能够更新，并且只能有一个条目用于uId-groupId组合，那么请告诉dbms，并使uId和groupId成为表的主键。


• 如果您希望能够更新，并且aa uId-groupId组合可能有重复，但uId-groupId-performacesScore组合将始终是唯一的，那么请将这三个表


• 如果您希望能够进行更新，并且任何组合都可能存在重复，请为此表格提供另一个技术ID列，并将其作为主键。 li>
  

• If you never want to update or delete single records, leave it as is.
• If you want to be able to update and there can only be one entry for a uId-groupId combination, then tell the dbms so and make uId plus groupId the primary key of your table.
• If you want to be able to update and there can be duplicates for a a uId-groupId combination, but a uId-groupId-performacesScore combination will always be unique, then make these three the table's primary key.
• If you want to be able to update and there can be duplicates for any combination, then give the table another column for a technical id and make this the primary key.

这篇关于多对多关系桥表困境的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！