CODEWITHSUNDEEP
sql-serverdatabasedatabase-design
Stan
Stan

Reputation: 26501

Bank transactions table - can this be done better?

I am wondering what is the best way to make bank transaction table.

I know that user can have many accounts so I add AccountID instead of UserID, but how do I name the other, foreign account. And how do I know if it is incoming or outgoing transaction. I have an example here but I think it can be done better so I ask for your advice.

In my example I store all transactions in one table and add bool isOutgoing. So if it is set to true than I know that user sent money to ForeignAccount if it's false then I know that ForeignAccount sent money to user.

My example

enter image description here

Please note that this is not for real bank, of course. I am just trying things out and figuring best practices.

Upvotes: 1

Views: 10893

Answers (4)

kaj
kaj

Reputation: 5251

I'd agree with the comment about the isOutgoing flag - its far too easy for an insert/update to incorrectly set this (although the name of the column is clear, as a column it could be overlooked and therefore set to a default value).

Another approach for a transaction table could be along the lines of: 

  TransactionID (unique key)   
  OwnerID   
  FromAccount  
  ToAccount  
  TransactionDate  
  Amount

Alternatively you can have a "LocalAccount" and a "ForeignAccount" and the sign of the Amount field represents the direction.

If you are doing transactions involving multiple currencies then the following columns would be required/considered 

  Currency  
  AmountInBaseCcy  
  FxRate

If involving multiple currencies then you either want to have an fx rate per ccy combination/to a common ccy on a date or store it per transaction - that depends on how it would be calculated

Upvotes: 1

Diego
Diego

Reputation: 36146

My opinion:

  • make the ID not null, Identity(1,1) and primary key
  • UserAccountID is fine. Dont forget to create the FK to the Accounts table;
  • You could make the foreignAccount a integer as well if every transaction is between 2 accounts and both accounts are internal to the organization
  • Do not create Nvarchar fields unless necessary (the occupy twice as much space) and don't create it 1024. If you need more than 900 chars, use varchar(max), because if the column is less than 900 you can still create an index on it
  • create the datetime columns as default getdate(), unless you can create transactions on a different date that the actual date;
  • Amount should be numeric, not integer

Upvotes: 1

Randy
Randy

Reputation: 16677

usually, i think, you would see a column to reflect DEBIT, or CREDIT to the account, not outgoing.

there are probably several tables something like these:

ACCOUNT
-------
account_id
account_no
account_type

OWNER
-------
owner_id
name
other_info

ACCOUNT_OWNER
--------------
account_id
owner_id

TRANSACTION
------------
transaction_id
account_id
transaction_type
amount
transaction_date

here you would get 2 records for transactions - one showing a debit, and one for a credit if you really wanted, you could link these two transactions in another table 

TRANSACTION_LINK
----------------
transaction_id1
transaction_id2

Upvotes: 1

HLGEM
HLGEM

Reputation: 96552

I think what you are looking for is how to handle a many-tomany relationship (accounts can have multiple owners, owners can have mulitple accounts)

You do this through a joining table. So you have account with all the details needed for an account, you have user for all teh details needed for a user and then you have account USer which contains just the ids from both the other two tables.

Upvotes: 0

Related Questions