0

Original image: enter image description here

Updated to have more-correct terminology and an 'is_debit' column: enter image description here

I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting.

'jackpots', 'users', and 'house' are each tables which reference an account_id, which in turn is used in the journal to record a credit or debit of money 'amount'.

I want to assure that the account_id is only in one table -- and that it is unique in that table.

What is the best way to refactor the tables so that account_id is guaranteed to appear only 1x in all of them?

What I've considered:

  1. Creating another table, 'account_type' that has different codes for user accounts, jackpot accounts, and the house's account. Then add two colums to 'accounts', ('account_type' and 'ref_id', the latter of which will reference the id of 'jackpots', 'users', or 'house'). This seems somewhat inelegant since I wouldn't know how to link the table type to the table name using SQL, alone. I don't mind using triggers, though, if this is the only way for an elegant, foolproof solution.

  2. Looking for some sort of built in constraint that says account_id cannot be used more than once among 3 tables. Would guess that this doesn't exist, however.

Help is appreciated!

  • Are you building a modest General Ledger or a Subledger that will tie to control accounts in an external General Ledger? – Pieter Geerkens Jul 24 '14 at 20:47
1

Start with:

  1. Create a ChartOfAccounts table with the Account code as Primary Key.
  2. Add a Foreign Key constraint to ChartOfAccounts on all tables with an AccountCode field.
  3. Use an IsDebit field, not the numeric sign, to distinguish Debits from Credits and reserve negative signs for transaction reversals (if used at all). This is necessary in order to generate T-Balances and Trial Balances properly from your Journal and Ledger
  4. Create a Journal table with Primary Key: TransactionType, PostingDate, Account, SubledgerCode, IsDebit and minimum attributes of: Amount, CreatedDate, CreatedBy, DocumentReference
  5. Design and spec a stored procedure (or type of routine suitable for where your business logic is located) for each type of transaction to be handled by the system. For your system these might be:
    • Ticket Purchase for Cash
    • Ticket Purchase on Account
    • Prize Payout in Cash
    • etc.

Please note that I am CGA, CPA in addition to being primarily a professional developer.

Update - Terminology:

  • A Journal is a chronological list of the details of all transactions of a given type, such as Cash Receipts, Cash Disbursements, Sales, etc.
  • A Ledger is a listing By Account of the aggregates of all transactions in a given time period.

It is occasionally necessary or expedient, when a wide variety of transaction types will be supported by the system (or to increase parallelism, as when many clerks need to be working at once Bob Cratchit style), to have multiple Journal files with different structure.

In a modern SQL Server system with only one Journal the Ledger could be defined as an Indexed View on the Journal. This would eliminate the need for either a trigger on the Journal to update the Ledger, or a batch-processing design.

Also, it is acceptable to have separate DrAmount and CrAmount columns in place of an IsDebit flag and single Amount column.

  • Thanks, for the answer, Pieter! I think I messed up terminology in my diagram: the table 'ledger' should actually be 'journal'. I'll update my image. – Nathan Lippi Jul 24 '14 at 20:45
  • @Nathan: A Ledger is different from a Journal. Both are necessary in either a General Ledger or Subledger system. You also really should take at least a half-course in Bookkeeping before ever attempting to write an accounting system of any complexity (or lack of it). I would like to retire some day and the world is already full enough of crappy accounting implementations to keep me employed well into a second century. – Pieter Geerkens Jul 24 '14 at 20:51
  • Noted. Will be doing some research the next few days, though it'll probably take a bit longer to get beyond the basics. I'm not making a double-entry accounting system for the end user but rather need to be very confident that the financial transactions we have sum to 0 before being added to the journal (like with a stored procedure). If it is easy to sum up, what is it that usually makes a accounting system crappy? – Nathan Lippi Jul 24 '14 at 21:05
  • @Nathan: For starters: (1) Using numeric sign instead of an IsDebit flag to distinguish Debits from Credits. (2) Not having both a Ledger and a Journal. (3) Not defining Foreign Keys wherever possible. (4) Not normalizing the database fully. Incomplete normalization may be necessary for performance in a reporting-only database, but complete normalization is essential in a transactional database. (5) Not making it double-entry. and my favourite (6) Not modelling the design on the 800-year old tried-and-true paper-and-pencil procedures of double-entry bookkeeping. – Pieter Geerkens Jul 24 '14 at 21:09
  • Thank you for the breakdown. Re. (1) I can see how there would be problems querying using amount > 0 since debit/credit don't always correspond with only negative/positive values. Is this why you so strongly suggest the flag? (2) Isn't a ledger just a view of a journal? If a ledger is pre-calculated doesn't that make the db not fully normalized? (3) Makes sense. (4) You can see how in the chart of accounts table (which I just updated), I'm planning on summing the balance using a trigger. Would this violate your rule of full normalization? (5) Agreed :). (6) Gonna hit the books. – Nathan Lippi Jul 24 '14 at 22:09
0

Thanks to @PieterGeerkins I've started to take a look into double-entry bookkeeping more, which seems to have helped me resolve the original problem.

Using one 'account_id' in 'chart_of_accounts' for the jackpot (instead of one 'account_id' for each jackpot time period) allows me to not worry about referencing 'account_id' from the table 'jackpots'. I'll still maintain a reference to the 'journal' transactions for each jackpot period using 'jackpot_id' in the 'transactions' table. 'jackpot_id' will sometimes be null for transactions not involving jackpots (such as deposits and withdrawals), but this sometimes-unused column seems a small price to pay considering the simplification that it allows, and considering that the vast majority of transactions will likely involve a jackpot.

The 'house' table turned out to be unnecessary, since it would have only contained a few lines which can be put at the the top of the 'chart_of_accounts' table.

Any additional feedback is more than welcome.

enter image description here

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.