MySql Invoice and Subscription Schema I have an ecommerce db built and I'm adding subscriptions. See the simplified structure below.

orders
id, customer_id

order_items
id, order_id, product_id

invoices
id, order_id

subscriptions
id, customer_id, product_id, run_date


The issue I'm having is the invoices table requires an order_id but a subscription just generates an invoice. I could put the original order_id in the subscription table to track the lineage but then it gets confusing as the invoices table would have duplicate order ids. I tried removing the order and order_items and keeping the invoices table and adding an invoice_items table to bypass the order ids altogether but I thought it makes more sense to keep them because people can also buy regular products like t-shirts and those are technically an order. Is there a recommended way to structure the tables so that invoices are generated correctly? Should orders be for single purchase products and invoices only for subscriptions?
The fact that you are finding it difficult to explain indicates that there is an issue with the design, the expectations, or the perceived requirements.

Are Subscriptions automatically paid for, such as through a credit card?  Is that why an Invoice seems unnecessary?  Or some other reason. 

Your current state is:

Customers can purchase your products in one of two ways:


Orders require Invoices. Perhaps you need to bill them so as to be paid.
Subscriptions have no Invoices. Perhaps subscriptions are automatically paid through a credit card, PayPal, etc. 


Since a Customer decides how to interact with you, that Customer may have several Subscriptions and also may place Orders. And I would think that the customer could cancel the Subscription whenever he wanted to.

Therefore, both you and your customer have reason to keep a complete trail of orders and fulfillments (whether Invoice or Subscription).  That means that you do potentially want an Invoice of some sort even for Subscriptions.  

Here are two ways to handle this, namely treat them as the same thing or as two different things.


Subscriptions create an Invoice for the fulfillment of a subscription instance.  The subscription Invoice could have a different scheme (e.g. SUB-Number-Date) for identifying the subscription Invoice than you would use for an Invoice for an Order (e.g INV:InvoiceNumber).  [I would guess that the subscription Invoice would be marked as already paid.]
Create a Subscriptions log with a header for the subscription and a log to track each fulfillment.  That allows you to track the details of each time Customers have their Subscriptions fulfilled. In the case that a subscription is cancelled early, it could be used to refund the customer the unused sum.


How your organization views your Customers (person or businesses) will help you determine what is the best approach for your business.

What would I do?  I would tend would separate the Order Invoice from the more complex Subscription Invoice.  When needed a summary of the totality of Orders, Subscriptions, and (returns, reshipments, etc) can be produced for your company and for your customer.