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

Ganton

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)
AntC

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.

edited at
0

Comments

0 comments
Login to comment

Related

Two foreign keys pointing to the same table / model

How to use Factory boy when two foreign keys are pointing to the same table

JPA Hibernate two foreign keys to the same table

Mysql: using two foreign keys to the same table

How to build a (My)SQL table structure for a table containing multiple foreign keys pointing to same table

Multiple columns from the same table in another table as foreign keys

is it possible for two rows of a column (primary key) of the table to have same character/value with same foreign key(another column)

sql for a table with two foreign keys to same table causing Cartesian product

Can I have 2 foreign keys in one table referencing the same column in another?

entity framework Invalid column name Id (two foreign keys from the same primary table)

how to create a select statement with 2 foreign keys in one table pointing to two different tables

EFCore Linq ThenInclude Two Foreign Keys To Same Table

MYSQL: select by two foreign keys from the same table

Eloquent relation setup, two foreign keys to same table laravel 5.2

Entity Framework Code First - two Foreign Keys from same table

mysql queries on two foreign keys referencing the same table

SQL query with two columns as foreign keys of the same table

join on two foreign keys from same table in SQL

EF 6 how to set two foreign keys to same table

Entity Framework Core Two Foreign Keys - Same Table

Select name of two foreign keys referring to same primary key table

EF Core 2.2 - Two foreign keys to same table

two foreign keys on same table to display parent fields

Two foreign keys on same table: how to implement on delete cascade?

How to create two foreign keys to the same table with SQLite in Xamari Form

How to add two foreign keys in the same class (table) in EF

List all foreign keys of a table, multiple foreign keys to same table

How to do a query with multiple foreign keys pointing to one table?

SQLAlchemy - Multiple Foreign key pointing to same table same attribute