One-to-"Zero or One" Relation

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?

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

StackExchange Database Administrators Q#153901, answer score: 2