mysqlsqldatabase-designe-commercerecurring-billing

Should original order be referenced on every subsequent invoice record in database


Consider the following database design (irrelevant columns and tables removed):

Database Design Illustration

Each time a recurring payment is due for a subscription, I intend to add an invoice record and subsequent payment record.

However, given orders can contain both one-off purchases, and subscription purchases, is it normal practice to reference the original order_id when generating new monthly invoices?

My hesitation, if not clear, is that the original order record may include items that are not subscriptions, and as such, are not relevant to the recurring invoices.


Solution

  • I'm not sure this is a matter of opinion.

    The original order contains the order for the subscription. That it contains other information is rather irrelevant. You care about the order where the subscription first appears.

    Keeping the original order id seems reasonable. In fact, when an invoice is created it is probably a best practice to have information about the original order for the items on the invoice. Otherwise, it becomes much more cumbersome to check the lineage of the items -- something that might be quite important for auditing purposes and other reasons.