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?
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.