Postgresql – How to change schema so that account_id reference is unique among 3 tables

database-designpostgresqlschema

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!

Best Answer

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.

Related Question