Relationships using a reference table environment (1 Viewer)

Gavx

Registered User.
Local time
Tomorrow, 01:16
Joined
Mar 8, 2014
Messages
151
Relationships.jpg


I wanted to create a database that enable me to characterise bank transactions for tax purposes.
The process is, get transactions from my various bank accounts into tblImport and then selectively add them to tblAccountTransactions. I then review each transaction and characterise is as say something that should be in tblRental or tblDividends.
I am now getting to the stage where things are getting complicated and before I proceed would appreciate if anyone felt inclined to take a quick look at the relationships and advise whether there are any glaring faults.
I believe the weird characteristic with this is that tblAccountTransactions is a reference table and therefore all the details comes from the bank (via tblImport) without any regard to Normalisation.
Any suggestions? Thanks for your time.
Gavin
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:16
Joined
Aug 11, 2003
Messages
11,696
You want to characterize your transactions, this would normaly mean you have 1 type "tblCharacters" instead of having two tables
I.e. Your tblCategories

I would not make this so that one transaction can have 1 or more rentals or Dividends or equities this is asking for problems down the line.
If you need to support this you want to split your transaction into muliple lines.

You seem to lack something like "counter party"

I have my concerns about your rental table instead of splitting income into columns you should be splitting them into rows. That is in a proper normalized environment

tblIntrest seems noting more than a category however you seem to be lacking "Debet" or "Credit" as a property/column in your transactions/import table.

tblAccount has an AccID your transactions table SHOULD have this ID as a reverse to it not based on your AccountNo.


