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)
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments