Quick DB design question

Ancalagon44

Diamond Member
Feb 17, 2010
3,274
202
106
Hi guys,

I'm busy doing a database design for a project that I am working on. The specific area that I have been assigned to is the financial area.

I want a DB design that enforces one debit and one credit per transaction. So, part of the design that I have come up, with a transaction line having a two joins to Account. One DebitAccountId and one CreditAccountId.

Is there any problem with having two joins between two tables? In the past, I know entity framework has moaned when it cant work out which path to follow to delete an item. Ie, if you delete an Account, and the delete cascades, there are multiple paths that it could follow to delete the transaction line. But for whatever reason, entity framework seems fine with this design. I've done a unit test or two.

Does anyone have any advice on whether having two joins between two tables is a good or bad idea?
 

KB

Diamond Member
Nov 8, 1999
5,394
383
126
Having two joins to the same table isn't a problem for the database. The only issue you have is the one you mentioned, that if you try to CASCADE delete from the account table to both relationships, the database will complain. You could just NOT cascade deletes and force the user to delete the transaction line before the accounts.


That said have you ever worked with any other Financial packages? IANAA but I have worked with Great Plains database and they do things differently. The transaction table has only one account field. Each transaction consists of an Account Field, Type field (Debit or Credit), Amount ($), Description and Memo. So to do a debit and credit you need two lines in the transaction table. This is a better design than yours because this allows you more flexibility in your transactions. So for example you can credit the cash account from two seperate asset accounts.
This would be three lines in the transaction table, one credit and two debits.
Then when you do a batch at months end, thats when it verifies that the debits and credits match.
 

Ancalagon44

Diamond Member
Feb 17, 2010
3,274
202
106
Thanks, I'd never thought about that. That's an interesting point.

I can think of a situation where that might apply, and there could be more in future. Its probably a more future proof solution, although I guess validation is a bit tricker. I mean, with CreditAccount and DebitAccount, your validation is more or less built in. With your method, you cant be sure that everything will balance.

Still, it is a more flexible solution. I'll have to think about it.
 

Cerb

Elite Member
Aug 26, 2000
17,484
33
86
How is it more tricky to validate? Whether you use a type field or two separate tables, having less redundant information makes validation more straight-forward. Whether done with a type field, or by two more tables with exclusive entries between them, not having special data to inspect to infer a type is never preferable to declaring the type.

If you take your current design, and change from one debit and credit per transaction to one credit or debit per transaction, you'll be set. That will give you all the flexibility of what KB described, no redundant data, and barely any changes your schema (possibly none, depending on how much you've defined in it). Just move to using left joins to get your flat-file-like output to look at.

As to joins, two joins is nothing, and not remotely a bad idea. I'm working on a DB for an upcoming app that's going to need 2 or more joins to do anything but search if an address exists. I don't know how many it may need in the end, just to pull a single record, but 10-15 is my guess (shades of EAV, so going for 1NF brings me all the way to 3NF). Joins are nothing to be feared, and views should start becoming your friends.
 

pauldun170

Diamond Member
Sep 26, 2011
9,133
5,072
136
Hi guys,

I'm busy doing a database design for a project that I am working on. The specific area that I have been assigned to is the financial area.

I want a DB design that enforces one debit and one credit per transaction. So, part of the design that I have come up, with a transaction line having a two joins to Account. One DebitAccountId and one CreditAccountId.



account table
-------------
-accountnum - pk - not null
-curr_balanceamt

transaction table
-------------
-transationid - pk
-transaction_amount - not null
-debit_accountnum - fk (account.accountnum ), not null
-credit_accountnum - fk (account.accountnum ), not null
-transaction_timestamp

create trigger on transaction table to update acct table
 
Last edited:

Cerb

Elite Member
Aug 26, 2000
17,484
33
86
^ Nulls are entirely avoidable, and the OP seemed quite close already to a 1NF design, at least. Two FKs that are identical is just wasteful, and at least as bad as nulls. Plus, that creates an unnecessary cyclic relationship.

KBs/GPs (with only the minimal amount of fields populated):

Account:
* AccountNum PK

Transaction:
* TransactionId PK
* AccountNum FK:Account.AccountNum
* TransactionType NOT NULL
* TransactionAmount NOT NULL

It's easily the simplest that's still normalized.

Mine:

Account:
* AccountNum PK

Credits:
* CreditId PK
* AccountNum FK:Account.AccountNum
* CreditAmount NOT NULL

Debits:
* DebitId PK
* AccountNum FK:Account.AccountNum
* DebitAmount NOT NULL

If transaction numbers need to be unique globally, there's extra work keeping track of that, though. The easiest way would generally be to add a table just to keep track of those, though triggers could do the same thing (just slower, which could be an issue if you get lots of inserts going on).

Account:
* AccountNum PK

Transactions:
* TransactionId PK

Credits:
* CreditId PK FK:Transactions.TransactionId
* AccountNum FK:Account.AccountNum
* TransactionAmount NOT NULL

Debits:
* DebitId PK FK:Transactions.TransactionId
* AccountNum FK:Account.AccountNum
* TransactionAmount NOT NULL

-OR-

Account:
* AccountNum PK

