Simple Personal Transaction Ledger

[xspdr]

Personal Accounting / Multi-Currency Ledger / Normalization / Relashionship

Hello everyone, I need help to normalize this table:

h.t.t.p.://
i1070.photobucket.com/albums/u485/rodrigo_regis/AccountingNormalization_zpsdf8071b2.jpg

One transaction must have 1 or more rows ("entry lines")
Each row must have:
one account
one currency
one entry
one amount [any number value I input]

Example:

#1, BitFinex, BTC, Debit, 1;
#1, Bank of America, USD, Credit, 480;
#1, Bank of America, USD, Credit, 40; [note: fee]
#2 .....

I am having a very hard time to figure out the best way to achieve this.
Could one of you tell me which tables and keys/relationships I should use?
I have tried a few different scenarios, but they are just not properly set up.
I'd love to have all this structure perfectly normalized
Thank you very much for your help

link to my older, edited post:

h.t.t.p.://
i1070.photobucket.com/albums/u485/rodrigo_regis/table_zps65c1ef53.jpg

 

[Privateer]

I did not view your jpg, but my first impression is you need to build many tables. One of the primary tenants of normalization is to remove duplication. So with your example, you don’t store Bank of America in the transaction table, your database will be huge in size. Here is how it should work.
Build these tables, with the first field as an AutoNumber and primary key, and the others as text fields. Add any other fields that you need, such as address stuff, making sure the information represents a one to one relationship.
tblBank
BankID BankShort BankLong
1 BofA Bank of America
2 BFX Bitfinex

tblCurrency
CurrencyID CurrencyShort CurrencyLong
1 USD US Dollar
2 BTC Bitcoin

tblGLType for General ledger types of transaction
GYTypeID GYTypeShort GYTypeLong
1 CR Credit
2 DB Debit

tblTransactionType
TransActionTypeID TransActionTypeShort TransActionTypeLong
1 F Fee
2 R Refund
3 T Tax

The last table is where they all get combined. This represents the example in your post.

tblTransactions
TransactionID BankID CurrencyID TransActionTypeID GYTypeID Amount
1 2 2 2 1.00
2 1 1 1 480.00
3 1 1 1 1 40.00

 

[xspdr]

Thanks for your reply buddy!

How would you normalize the last table? (tblTransactions)

This is pretty much how my data is been built, but that last table doesn't look righ. There are 4 foreign keys on it.
A few days ago I tried a bunch of different juction tables, pks, fks and relationships, but could never get it right.
Any ideas?
Thanks

 

[Privateer]

Nothing wrong with multiple foreign keys in a table; that constitutes normalization. Everything has a one to one relationship and there is no duplication. I am curious what you would want to do to change it. Let me know.

 

[xspdr]

Your post up here provides a good solution to what I am looking for.

I thought I should have many "many to many" relationships to properly build this data (100% normalized).

At first I had 4 junction tables, but then I gave up because it was giving me too much trouble to understand all the fields and keys, so I decided to go the simpler way with one table, many foreign keys.

Anyone know if this is respecting Normal Form 1-5?

 

[essaytee]

You may be interested in this thread where I compiled a personal accounts schema. By no way complete but it may give you ideas on certain approaches.

By all means pick holes in it, question it as I don't claim to be an expert.

I haven't started the designing of queries and forms just yet due to other things getting in the way.

Steve.

edit:

Anyone know if this is respecting Normal Form 1-5?

Sorry, can't answer this without myself hitting the books.

 

[xspdr]

Very nice mate
Thank you