Best database design for payments with multiple constraints

I have to register payments.

• There are two things to pay. thingC or thingD (they may not be paid).

• You can only pay one thingD per payment transaction.

• You can pay many thingC per payment transaction.

• You can pay for thingC or thingD per payment, not both.

• There are three codes that have to be generated for each thingC or thingD that was paid.

Existing tables

thingC

idthingC serial PRIMARY KEY
--more columns

thingD

idthingD serial PRIMARY KEY
--more columns

Tables I think I need:

payment

idpayment serial PRIMARY KEY
idthingD integer REFERENCES thingD -- the payment may be for a thingD
type character(1) NOT NULL --C or D
c1 integer
c2 integer
c3 integer

payment_C

idpaymentC serial NOT NULL
idpayment integer NOT NULL REFERENCES payment
idthingC integer NOT NULL REFERENCES thingC
c1 integer NOT NULL
c2 integer NOT NULL
c3 integer NOT NULL

In table payment I'll have values in idthingD, c1, c2 and c3 if type is 'D'. The columns mentioned above will be null if type is 'C', and there will be values in table payment_C.

I have to be able to say:

• idpayment 1 was for thingD 2 and have the codes 1, 4, 8.

• idpayment 2 was for thingC 5 with codes 2, 3, 6 AND thingC 8 with codes 4, 5, 8

What do you think? In which table should the codes be? Can the design be improved.

You obviously put some thought into this design. But you would regret that you have to search for codes in two tables. And what if you want to add details for codes?

I suggest to store all codes in the same table like this:

CREATE TABLE payment (
   idpayment serial PRIMARY KEY
   -- more payment details?
);

CREATE TABLE payment_code (
   idpayment_code serial PRIMARY KEY
 , idpayment      int NOT NULL REFERENCES payment
 , idthingC       int REFERENCES thingC  -- can be NULL
 , idthingD       int REFERENCES thingD  -- can be NULL
 , c1             int NOT NULL
 , c2             int NOT NULL
 , c3             int NOT NULL
 , CONSTRAINT either_c_or_d              -- reference either to c or to d
      CHECK (idthingC IS NULL AND idthingD IS NOT NULL
          OR idthingD IS NULL AND idthingC IS NOT NULL)
 , CONSTRAINT c_distinct_per_payment     -- distinct idthingC per payment (?)
      UNIQUE (idpayment, idthingC)
 );

CREATE UNIQUE INDEX idx_one_d_per_idpayment ON payment_code (idpayment)
WHERE idthingD IS NOT NULL;              -- only one idthingD per payment

(Depending on the nature of the codes, maybe even just 1 code per row.)

The partial unique index idx_one_d_per_idpayment disallows more than one reference to thingD per payment.

This allows at most 1 thingD per payment and any number of distinct thingC.

Mutually exclusive payment types

You later clarified, those are mutually exclusive.

This allows at most 1 thingD per payment or any number of distinct thingC (but not both):

CREATE TABLE payment (
   idpayment serial PRIMARY KEY
 , type_cd   "char" NOT NULL DEFAULT 'C'  -- optional default
   -- more payment details?
, CONSTRAINT type_cd_valid CHECK (type_cd = 'C' OR type_cd = 'D')
, CONSTRAINT redundant_uni_for_fk_constraint UNIQUE (idpayment, type_cd)
);

CREATE TABLE payment_code (
   idpayment_code serial PRIMARY KEY
 , idpayment      int NOT NULL      -- multicolumn ...
 , type_cd        "char" NOT NULL   -- ... FK reference
 , idthingC       int REFERENCES thingC
 , idthingD       int REFERENCES thingD
 , c1             int NOT NULL
 , c2             int NOT NULL
 , c3             int NOT NULL
 , CONSTRAINT either_c_or_d
      CHECK (type_cd = 'C' AND idthingC IS NOT NULL
          OR type_cd = 'D' AND idthingD IS NOT NULL)
 , CONSTRAINT c_distinct_per_payment
      UNIQUE (idpayment, idthingC)
 , CONSTRAINT payment_c_or_d
      FOREIGN KEY (idpayment, type_cd) REFERENCES payment(idpayment, type_cd)
 );

CREATE UNIQUE INDEX idx_one_d_per_idpayment ON payment_code (idpayment)
WHERE type_cd = 'D';  -- slightly simpler now

To enforce your rules, add a type column in table payment (I named it type_cd since "type" is too generic, and I chose data type "char" - with double quotes - efficient for tiny enumerations). Include that column in the FK reference (redundantly). This allows a CHECK constraint (either_c_or_d) in table payment_code to enforce valid references.

The UNIQUE constraint redundant_uni_for_fk_constraint seems redundant, but is required for the multicolumn FK reference on (idpayment, type_cd). Related, with more explanation:

• Enforcing constraints "two tables away"

Both FK columns (idpayment, type_cd) must be NOT NULL to enforce referential integrity. Or (if you need rows without assigned payment) declare the FK constraint as MATCH FULL. Details:

• Two-column foreign key constraint only when third column is NOT NULL

• MATCH FULL vs MATCH SIMPLE in foreign key constraints

StackExchange Database Administrators Q#159627, answer score: 3