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
