Best database design for payments with multiple constraints

Question

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.

• Erwin Brandstetter: Can the same payment be for 1 thingD and several thingC? Or are both mutually exclusive?
• Kaz Miller: Only one thingD or several thingC per payment
• Erwin Brandstetter: I took the liberty to add this to the question - where defining info should go.

Answer 1

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

• Kaz Miller: The CONSTRAINT c_unique_per_payment should be for thingD
• Erwin Brandstetter: @KazMiller: This constraint is meant to disallow the same thingC more than once in the same payment. I added a (?) because I am not sure you need this constraint. You did not explicitly ask for it, but typically this would be nonsense. (There can still be multiple distinct thingC per payment.) I renamed it to c_distinct_per_payment now to clarify. The partial unique index idx_one_d_per_idpayment prevents more than one thingD per payment.