Multiple instances of same foreign key in a single table

Question

I have a table that references the same foreign key multiple times (4 times) in the same table. Can someone explain why/how this makes sense?

Answer 1

Pedantically, A table doesn't reference a foreign key, it references a table via a foreign key.

It makes sense if it's necessary for the data model.

Here's an example. A Sales Order is associated with multiple parties playing different roles, such as the Selling Party, Customer Party, Paying Party, and Order Taking Party.

create table sales_order (
  order_id int primary key,
  seller_party_id int not null references parties(party_id),
  customer_party_id int not null references parties(party_id),
  order_taken_by_party_id int not null references parties(party_id),
  payer_party_id int not null references parties(party_id),
  ...
);

Answer 2

So, let's say that you have a company database with two tables, Employees and positions. Let's say that Employees just contains employee id and username. Positions could have position id, position name, employee fk, boss fk, and direct report fk.

So each record of positions has three fk's to the Employees table, each referencing a different employee id.

So in essence, you have have multiple foreign keys in a table to the same table, but they might mean something slightly different, but use the same reference data.