Generating Invoices and Tracking

Every 2 weeks, the system will generate the invoices for the companies.

Company will receive an invoice on the 1st and 16th every month. (It will run via Cron Job every 2 week. It scan through the order table and then add into 'invoice' table. Is there alternative?)

There are list of customers orders in the orders table and it also indicate which company it belong to (orders.company_id)

The invoice table calculate the total cost of the orders from orders table.

I am trying to figure it out how to design reasonable invoices tracking. Sometime company will have to send me the fees or sometime I send them the fees (invoice.amount)

I need to track the invoices with the following:

• when the company have sent me the amount

• when did I sent the amount to the company

• how much amount has been received from the company

• how much amount did I sent to the company

• did I receive the full amount (if not, what do I need to update on the Db?)

• invoice status (Invoice Sent, Cancelled, Amount Received, Amount Sent)

Here is the database design I have came up with:

company table

mysql> select * from company;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Company A |
|  2 | Company B |
+----+-----------+

Customers can select a company from my website.

orders table

mysql> select * from orders;
+----+---------+------------+------------+---------------------+-----------+
| id | user_id | company_id | total_cost | order_date          | status_id |
+----+---------+------------+------------+---------------------+-----------+
|  1 |       5 |          2 |      25.00 | 2012-02-03 23:30:24 |         1 |
|  2 |       7 |          2 |      30.00 | 2012-02-13 18:06:12 |         1 |
+----+---------+------------+------------+---------------------+-----------+

two customers have ordered the products from Company B (orders.company_id = 2). I know the orders fields is not enough, just simplified for you.

orders_products table

```

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')

--

StackExchange Database Administrators Q#13071, answer score: 10