Probably more when I really try :( Sorry to be harsh
 

vba_php

Forum Troll
Local time
Today, 09:16
Joined
Oct 6, 2019
Messages
2,884
The process is, get transactions from my various bank accounts into tblImport and then selectively add them to tblAccountTransactions. I then review each transaction and characterise is as say something that should be in tblRental or tblDividends.

I believe the weird characteristic with this is that tblAccountTransactions is a reference table and therefore all the details comes from the bank (via tblImport) without any regard to Normalisation.
I might be able to offer something here worthwhile. It looks semi-OK to me, Gavin. Since I am #2 to post a reply here, The one thing I would say (or maybe 2 things), is that you seriously have to have a keen eye when downloading table data from any large organization because those places (at least from what I've seen) don't pay attention to detail when it comes to considering what the "little guy" sees when he/she downloads the transaction data. and I'm sure many engineers at these large companies don't care, because it's "not their problem". I've heard that countless times. In terms of your structure, the thing I would say about that is.....it really depends on what you're going to do with the data (types of reports to be generated, any aggregation in querying, etc...). I would assume that the setup you have right now could serve plenty of purposes, but if you're going to do what a lot of people *normally* do with banking data, like generating reports on it, mailman's suggestions would probably serve you well in terms of changing stuff so the data can be *universally* accepted by Access in terms of what it can do for you and what it can't.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:16
Joined
Feb 28, 2001
Messages
27,003
Let me feed back what I see, not from your words, but from your structure. That may give you insight as to what you built. Then you can decide if that was what you wanted to build.

You appear to have four sources of income: Rentals, Interest, Equities, and Dividends. You have a "native" ID for each transaction source (I.e. RentalID, EquityID, InterestID, DividendID). You roll these up into a single table, Transactions, where there is another ID local to that table and a foreign key for which you have potentially four contributors (your four income type tables). The rest of your tables provide details subsidiary to each type of income source.

Now, a couple of comments:

1. On the right side of your diagram you have a path leading from Transactions to Equities to Equity Name. You also have a path leading from Transactions to Dividends to Equity Name. This is a "split path" situation (i.e. two different routes from point A to point B) that will drive Access bonkers when it tries to build detailed queries. The query wizard should balk at that. You might be able to manually build the right SQL, but that structure is going to eat your socks any time the query wizard comes into play.

EDITED next item after a second look:

2. You have a four-way split on what the foreign key TransactionID references. Even if the arrow points the other way, it is impossible to gather records from four parallel sources that way. It is a normalization violation in that the content of that field doesn't reference a uniquely located child. This is in the same vein as Namliam's comments about "rows vs. columns" but with a different focus.

3. Somehow I'm missing something, but if this is a "normal" Interest table, it ALSO should have a bank account ID. I see no relationship from the bank accounts to the interest table. Is that intentional? Is the interest NOT from a bank account or is what I see just a result of a "work in progress"?

What I might do differently is dump the transaction table completely, in favor of a four-way UNION query that you can construct from the four basoc sources - except that I would also collapse your Dividend and Equity tables to a single table if that is possible and have two types of transactions in it, both stemming from a single source (either a dividend from an equity item, or a sale or purchase of an equity item), which then means three-way UNION query for transactions. You can use UNION queries as recordsources, so reports will work OK. But you are trying to squish disparate items into a single item in way that might "eat your socks" down the road. Dumping the transaction table dumps the split path problem.

There might be more to consider but that right there is enough to get you at least thinking.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:16
Joined
Sep 12, 2006
Messages
15,614
Given that transactions on a bank statement have very little information

Date, Tran type, Description, DR Amount, CR Amount - I struggle to see how you will reliably analyse the data from this - or do you have access to more data fields than this?

of course if the different bank accounts themselves indicate how you need to treat the transaction, then that might make it more straightforward.
 
Last edited:

vba_php

Forum Troll
Local time
Today, 09:16
Joined
Oct 6, 2019
Messages
2,884
Given that transactions on a bank statement have very little information
aint that the truth! I remember a project involving downloading ACH info from a website into excel and automating the re-upload. same thing happened there. annoying.
 

GinaWhipp

AWF VIP
Local time
Today, 10:16
Joined
Jun 21, 2011
Messages
5,901
Hmm, in addition to the other comments I see you are using words that are reserved in Access, i.e. Category, Memo. This is not a good idea as it will cause you issues down the road. I would changing those names. For a complete list see...
 

Gavx

Registered User.
Local time
Tomorrow, 01:16
Joined
Mar 8, 2014
Messages
151
Thanks for your suggestions, I will look into.
Just to explain the process, the data from the bank goes into tblImport. I select transactions records in this table via a checkbox and then import them into tblAccountTransactions, so this table contains all transactions from all accounts. I then go through this table line by line categorising each transaction. At that point a record is written to any of the other tables depending on its category.

So the tblAccountTransactions is very much a reference table and other tables relate to it. I am sensitive and hampered in coming from a spreadsheet background and this is why I ask for guidance - because it seems this is a problem resembling a spreadsheet challenge.

The reports just come from each table. DR and CR and handled via +/-.

The remark about the rental table, amounts being in rows rather than columns is interesting and was considered but I wasn't sure how to do this. More thinking obviously.

Thanks again
Gavin
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:16
Joined
Feb 19, 2002
Messages
42,983
I don't see a problem with tblAccountTransactions being related to four tables or with tblEquityName being related to two tables as long as you are aware that your queries will be complicated and require left joins if you want to include more than 1 of the four related tables in the same query. I'm assuming that any given TransID would appear in one and only one of the four tables. So a transID is either interest, rental, divident, or equity.

Once thing I don't like is that you are not using the autonumbers as your foreign keys. Autonumbers should be the PK of a table where they exist. If you have a natural unique ID, you could use that instead. Typically these natural unique IDs come from some other system and so you don't have any control over them. That means that I would not use them as a PK. Use the autonumber as the PK. Leave the natural unique ID in the parent table. That leads me to confusion regarding tblImport which seems to have some data from tblAccounts and some from tblAccountTransactions. Data fields such as AccountNo should NEVER appear in multiple tables. They should be in one and only one table and I can't tell which table that should be.

The schema needs some work and without more knowledge of the application, I can't put my finger on the solution. I can only see things that don't look right.

And finally, using different names for the same PK/FK just adds confusion so is it TransID or TransNo?
 

Gavx

Registered User.
Local time
Tomorrow, 01:16
Joined
Mar 8, 2014
Messages
151
I'm assuming that any given TransID would appear in one and only one of the four tables. So a transID is either interest, rental, divident, or equity.

Correct. The purpose of the database is the raw transactions from a CSV file are imported into tblImport and during this process an Account Number (read from tblAccounts) is assigned to each record. tblImport is viewed and records are selected via checkbox that are are to be imported into tblAccountTransactions (the rest are discarded and tblImport is emptied).
Records in tblAccountTransactions are then reviewed and categorised as Rental, Dividend etc. Any reporting is done from tblRental, tblDividends, tblInterest etc. No reporting will ever occur in tblAccountTransactions - it is just a warehouse of transactions.

Autonumbers should be the PK of a table where they exist
This is implemented.

using different names for the same PK/FK just adds confusion so is it TransID or TransNo?
My mistake, should be TransID.

Thanks a lot for your time.
 

Users who are viewing this thread

Top Bottom