Transactions:
* TransactionId PK
* SubtransactionId FK:Credits.CreditId or FK:Dedits.DeditId NOT NULL

Credits:
* CreditId PK
* AccountNum FK:Account.AccountNum
* TransactionAmount NOT NULL

Debits:
* DebitId PK
* AccountNum FK:Account.AccountNum
* TransactionAmount NOT NULL

It should be clear how this quickly becomes like what KB described, as you start merging redundant rows into Transactions. OTOH, you may have a real-world want or need for separate tables for each type, and there's nothing wrong with that.
 
Last edited:

pauldun170

Diamond Member
Sep 26, 2011
9,133
5,072
136
^ Nulls are entirely avoidable, and the OP seemed quite close already to a 1NF design, at least. Two FKs that are identical is just wasteful, and at least as bad as nulls. Plus, that creates an unnecessary cyclic relationship.

KBs/GPs (with only the minimal amount of fields populated):

Account:
* AccountNum PK

Transaction:
* TransactionId PK
* AccountNum FK:Account.AccountNum
* TransactionType NOT NULL
* TransactionAmount NOT NULL

It's easily the simplest that's still normalized.

Mine:

Account:
* AccountNum PK

Credits:
* CreditId PK
* AccountNum FK:Account.AccountNum
* CreditAmount NOT NULL

Debits:
* DebitId PK
* AccountNum FK:Account.AccountNum
* DebitAmount NOT NULL

If transaction numbers need to be unique globally, there's extra work keeping track of that, though. The easiest way would generally be to add a table just to keep track of those, though triggers could do the same thing (just slower, which could be an issue if you get lots of inserts going on).

Account:
* AccountNum PK

Transactions:
* TransactionId PK

Credits:
* CreditId PK FK:Transactions.TransactionId
* AccountNum FK:Account.AccountNum
* TransactionAmount NOT NULL

Debits:
* DebitId PK FK:Transactions.TransactionId
* AccountNum FK:Account.AccountNum
* TransactionAmount NOT NULL

-OR-

Account:
* AccountNum PK

Transactions:
* TransactionId PK
* SubtransactionId FK:Credits.CreditId or FK:Dedits.DeditId NOT NULL

Credits:
* CreditId PK
* AccountNum FK:Account.AccountNum
* TransactionAmount NOT NULL

Debits:
* DebitId PK
* AccountNum FK:Account.AccountNum
* TransactionAmount NOT NULL

It should be clear how this quickly becomes like what KB described, as you start merging redundant rows into Transactions. OTOH, you may have a real-world want or need for separate tables for each type, and there's nothing wrong with that.

An account can be many types (credit, debit, whatever)
A credit is a transaction
A debit is a transaction
A debit account is an account
A credit account is an account
Each transaction is defined by a debit of one account and a credit to another (flow of funds from one account to another account).

In your example you have 4 tables
An account table, a master transaction table and 2 sub transaction tables.
Each transaction would result in 3 inserts (the 3 transaction tables) and 2 updates (to the account table)
Each query would require a join of 4 tables


note: transactionID was a typo. It doesn't need it.
account table
-------------
-accountnum - pk - not null
-curr_balanceamt

transaction table
-------------
-transationid - pk
-transaction_amount - not null
-debit_accountnum - fk (account.accountnum ), not null
-credit_accountnum - fk (account.accountnum ), not null
-transaction_timestamp


Note #2: I see where you're coming from now.
I was in cleaning basement mindset, not DB mindset.
 
Last edited:

Cogman

Lifer
Sep 19, 2000
10,277
125
106
My company just so happens to work on financial software which is completely in charge of tracking transactions across various accounts and aggregating information for those accounts.

Here is my suggestion.

Ditch the idea of having a credit/debit table. After all, the only real difference between the two is the sign on the amount transacted. Instead you should think about having a single transaction table which tracks transactions for all accounts. Figure out in the software whether a given transaction is a debit/credit. If you are assuming all accounts are on the system (rarely happens) you can enforce in software that all the debits/credits for a transaction balance. However, most real systems don't have all accounts involved.

Our real, in the wild system, has a single transaction table. It has transaction types, but the types aren't credit/debit. Rather the types are things like Buy, Sell, short sell, buy cover, dividend, etc. The code manages whether a transaction for a given type is valid.
 

Cerb

Elite Member
Aug 26, 2000
17,484
33
86
In your example you have 4 tables
An account table, a master transaction table and 2 sub transaction tables.
Each transaction would result in 3 inserts (the 3 transaction tables) and 2 updates (to the account table)
Each query would require a join of 4 tables
A join of 2-3 tables, as the Transaction table could be ignored for most queries. Cogman's and KB's are definitely better, though. I would try implementing what I described to save time and effort if the software were already far enough along to make such changes annoying to make (it was a way to minimize application-visible schema changes, compared to what was already being worked on). If hardly anything has been written, yet, the explosion of tables should be totally unnecessary.
 
Last edited:

KentState

Diamond Member
Oct 19, 2001
8,397
393
126
I typically see this impletemented as

Transaction_Table
------------------
TansactionID
AccountID
TransactionType (C or D)
TransactionAmount
.
.
.

You may have an additional key if the transaction ID is derived. The TransactionType could simply be a UDT (user defined type), a FK or a simple boolean value.