1

I'm trying iron out the data model for the accounting part of an application that does bookkeeping (among other things) for online retail sales. I've read a few books books, but they either assume you're a mom&pop bakery that only takes cash and checks or they are tomes as long as your arm that assume you want to be a CPA. None are written for developers.

I've also looked at the model for some open source accounting packages to get ideas. I feel like I have a pretty good idea of the broadstrokes at this point, but I have a few issues that I'm unsure about how to handle, mostly relating to the linking of transaction records to payment records and recording settled credit cards. I'm hoping someone here has some experience developing and designing databases for accounting applications and can share some insight.

The Processes

(letters reference considerations below)

When an Order is placed online and imported into the system the following happens (inside a database transaction):

  • Create a Sales Order record in the Sales Order table.

If a credit card payment was made:

  • Create a Payment record in the Payments table.
  • POST a Transaction record with a credit to a Revenue Account "sales" and a debit to an "unsettled funds" Asset Account. Link to the payment record. (a) (b)
  • Create a PaymentApplication record that records that the new Payment record representing the credit card payment applies to the newly created Sales Order. (c)
  • POST a Transaction record with a credit to Revenue Account "sales" (?) and a debit to "Sales Tax Payable" Liability Account. (d)

When the merchant account "settles" at the end of each business day (they put money in the real bank account as a lump sum):

  • POST a Transaction record with a credit to "unsetled funds" Asset Account and a debit to a "Cash In Bank" Asset Account. (e)

Considerations

  • (a) Does recording sales tax liability (if applicable) come in here or at (d)? If the customer indicates they want to pay by check, and the sale is not on credit, we don't have any sales tax liability for that order yet because it's not a "real" order until we get the check. (They could just not send the check, and since we haven't shipped, an unpaid check order is more like a quote).

  • (b) Does it make more sense to store a TransactionID column in the document and payment records, or to have two fields, PaymentID and DocumentID in the Transaction header where only one is ever used per record?

  • (c) Does a PaymentApplication record link to any transactions posted to the ledger?

  • (d) Does this Transaction record link to the Sales Order record that caused the tax liability? It can't be part of the payment since a payment is not necessarily a part of any specific order.

  • (e) Many software packages have a reconciliation process where the user checks off all of the orders (technically payments) that were included in the batch credit card settlement. How is this structured in the database?

    Does anything post to the ledger besides a single lump sum moving from unsettled to the bank? If not, how is the link between orders and the batch settlement recorded in the tables? I'm not sure if the settlement should record against payments or orders, but I'm thinking payments would make more sense since a user could technically pay part of the order with a credit card and part with store credit.

    But the Payment record is already linked to the transaction the moves money from Revenue to the "unsettled" Asset account. Does the Payment Record have a second TransactionID to store the settlement batch TransactionID as well?

    Or, if the PaymentID field is in the Transactions table, not a TransactionID in the Payments table, you can't store the PaymentID in the Transaction record for the Revenue to Sale move, then store that same PaymentID again in a second transaction record for the "unsettled" to bank move, since in that model, there would only be one PaymentID field in the Transaction record and a single Transaction record for all the days payments at once.

    Or does each Transaction Line have a DocumentID and PaymentID filed, so a settlement has one header in the transactions table, one credit from "unsettled" and multiple linked debits to "cash", one per payment, linked to the Payment record?

    Or is there simply a Settlements meta data table that attaches Payment records to Transactions record, which does not impact the ledger for financial purposes, just records the link for informational purposes?

Proposed Table Structure

(question mark denotes uncertainty, ellipses denotes omission of irrelevant fields for brevity, letters reference considerations above)

Journal Entries

JournalEntryID
AccountingPeriodID
TimeStamp
DocumentID? (b)
PaymentID? (b)

Journal Entry Lines

LineID
JournalEntryID
AccountID
Amount

Documents (fields common to all sales orders, purchase orders, packing slips, etc)

DocumentID (PK)
TransactionID? (b)
...

Document_SalesOrders (extension fields for the document table when the type is sales order)

DocumentID (FK)
CartOrderID
...

Payments

PaymentID
Amount
PaymentTypeID (FK, check, credit card, etc)
TransactionID?
...

PaymentApplication

PaymentID
DocumentID
AppliedAmount
TransactionID? (b)

Question

How should I structure the tables to record the relationships between payments, documents, transactions and settlements as described above?

  • I would urge you not to invest months and months writing an accounting software of your own, but rather buy one off the shelf (on prem or cloud-based) that sports a good API, then integrate your online sales suite with that software. There's much more to accounting than just the sales ledger, not least of which all the reporting. Consider all the accounting/legal/administrative implications of where you're going with this. – Daniel Hutmacher Apr 1 '17 at 21:05
  • @Nick What did you ultimately decide on? – alexw Sep 9 '17 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.