MySql Invoice and Subscription Schema

database-designMySQLschema

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?

Best Answer

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.

Related Solutions

Mysql – Generating Invoices and Tracking

Cash matching

This is a cash matching problem. You can track this at one of two levels:

Compare invoiced to cash figures (somewhat sloppy but this is actually how it's done for inwards business by most Lloyd's Syndicates, often called a 'written vs. signed' report).
Maintain explicit cash allocations from cash payments broken down by invoice.

From your question I think you want to do the latter.

Typically this is done by having a separate set of cash transactions, and a bridging table that has the allocation of cash payments to invoices. If the values are equal or the cash payment comes with a single invoice reference you can do the allocation automatically. If there's a M:M relationship between invoices and payments you will need to do a manual matching process (doing this automatically is actually a variant of the knapsack problem).

A basic cash matching system

Imagine that you have an invoice table, a cash payments table and an allocation table. When you issue an invoice then you set up an invoice record in the invoices table and a 'receivable' or 'payable' record in the allocations table.

Invoice #1, $100
Allocation: a record with a reference to invoice #1, 'receivable' transaction type and $100 owing. No reference to a cash payment on this record.

Now, you get a cash payment of $100

Cash payments (chq #12345): $100
Allocation: a record with a reference to invoice #1 and chq #12345, 'cash' transaction type and -100 owing ($100 paid).

You can generalise this to a M:M relationship where you get multiple payments against a single invoice or a payment covering multiple invoices. This structure also makes it quite easy to build credit control reports. The report just needs to find invoices older than (say) 180 days that still have outstanding balances.

Here's an example of the schema plus a couple of scenarios and an aged debt query. Unfortunately I don't have a running mysql instance to hand, so this one is for SQL Server.

-- ==============================================================
-- === CashMatch.sql ============================================
-- ==============================================================
--


-- === Invoices =================================================
--
create table Invoice (
       InvoiceID        int identity (1,1) not null
      ,InvoiceRef       varchar (20)
      ,Amount           money
      ,InvoiceDate      datetime
)
go

alter table Invoice
  add constraint PK_Invoice 
      primary key nonclustered (InvoiceID)
go


-- === Cash Payments ============================================
--
create table CashPayment (
       CashPaymentID    int identity (1,1) not null
      ,CashPaymentRef   varchar (20)
      ,Amount           money
      ,PaidDate         datetime
)
go

alter table CashPayment
  add constraint PK_CashPayment
      primary key nonclustered (CashPaymentID)
go




-- === Allocations ==============================================
--
create table Allocation (
       AllocationID       int identity (1,1) not null
      ,CashPaymentID      int  -- Note that some records are not
      ,InvoiceID          int  -- on one side.
      ,AllocatedAmount    money
      ,AllocationType     varchar (20)
      ,TransactionDate    datetime
)
go

alter table Allocation
  add constraint PK_Allocation
      primary key nonclustered (AllocationID)
go


-- ==============================================================
-- === Scenarios ================================================
-- ==============================================================
--
declare @Invoice1ID int
       ,@Invoice2ID int
       ,@PaymentID int


-- === Raise a new invoice ======================================
--
insert Invoice (InvoiceRef, Amount, InvoiceDate)
values ('001', 100, '2012-01-01')

set @Invoice1ID = @@identity

insert Allocation (
       InvoiceID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice1ID, 100, '2012-01-01', 'receivable')


-- === Receive a payment ========================================
--
insert CashPayment (CashPaymentRef, Amount, PaidDate)
values ('12345', 100, getdate())

set @PaymentID = @@identity

insert Allocation (
       InvoiceID
      ,CashPaymentID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice1ID, @PaymentID, -100, getdate(), 'paid')



-- === Raise two invoices =======================================
--
insert Invoice (InvoiceRef, Amount, InvoiceDate)
values ('002', 75, '2012-01-01')

set @Invoice1ID = @@identity

insert Allocation (
       InvoiceID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice1ID, 75, '2012-01-01', 'receivable')


insert Invoice (InvoiceRef, Amount, InvoiceDate)
values ('003', 75, '2012-01-01')

set @Invoice2ID = @@identity

insert Allocation (
       InvoiceID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice2ID, 75, '2012-01-01', 'receivable')


-- === Receive a payment ========================================
-- The payment covers one invoice in full and part of the other.
--
insert CashPayment (CashPaymentRef, Amount, PaidDate)
values ('23456', 120, getdate()) 

set @PaymentID = @@identity

insert Allocation (
       InvoiceID
      ,CashPaymentID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice1ID, @PaymentID, -75, getdate(), 'paid')

insert Allocation (
       InvoiceID
      ,CashPaymentID
      ,AllocatedAmount
      ,TransactionDate
      ,AllocationType
) values (@Invoice2ID, @PaymentID, -45, getdate(), 'paid')



-- === Aged debt report ========================================
--
select i.InvoiceRef
      ,sum (a.AllocatedAmount)                 as Owing
      ,datediff (dd, i.InvoiceDate, getdate()) as Age
  from Invoice i
  join Allocation a
    on a.InvoiceID = i.InvoiceID
 group by i.InvoiceRef
         ,datediff (dd, i.InvoiceDate, getdate())
having sum (a.AllocatedAmount) > 0

How to have a one-to-one relation to either one of two other tables

There are several options:

Combine Paypal and Stripe tables into a single Payment table, with fields to hold either type of payment;
Have both a paypal_id and a stripe_id in the Order table; use a condition on the table to ensure that at least one of them is NULL.
Have a single Payment_ID with a flag to indicate whether it's an id from Paypal or Stripe as you propose.

The first two options allow for the payment ID(s) to be foreign keys to the payment table(s), the third does not.

I would lean towards a single table with all payment data, but that may be a personal preference. Which of the first two options is better would depend on whether there are any common columns between Paypal and Stripe, and just how large the distinct columns are.

UPDATE: joanolo's answer lays out how to actually implement my option 2; take a look!

Best Answer

Related Solutions

Mysql – Generating Invoices and Tracking

How to have a one-to-one relation to either one of two other tables

Related Question