Using NULLs in matchup table

Question

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` int(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

Answer 1

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;