Table with two foreign keys pointing to the same column of another table

Question

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)

If a `case` has two `clients` then how else would you represent that? What problem have you encountered? — David, May 10 '18 at 19:57
There is nothing wrong with 2 foreign keys, assuming each record linked to plays a different role (they could even both be the same record, if that entity fulfilled both roles). — Scott Hunter, May 10 '18 at 19:57
@david most of my db knowledge is self-taught, so i wasn't sure whether the design would be better than have separate table for each party. — Ganton, May 10 '18 at 20:09
@scott-hunter it would be like client table have a 'john' (id=1) and 'jane' (id=2), and the case table would have a case entry with party1 as 'john' and party2 as 'jane'. — Ganton, May 10 '18 at 20:11
Assistance should be provided to improve question not close it. — Martin Spamer, May 10 '18 at 21:04

score 1 · Answer 1 · answered May 10 '18 at 21:49

If that restraint that there are only two parties to a counseling case really holds for all cases, there is no good reason to do it differently. If you could end up with a one to more-than-two relationship you should make that relationship a separate table, e.g. rel_cases_clients: (ID, FK_Case, FK_Client). It is also a bit nicer regarding data normalization.

Generally, having many foreign keys in your table is no problem at all and highly normalized designs are usually full of them. But keep in mind that such relations usually mean more work in your queries. (JOIN)

AntC · Accepted Answer · 2018-05-16T21:58:17.333

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

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?

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

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

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.

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

Thanks. yes, their role would be distinct (think of it like a mother and father). and yes, the case is usually initiated when 1 client makes contact with us or is referred to us, so a case would usually be create with just 1 party's info at first. — Ganton, May 14 '18 at 19:05
if i were to write a select statement that shows the `case_id, 1st party names, 2nd party names` based on the table structure you have, would have I have to merge (union) both select statements, each with a where condition on the role of that party? — Ganton, May 16 '18 at 20:07

Table with two foreign keys pointing to the same column of another table

2 Answers2

Linked