SQLzoo.net

ACME Accounts

This tutorial uses an example database which is typical of a simple accounts system. ACME sell products to a number of account customers. These customers regularly place orders. They are not required to pay immediately and so may be in debt to ACME temporarily. (They may even be in credit but this is less common).

A few points should be noted for such financial systems:

• Money should be stored exactly. Typically as DECIMAL(10,2) - never use a FLOAT to record money.
• An audit trail must be maintained. It is not enough to know who owes whom how much - we need to know why.
• Every "transaction" should be recorded and kept indefinitely. A transaction is when (A) Some one pays you money or (B) You do something that you will charge for - in this case ship goods. Don't confuse this with the database term transaction.
• The current balance is not stored - we can calculate it if required. We can "consolodate" accounts - we move all transactions for a customer to an archive file and replace them with a single "balance carried forward" item.
• Every transaction should be dated.
• Transactions should never be altered. If a record is entered incorrectly it should be "reversed" by an additional record. (This database does not support reversals)
• We make no attempt to match receipts and shipments
• Several shortcomings of this simple system are discussed in the tutorial.