Database design - passenger, driver, wallet. How to handle multiple foreign keys with Id and type fields?

Question

I have to design a database schema for new project. I will use PostgreSQL for the implementation of the schema.

Right now I'm stuck in a scenario where I need to handle foreign keys based on type and id. Here are the tables:

Passenger:

PassengerID
FirstName
Email
DOB
Address

Driver:

DriverID
CNIC
Vehicle
License
Status

Wallet:

WalledID
Amount
UserId
UserType

Problem: I want that every wallet row should have UserId as DriverId or PassengerID and in the type field the name of the table.

How can I achieve this with PostgreSQL? I have heard that it is possible from polymorphic association but don't know how to implement it.

Answer 1

You cannot have that directly, but you can do the following:

• Have two foreign key columns driverid and passengerid in wallet that reference the respective tables.

• Add a check constraint that force one of the foreign key columns NULL and the other NOT NULL based on usertype.

  CHECK (usertype = 'driver'    AND driverid IS NOT NULL AND passengerid IS NULL
      OR usertype = 'passenger' AND driverid IS NULL AND passengerid IS NOT NULL)
  

• If you want, add a view on wallet that uses coalesce to map both foreign key columns to a single one.

Answer 2

An alternative is to add a "supertype" of Driver and Passenger. In lack of a better name, I'll call it car_resident:

CREATE TABLE car_residents
( resident_id ... not null primary key
, resident_type char(1) not null
,     check (resident_type in ('P','D'))
,     unique (resident_type, resident_id)
, ... );

CREATE TABLE Passenger
( PassengerID ... NOT NULL PRIMARY KEY
, resident_type char(1) not null
,     CHECK check (resident_type = 'P')
,     FOREIGN KEY (resident_type, PassengerID)
          REFERENCES car_resident (resident_type, resident_id)
, ...
);

-- similar construct for Driver

CREATE TABLE Wallet
( ... NOT NULL PRIMARY KEY
, resident_id ... NOT NULL
, resident_type ... NOT NULL
, ...
,     FOREIGN KEY (resident_type, resident_id)
          REFERENCES car_resident (resident_type, resident_id)
);