Database design - passenger, driver, wallet. How to handle multiple foreign keys with Id and type fields? 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.
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.