Consider the following database design (irrelevant columns and tables removed):
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.
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.