I've come along way with your advice. Your idea was sound and it gets 90% of the job done. This next question might be more appropriate over at the Drupal site but I figure you guys are experts.

I'm creating database tables to store things and have a few questions:

1) No foreign keys anywhere in the code. This appears to be a decision based on coding to the safest possibility. Only InnoDB supports FOREIGN KEY. Am I right?

2) I want to have a reference to the uc_order.order_id field (the reason for the foreign key questions). If I make the field the same as currently declared the table is enforcing the data type, but without foreign key constraints my table won't enforce the constraint if you change the data type of the primary key for the uc_order table. Am I worrying needlessly?

3) Since I'm not doing foreign keys (assuming I'm right in #1 above), it becomes my responsibility to implement the case 'delete' in the hook_order() method that enforces the foreign key relationship. If the order gets deleted, I should cascade any changes necessary, up to and including deleting the row.

4) In this particular instance, the data being stored by the module is a key that has already been purchased through the 'order' event. One significance of this revolves around the business fact that the store operator using this module has already purchased that key and will need to revoke, and request a refund for it. The online interface from the vendor allows an administrator to revoke a key; unfortunately, the API does not. So... I was thinking that I'd NOT delete the row, rather, take advantage of the fact that I don't have foreign key constraint:

• set value of my_table.order_id to NULL,
• provide report that shows "orphaned" orders,
• create a GUI that integrates the process of revoking the key from vendor's site and deleting orphaned order from my tables (not sure how I'll do that yet...)

Seems a little like a hack, but until the API supports revocation, we need to have a way to manage this process when an order is deleted for a given reason. The only other idea I've come up with is to copy the row to an _orphans table and report/GUI off that. Cleaner, but more work...

Your advice is, again, appreciated.