MySql Invoice and Subscription Schema

Question

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?

Answer 1

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:

1. Orders require Invoices. Perhaps you need to bill them so as to be paid.
2. 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.

1. 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.]

2. 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.

Answer 2

There is not enough information, but sadly I don't have enough reputation to post a comment so I'll do my best even when I'm making some assumptions.

1. The term subscription suggests a periodic event. Maybe the monthly t-shirt. As it is periodic, it does not generate any order when it is created. When the periodic event is triggered (the monthly t-shirt is released) the subscribers get automatically an order. This is a business rule that can be implemented in the application layer.
   • 1.a. If you cannot control it in the application layer, then you would need an additional entity to distinguish the subscription itself from the triggered periodic event. For this purpose it could be useful to keep track of the triggered subscriptions by means of a different table. So the Subscription entity solves the many-to-many relationship between Customers and Products (what periodic products a certain customer has subscribed, independently from if those subscriptions have already happened or not), and the Subscription_triggered_event would track every periodic occurrence of such subscription. I subscribe for the monthly t-shirt and I wait. When the next monthly t-shirt is released, it generates a new line in the mentioned Subscription_triggered_event table and this table can trigger the creation of the corresponding order.
2. Do we need to assume that each subscription generates a single order o may they be combined in different order lines of the same order? I mean, if I'm subscribed to the t-shirt of the month and the mug of the month, when they are released, do I get two orders or a single one with two products?
   • 2.a. If there is generated a single order, then you could create a relationship between Order and Subscription entities with a foreign key in Order. But in my opinion this would be valid only if every order is generates by a subscription. Otherwise, if user can place orders independently from subscriptions this solution would make the model dirty and I personally would create a business rule controlled by the application layer or a trigger instead an explicit foreign key in the subscription table.
   • 2.b. If the case is that several subscription occurrences can be merged into a single order, then the trigger or application should simply create a single order and put as lines all the different products with an occurrence. For this solution the previous proposal of having a Subscription_triggered tracking table would be really useful. Otherwise we could achieve the same with a view with a lighter weigh in the code.
3. Finally, the relationship between order and invoice is one-to-one so it should be easy to manage. However the invoice shouldn't be generated until the order gets it's final state. You may need to control the workflow of the Order to know when is it "invoiceable" or not, (i.e. when it is shipped). Without that control flow you would need to generate automatically an invoice when you create an order, but this is not working with triggers as you need to create an order before creating the order lines to keep your referential integrity. Of course this can be solved by removing the referential integrity in the database and controlling it in the application layer.

Hope this helps.

Answer 3

The order-invoice-payment pattern is one commonly seen in accounting. Those tables belong together in the "finance" part of your business model. It is well-established practice to be able to tie incoming money back to a customer request. Your financial auditors will expect to be able to follow that trail and reconcile the rows there with other records.

Subscriptions belongs in the "sales" part of your model, along with any other channel you have for bringing in trade. Although a subscription may share a number of foreign keys with an invoice they are distinct and separate concepts with different lifecycles. For example, a subscription may be removed once it has expired but an invoice must be retained for, say, seven years for tax purposes.

I would suggest you keep your tables just as you show them in the question. As a subscription is written, automatically generate the corresponding order and invoice to match. This can be done in the application or through a DBMS trigger. There will be a one-to-one relationship between Subscriptions and Orders. The foreign key column should be added to Subscriptions so Orders can remain agnostic to the source of the order.