Question :
I have the following requirements:
-
Every transaction has one of the following types; debit, credit, deposit, or withdraw.
-
Debit or credit transactions must have a linked invoice record and no bank account record.
-
Deposit or withdraw transactions must have a linked bank account record and no invoice record.
Currently my basic design is like this:
My current solutions are:
- Have
atwo nullable foreign keys to the invoice and bank account tables in the transaction table. However, I believe a nullable foreign key column isn’t a good design. - Have a foreign key in the invoice and bank account tables that link to the transaction table. However it’s seems harder to query and it models a one-to-many relation (not one-to-optional-one relation I want).
Which approach that I outlined is a better solution? Or is there another way to meet my constraints that is even better?
Answer :
Although very many NULLable columns are normally a sign of bad design, in some instances, it’s perfectly legit to have them, and use the database CONSTRAINTS to make sure that the 0-1 relations and your business rules are kept. This would be your solution #1. Your solution #2 isn’t easily helped by the database, you could eventually have one transaction without the corresponding rows in both invoices and bank_accounts tables.
Let’s assume, for a moment, that you’re not using MySQL1 (at least, as of version 5.7).
Let’s assume you are using another database that actually performs the checks2, it would make sense to use a schema like the following one, with one invoice_id and one bank_account_id columns, and the necessary constraints that guarantee that they’re REFERENCEing the proper rows in the proper tables (what you call links), and the CHECKS make sure that the proper ones appear, and the ones that do not correspond are not there:
CREATE TYPE transaction_type AS ENUM
('debit', 'credit', 'deposit', 'withdraw') ;
-- Note: this could be a table with four values (and probably four ids)
CREATE TABLE invoices
(
invoice_id integer /* serial */ PRIMARY KEY,
other_data text
) ;
CREATE TABLE bank_accounts
(
bank_account_id integer /* serial */ PRIMARY KEY,
name text,
other_data text
) ;
CREATE TABLE transactions
(
transaction_id integer /* serial */ PRIMARY KEY,
type transaction_type,
nominal decimal(10, 2),
invoice_id integer REFERENCES invoices(invoice_id) ON UPDATE CASCADE ON DELETE RESTRICT,
bank_account_id integer REFERENCES bank_accounts(bank_account_id) ON UPDATE CASCADE ON DELETE RESTRICT,
-- Constraints for your business rules
CONSTRAINT chk_debit_and_credit_must_have_bank_account
CHECK (case when type in ('debit','credit') then
bank_account_id IS NOT NULL
else true end),
CONSTRAINT chk_debit_and_credit_must_not_have_invoice
CHECK(case when type in ('debit','credit') then
invoice_id IS NULL
else true end),
CONSTRAINT chk_deposit_and_withdraw_must_have_invoice
CHECK(case when type in ('deposit','withdraw') then
invoice_id IS NOT NULL
else true end),
CONSTRAINT chk_deposit_and_withdraw_must_not_have_bank_account
CHECK(case when type in ('deposit','withdraw') then
bank_account_id IS NULL
else true end)
) ;
With this in mind, and the following data…
-- Adding two invoices
INSERT INTO invoices
(invoice_id, other_data)
VALUES
(1, 'data for invoice 1'),
(2, 'data for invoice 2')
;
-- Adding two bank accounts
INSERT INTO bank_accounts
(bank_account_id, other_data)
VALUES
(1000, 'Bank account 1000'),
(1001, 'Bank account 1001')
;
… you can have one legit INSERT
-- Good credit and debit
INSERT INTO transactions
(transaction_id, type, nominal, invoice_id, bank_account_id)
VALUES
(2000, 'credit', 1000.00, NULL, 1000),
(2001, 'debit', 900.00, NULL, 1000) ;
… and some illegal ones (being rejected by the database)
-- Bad credit, it's got invoice
INSERT INTO transactions
(transaction_id, type, nominal, invoice_id, bank_account_id)
VALUES
(2002, 'credit', 1000.00, 1, 1000) ;
ERROR: new row for relation "transactions" violates check constraint "chk_debit_and_credit_must_not_have_invoice" DETAIL: Failing row contains (2002, credit, 1000.00, 1, 1000).
-- Bad credit, it's got not bank_account_id
INSERT INTO transactions
(transaction_id, type, nominal, invoice_id, bank_account_id)
VALUES
(2003, 'credit', 1000.00, NULL, NULL) ;
ERROR: new row for relation "transactions" violates check constraint "chk_debit_and_credit_must_have_bank_account" DETAIL: Failing row contains (2003, credit, 1000.00, null, null).
(and all other combinations)
A careful choice of names for the constraints can help a lot on debugging erroneous inserts or updates. If you need maximum speed, all the constraints can be reduced to just one single check expression. I normally would try to have the database help me, and keep it simple (4 easy names and 4 easy-to-read expressions, instead of a single one).
You can find all the setup at dbfiddle here
1This is the reason why:
Unfortunately, MySQL doesn’t help much, because from MySQL’s 5.7 manual on CREATE TABLE
CHECK
The CHECK clause is parsed but ignored by all storage engines. See Section 1.8.2.3, “Foreign Key Differences”.
2I’ve used PostgreSQL. With some syntax variations, this would also work with SQL Server or Oracle
Let me suggest a third alternative: You can use triggers to check your business rules and fail the insert/update in a similar fashion a foreign key constraint would.
For your scenario I setup something like this:
CREATE TRIGGER Transaction_BEFORE_INSERT BEFORE INSERT ON Transaction_Table FOR EACH ROW
BEGIN
-- deposit, withdraw must have a bank account and no invoice
if NEW.`Type` in ('deposit','withdraw') then
if not(select count(*) from Account_Table where id = NEW.id)
or (select count(*) from Invoice_Table where id = NEW.id)
then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Error message';
end if;
-- credit, debit must have an invoice and no bank account
elseif NEW.`Type` in ('credit','debit') then
if not(select count(*) from Invoice_Table where id = NEW.id)
or (select count(*) from Account_Table where id = NEW.id)
then
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Another error message';
end if;
end if;
END
I’ve used SQLSTATE 45000 which is a generic state for user-defined exceptions. You may customize that and also set an appropiate error message.
You would also need to create a BEFORE UPDATE trigger that does a similar check.
There will be a performance penalty for abusing triggers this way. Each insert/update will run 2 selects on the other table. You should optimize the selects to be as fast as possible. On high transaction databases it could be a bad solution. So if you choose taking this route please test the impact before running it on production.
You don’t have a transaction table, you have a [BankAccountTransaction] and a [CreditTransaction] table.
Yes, split you transaction in two tables, one has a FK to [BankAccount] and the another got a FK to [Invoice].
If the need arises you can create a “parent” [Transaction] table and use it to intermediate the relation between the above two tables to a table don’t see the difference between them.
Or if that important only to report like queries you can create a view where you easily use a UNION.
+---------------------+ +---------------+
| | | |
+---------------+ CreditTransaction +----------------> Invoice |
+-------v------+ | | | |
| | +---------------------+ +---------------+
| |
| Transaction |
| |
| | +------------------------+ +----------------+
+-------^------+ | | | |
| | BankAccountTransaction | | BankAccount |
+-----------------+ +-------> |
| | | |
+------------------------+ +----------------+