Using NULLs in matchup table

I am working on the accounting portion of a reservation system (think limo company).

In the system there are multiple objects that can either be paid or submit a payment. I am tracking all of these "transactions" in three tables called: tx, tx_cc, and tx_ch.

tx generates a new tx_id (for transaction ID) and keeps the information about amount, validity, etc. Tx_cc and tx_ch keep the information about the credit card or check used, respectively, which link to other tables (credit_card and bank_account among others).

This seems fairly normalized to me, no?

Now here is my problem:

The payment transaction can take place for a myriad of reasons. Either a reservation is being paid for, a travel agent that booked a reservation is being paid, a driver is being paid, etc.

This results in multiple tables, one for each of the entities: agent_tx, driver_tx, reservation_tx, etc.

They look like this:

CREATE TABLE IF NOT EXISTS `driver_tx` (
  `tx_id` int(10) unsigned zerofill NOT NULL,
  `driver_id` in开发者_Go百科t(11) NOT NULL,
  `reservation_id` int(11) default NULL,
  `reservation_item_id` int(11) default NULL,
  PRIMARY KEY  (`tx_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now this transaction is for a driver, but could be applied to an individual item on the reservation or the entire reservation overall. Therefore I demand either reservation_id OR reservation_item_id to be null. In the future there may be other things which a driver is paid for, which I would also add to this table, defaulting to null.

What is the rule on this? Opinion?

Obviously I could break this out into MANY three column tables, but the amount of OUTER JOINing needed seems outrageous.

Your input is appreciated.

Peace, Tom

Maybe the best way to handle this is through generalization, but is difficult to tell only by the given database structure.
You could model a supertype for every entity that can be paid and another supertype for every entity that can submit a payment.

In this case your table driver_tx would be generalized to:

CREATE TABLE IF NOT EXISTS `tx` (
  `tx_id` int(10) unsigned zerofill NOT NULL,
  `paying_id` int(11) NOT NULL,
  `payable_id` int(11) NOT NULL,
  PRIMARY KEY  (`tx_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;