You're tracking payments. Payments can come in several forms, and you want to
make sure each payment has exactly one form.
How to do this?
Read on...
Let's say we're taking several kinds of payments: Check, Credit Card,
and Manila Envelope. We'll use some partial unique indexing and check
constraints to get them into one table. Let's start with the columns:
CREATE TABLE payment (
payment_id BIGSERIAL PRIMARY KEY,
payer_id INTEGER NOT NULL REFERENCES payer,
payment_date DATE NOT NULL,
amount NUMERIC NOT NULL CHECK(amount > 0),
currency_id INTEGER NOT NULL REFERENCES currency,
/* Check */
check_number TEXT,
check_date DATE,
/* Credit Card */
cc_id INTEGER REFERENCES locked_down_tight_cc_table, /* You wouldn't store an actual CC number here, would you?!? */
expiry_date DATE,
CHECK(expiry_date = DATE_TRUNC('month', expiry_date)),
/* Manila Envelopes are pretty anonymous */
is_manila BOOLEAN NOT NULL DEFAULT FALSE,
Next, let's constrain those columns a payment can be exactly one of the
allotted types. We'll make one big CHECK constraint in three sections.
CHECK (
Check:
(
/* It's a check: */
check_number IS NOT NULL AND
check_date IS NOT NULL AND
cc_id IS NULL AND
expiry_date IS NULL AND
NOT is_manila
) OR
Credit Card:
(
/* It's a credit card: */
check_number IS NULL AND
check_date IS NULL AND
cc_id IS NOT NULL AND
expiry_date IS NOT NULL AND
NOT is_manila
) OR
Manila Envelope:
(
/* It's a manila envelope: */
check_number IS NULL AND
check_date IS NULL AND
cc_id IS NULL AND
expiry_date IS NULL AND
is_manila
)
)
);
Now, let's make sure we don't have duplicate payments in each type by making
some partial unique indexes. Since we have check constraints on what can be
in the columns, we only have to put enough information in the WHERE to check
against the already-constrained columns.
Checks:
CREATE UNIQUE INDEX payment_unique_check_idx ON payment (
/* Every partial index gets these: */
payer_id,
payment_date,
amount,
currency_id,
/* It's a check, so add these */
check_number,
check_date
)
WHERE
check_number IS NOT NULL;
Credit cards:
CREATE UNIQUE INDEX payment_unique_credit_card_idx ON payment (
payer_id,
payment_date,
amount,
currency_id,
/* Credit card */
cc_id,
expiry_date DATE
)
WHERE
cc_id IS NOT NULL;
And the ever-popular manila envelopes:
CREATE UNIQUE INDEX payment_unique_credit_card_idx ON payment (
payer_id,
payment_date,
amount,
currency_id
/* Manila Envelope: no more columns needed */
)
WHERE
is_manila;
As the business grows, there are lots more things you can do with helper
VIEWs, partitioning, etc.
Want to find out more? Leave a comment!
Until the next time...
The only thing... I would have expected "multiple inheritance" to involve using:
a) The inheritance system, with
b) Multiple partitions
The constraints nicely enforce what they're supposed to, thus allowing the multiple things to co-exist in the same table. But I'd still expect multiple partitions...
(Chris heads off muttering "multiple partitions"...)
How can you make a unique constraint covering all tables?
As long as that's not solved, real inheritance will lie dormant.
With real support for that feature your stripped down example would be:
CREATE TABLE payment (
payment_id BIGSERIAL PRIMARY KEY,
payer_id INTEGER NOT NULL REFERENCES payer,
payment_date DATE NOT NULL,
amount NUMERIC NOT NULL
-- omitt other stuff for now
);
CREATE TABLE cards(
cc_id integer not null,
expiry_date date not null
) INHERITS (payment);
CREATE TABLE checks (
check_number text not null,
check_date date not null
) INHERITS (payment);
-- manilly et al. are left as an exercise...
insert into checks values(default, 0, now(), 42.42, 'F42X4', now());
insert into checks values(default, 0, now(), 14.80, 'F42X5', now());
insert into cards values(default, 0, now(), 1.42, 42424242, now());
insert into cards values(default, 0, now(), 8.24, 42424242, now());
select from payment;
payment_id | payer_id | payment_date | amount
------------+----------+--------------+--------
3 | 0 | 2009-09-03 | 1.42
4 | 0 | 2009-09-03 | 8.24
1 | 0 | 2009-09-03 | 42.42
2 | 0 | 2009-09-03 | 14.80
select from cards ;
payment_id | payer_id | payment_date | amount | cc_id | expiry_date
------------+----------+--------------+--------+----------+-------------
3 | 0 | 2009-09-03 | 1.42 | 42424242 | 2009-09-03
4 | 0 | 2009-09-03 | 8.24 | 42424242 | 2009-09-03
That actually works in PostgreSQL.
Note that the checks are exactly where they are required. The schema is also easily expanded by simply adding new tables -- no need to touch the other checks.
What does NOT work is -- very sadly -- that there is no way to make unique constraints across all three tables. So this will work but should fail:
INSERT INTO into cards SELECT payment_id, payer_id, payment_date, amount, 1234, now() FROM checks ;
SELECT * FROM cards;
payment_id | payer_id | payment_date | amount | cc_id | expiry_date
------------+----------+--------------+--------+----------+-------------
3 | 0 | 2009-09-03 | 1.42 | 42424242 | 2009-09-03
4 | 0 | 2009-09-03 | 8.24 | 42424242 | 2009-09-03
1 | 0 | 2009-09-03 | 42.42 | 1234 | 2009-09-03
2 | 0 | 2009-09-03 | 14.80 | 1234 | 2009-09-03
Therefore the complete thing is quite unusable in PostgreSQL. sigh