Quick Ref.

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

BADGUY PRODUCT
Customers with accounts at ACME Products stocked by ACME
ID NAME ADDRESS
C001 Wile E Coyote www.warnerbros.com
C002 Sylvester www.warnerbros.com
C003 Tom www.mgm.com
C004 Elmer Fudd www.warnerbros.com
C005 Dick Dastardly www.warnerbros.com
ID DESCRIPTION PRICE
P001 Anvil 75
P002 Portable holes 5
P003 Horseshoe magnet 80
P004 TNT 50
P005 Bomb 50
P006 Elastic band 2
P007 Rocket roller skates 80
P008 Space ship 60000000
P009 Road sign "Diversion Left" 30
P010 Road sign "Diversion Right" 30
RECEIPT SHIPPED
Money received from customers Shipments made to customers
BADGUY RDATE NOTES AMOUNT
C005 1998-02-10 DD 5
C005 1998-03-10 DD 5
C005 1998-04-10 DD 5
C005 1998-05-10 DD 5
C005 1998-06-10 DD 5
C005 1998-07-10 DD 5
C001 1998-07-15 Cheque 100
C001 1998-07-23 Cash 255
BADGUY SDATE PRODUCT QUANTITY
C005 1998-01-01 P008 1
C001 1998-07-10 P003 1
C001 1998-07-22 P004 2
C001 1998-07-22 P005 12
C001 1998-07-23 P005 12
C001 1998-07-23 P006 1
C001 1998-07-23 P007 1

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.