具有两个外键的表指向另一个表的同一列 [英] Table with two foreign keys pointing to the same column of another table

问题描述

我想知道从设计的角度来看，有一个带有2个外键的表是可行还是理想的，每个外键指向另一个表的同一字段。表 Case 有两个字段，它们是FK引用表 Client 的PK（每个案例都有两个客户端）

I wonder whether it is possible or ideal from a design standpoint to have a table with 2 foreign keys, each one pointing to the same field of another table. The table Case has two fields that are FK referencing the PK of the table Client (Every case has two clients)

client table:  
------------------
PK:client_id
   first_name
   last_name

case table:  
------------------
PK:case_id
   party1 (client_id)
   party2 (client_id)

推荐答案

是的，完全可以让两个FK分别指向另一个表的相同字段。我想说这很不正常，可能是设计未正确归一化的症状。考虑

Yes it's perfectly possible to have 2 FKs each pointing to the same field of another table. I'd say it's unusual, and might be a symptom of a design that's not properly normalised. Consider

每个案例都有两个客户

Every case has two clients

我怀疑这过于简化。这两个客户扮演不同的角色吗？即使完全有两个完整案件的客户，也许您只能一一了解他们？ （因此，您首先要记录情况，然后再添加 party1 ，然后再次添加

I suspect is an over-simplification. Are these two clients in distinct roles wrt the case? Even if there are exactly 2 clients for a fully-fledged case, maybe you only find out about them one by one? (So you first want to record the case, then later add party1, later again add party2.) Is it possible the two parties are the same client?

如@AndreasT所暗示的，更常见的设计是：

A more common design, as @AndreasT hints at, would be:

client table:  -- as you have

case table:
----------------
PK: case_id
    other stuff about the case, start date, etc

party-case-role
----------------
PK: { case_id(FK case table)
    { party(FK client table client_id)
    { role

其中角色可以为 party1，party2，见证人，咨询专家，监护人，看护人，... （取决于您的案子）

where the role could be party1, party2, witness, consulted_expert, guardian, carer, ... (depending on what your cases are about)

数据结构被称为 ppr（人-党角色），在行业中很常见，在您要处理的客户/客户/供应商/代理商之间有许多交叉链接-在保险或法律案件中，

This style of data structure is sometines called 'ppr' - person-party-role, and is common in industries with many cross-links amongst the clients/customers/suppliers/agents you're dealing with - in insurance or legal cases, for example.

查询返回 party1 ， party2 与案例详细信息结合在一起（根据您的评论请求）（未试用）

For a query to return party1, party2 joined up with the case details (per your comment request) (untested)

SELECT case.*, p1.first_name, p1.last_name, p2.first_name, p2.last_name
FROM case
INNER JOIN (SELECT * FROM party-case-role WHERE role = 'party1' AS ppr1)
        ON case.case_id = ppr1.case_id
INNER JOIN client AS p1 ON ppr1.party = p1.client_id
INNER JOIN (SELECT * from party-case-role WHERE role = 'party2' AS ppr2)
        ON case.case_id = ppr2.case_id
INNER JOIN client AS p2 ON ppr2.party = p2.client_id

这篇关于具有两个外键的表指向另一个表的同一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！