另一个多多场景
我的数据库模型中有 2 个需要多对多关系的特定表。 这些表是Systems
和Users
。 一个用户可以是多个系统的成员,一个系统中可以有多个用户。
system_id
是系统表中的 PK,user_id
是用户表中的 PK。 我将它们与 system_id
链接为用户表中的 FK。 我似乎无法找出两者之间的桌子。 我有形成一个 system_user
表并在其中使用两个外键的想法,但不知道它是如何工作的。
My database model has 2 particular tables in it that require a many-to-many relationship. The tables are Systems
and Users
. One user can be a member of more than one system and one system has more than one user in it.
system_id
is the PK in the system table and user_id
is the PK in the user table. I have them linked with system_id
as a FK in the user table. I can't seem to figure out a table in between the two. I had the notion of forming a system_user
table and using both foreign keys in there but cant see how that would work.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
你正朝着正确的方向前进。
创建一个表“system_user”:
从严格的关系建模意义上来说,这不是一个正确的实体,因此它不需要主键。 但是,如果您的场景需要,您可以添加一个。
要获取系统中的所有用户,您可以像这样查询:
要获取用户可以访问的所有系统,您可以像这样查询:
希望这有帮助!
You're headed in the right direction.
Create a table "system_user":
This is not properly an entity in a strict relational modelling sense, so it doesn't require a primary key. However, you can add one if your scenarios require it.
To get all of the users in a system you'd query like so:
To get all of the systems a user can reach, you'd query like so:
Hope this helps!
你做对了。 对多对多关系使用映射表。 您的系统或用户表中不需要任何外键列。 您的映射表将包含诸如 MappingID PK、SystemID 和 UserID 之类的内容。 您还可以将整行设置为 PK,但这是不必要的(如果您不想使用 MappingID)。 然后,只需将对插入到该表中,将系统与用户相关联。 您可以选择通过SystemID或UserID来获取某个实体拥有的所有关系。 您还可以包含映射可能具有的任何元数据,例如建立关联的日期。
You got it right. Use a mapping table for many to many relationships. You will not need any foreign key columns in your Systems or Users tables. Your mapping table will have something like a MappingID PK, SystemID, and UserID. You could also make an entire row the PK, but its unnecessary (if you didn't want to use the MappingID). Then, simply insert pairs into this table that associate a System with a User. You can select by SystemID or UserID to get all the relationships a certain entity has. You may also include any metadata the mapping might have, like the date the association was established.
你走在正确的轨道上。 您的新 system_user 表至少有 3 列:
You're on the right track. Your new system_user table would at a minimum have 3 columns: