Ideal way to store data that can be one of a few possibilities in PostgreSQL?

Question

I'm not too sure if there is a special term for this kind of data, but the concept is similar to a C++ std::variant (but maybe in PostgreSQL it wouldn't be ideal to use a variant-like structure).

As an example, suppose I have a table purchases that has some columns:

CREATE TABLE purchases (
    purchaseid bigserial,
    buyerid bigint,
    name text,
    ...
);

Each purchase would be paid via one of a small number of possibilities, e.g. cheque or bitcoin. Each possibility has different associated fields that would need to be saved.

Payment by cheque would require saving the cheque number and the bank name, and payment by bitcoin would require saving the sender's bitcoin address and a return bitcoin address.

It would seem wise (with my little knowledge of PostgreSQL) to have cheque payments and bitcoin payments reside in separate tables due to the differing fields.

What is the ideal way to store such data (and relate them to each row in purchases) if I know that each purchase can only be paid by either cheque or bitcoin, but not both?

Answer 1

The approach I take for such situations is indeed as you suggest: you need a different table for cheque details, bitcoin details etc. These tables must include the purchase id and the purchase table must have a paymenttypeid. Then when wishing to view the purchases together with details, you need to do a left join. The trick here is to have a case statement in the view such that depending on paymenttypeid you select from the current left joined table. In doing this, you need to make sure that you select the same number of columns from the details tables, making appropriate adjustments for types.

So you get something like:

SELECT p.purchasedate, 
       p.otherfields,
       pt.description,
       case p.paymenttype 
           WHEN 1
               THEN bd.DetailField1 -- cast if necessary
           WHEN 2
               THEN cd.DetailField1 -- cast if necessary
           END AS Detail1,
       case p.paymenttype 
           WHEN 1
               THEN bd.DetailField2 -- cast if necessary
           WHEN 2
               THEN cd.DetailField2 -- cast if necessary
           END AS Detail2
       FROM purchases p
       inner join paymenttypes pt 
           on p.paymenttypeid = pt.id
       left join bitcoindetails bd 
           on bd.purchaseid = p.id
       left join chequedetails cd 
           on cd.purchaseid = p.id

Although this is based on left joins, we know in practice it will always succeed, because the case statement is based on the paymenttypeid. It means that we are always selecting from the correct details table. Details must be present in the details table for the record selected, because it matches the paymenttypeid.