Solved Help setting up a new Investment Portfolio database using normalization

[mike60smart]

I looked at what you did, and I think you don't understand, @mike60smart, ETF, Foreign tax, & Money Market are not mutually exclusive nor dependent on one another. They are individual independent binary states. As far as I can guess, every combination of the 3 (all 8 possibilities) are possible, but as far as I know there may only be 2-3 possibilities. As I cannot saddle my data with such limitations, leaving them separate allows for every possibility. As far as I know, there could be more possibilities unknown to me now to cover, I cannot predict everything I might need the future, I just know I need these.

Specifically, a "core class" account is the only account type that can store cash. Therefore, before allowing any monetary transaction a "core class" account must be selected, that's easy, just use that account type. But many transactions are independent of ETF, Foreign tax, & Money Market designations. 2 of these 3 aren't even important now, I have them in there because these accounts may be handled differently in certain situations, I want them in there because they could be important later when I add a functionality to this database. Really, I don't even want to have types 2&3 there in your table, but simply binary yes/no's to tell my forms and reports how to behave, such as whether or not a tax on a transaction is a foreign tax. As a matter of fact, I also need a field to denote the foreign entity of the tax, perhaps a Country, or a Region, just to reconcile the taxes paid with the US Federal Income Tax return documents. That doesn't need a separate account type as far as I knew originally, but I understand where you guys are coming from with this desire to place it in the account type table. So I go along with it, I know I am in a learning process.

So are you saying that the tblAccDirectory could have 1 or more of the Account types associated with it?

If this is the case then it would be managed differently.

Here with an example in the attached.

 

[HalloweenWeed]

So are you saying that the tblAccDirectory could have 1 or more of the Account types associated with it?

If this is the case then it would be managed differently.

No, I'm saying that as far as I know now:
All ETF accounts are foreign taxed. Just because I think so, not positive.
There may be non-ETF's with foreign tax.
Money Market has nothing to do with whether or not it is an ETF, or has foreign tax.
I don't know whether or not any non-ETF's will have foreign tax, it's beyond my knowledge.
I may not know when an account is foreign taxed when I create the account. I only know after I receive the end-of-year tax documents.

So, each one is just there as a separate property to the account. You guys are just making this to difficult, it's not that difficult to just have those binaries there. This is why I wanted it in the account directory, not in the account type table.

 

[mike60smart]

No, I'm saying that as far as I know now:
All ETF accounts are foreign taxed. Just because I think so, not positive.
There may be non-ETF's with foreign tax.
Money Market has nothing to do with whether or not it is an ETF, or has foreign tax.
I don't know whether or not any non-ETF's will have foreign tax, it's beyond my knowledge.
I may not know when an account is foreign taxed when I create the account. I only know after I receive the end-of-year tax documents.

So, each one is just there as a separate property to the account. You guys are just making this to difficult, it's not that difficult to just have those binaries there. This is why I wanted it in the account directory, not in the account type table.

OK so put it another way.
If you have fields in a table for specific TAX Types and in the future there is a need to add additional TAX Types it means
you have to Modify the Table, any related queries, Forms and Reports.

This is why a List of Tax Types should always be in a separate table.

Up to you if you want to ignore the advice.

 

[HalloweenWeed]

OK so put it another way.
If you have fields in a table for specific TAX Types and in the future there is a need to add additional TAX Types it means
you have to Modify the Table, any related queries, Forms and Reports.

This is why a List of Tax Types should always be in a separate table.

Up to you if you want to ignore the advice.

Well, I suppose it could be a "tax entity" field: 0 = No tax, 1 = entity 1, etc. I could work with that. Could that be in the Account Directory, instead of the account type table?

 

[Pat Hartman]

The four "cash" fields are still suspect. This looks like a repeating group. Can ALL of the values be true at once or can only one be true? If only one can be true, there should be only a single field and that field would contain one of the four value types rather than having four fields with true/false values, you have one field with one of four values.

 

[HalloweenWeed]

The four "cash" fields are still suspect. This looks like a repeating group. Can ALL of the values be true at once or can only one be true? If only one can be true, there should be only a single field and that field would contain one of the four value types rather than having four fields with true/false values, you have one field with one of four values.

OK, now I think I understand what plog was trying to convey, that's exactly it. All these are really descriptors of the transaction type, and your answer is definitely no, they were there for me to use to either poll for the working of my forms, and/or to quickly see myself what the transaction type ID was for a particular transaction when programming/building the forms and reports. So, by you guy's definition of not part of a normalized database they need to go. But for me, that's like taking a jump without a safety net. I will remove them, but I have little confidence that I can build this correctly afterward.

And this has helped me understand @plog's post:

Nope, still not it. I just don't know how to impress upon you this point any further:

You should not use values as names. Table/Field names should not be terms specific to your data. Names should be so generic anyone familiar with the english language should be able to tell what is in them.

Your transaction table still has values as names. It should have a lot less fields and all those values should go in as value in records, not as field names in that table. Your transaction table should be closer to this:

tblTransTypes
tt_ID, atuonumber, primary key
tt_Name, text, name of transaction (buy, sell, fee, dividend, etc)
tt_Value, number, determines how transaction effects total (1 is credit, -1 is debit, 0 doesn't effect total)
tt_PerShare, Yes/No, determines if this transaction is multiplied per share or just applied by itself

Than the tblXactntyp table you have.

OK I'll give it another shot.

 

[Pat Hartman]

So, by you guy's definition of not part of a normalized database they need to go. But for me, that's like taking a jump without a safety net. I will remove them, but I have little confidence that I can build this correctly afterward.

Of course you can, and it will be ever so much easier You won't need the validation logic (which I seriously doubt that you have anyway) that prevents any more than one value from being true. You also only have one field to test in a query. That means instead of having four queries, one to bring back each type of data, you have ONE query with an argument. The argument specifies which type you want the query to return at any given point in time. I don't know if it is possible for the list to be expanded but if it is, expansion is totally transparent. NO changes need to be made to anything. You simply add a new value to the RowSource list and that's it - PERIOD. No queries need to change, no forms, no reports. YEAH!!!!!!!!

 

[HalloweenWeed]

tblTransTypes
tt_ID, atuonumber, primary key

One more thing: Why have these 3 fields: tt_ID, autonumber, primary key; or is this just one in the same field, fully described? Please clarify. Thank you.

 

[plog]

That's one field. The name, the data type and the description of what it's for

 

[HalloweenWeed]

OK, I think I've done that, but not sure if I should carry this further to combine the tax & fee tables and the cash amount in the transaction table, into a single currency table, and an 'entry type' table to describe whether or not the entry is Tax, Fee, sale, or purchase. Here's what I have so far:

 

[HalloweenWeed]

The problem with combining the cash entries into one table, as far as I know, is that it may necessitate multiple entries for one transaction. One of my original specifications was that I wanted to eliminate multiple entries per transaction. As I believe I stated before, one transaction may or may not have taxes and fees.

 

[HalloweenWeed]

Whoops, I just seen a mistake in table reference links to the Tax table and the Fees table. I will fix...

 

[HalloweenWeed]

Table references fixed, and added the "Entity table." Moved the 'tax entity' field to the account directory:

 

[Pat Hartman]

Tax is an optional column. It is filled or not filled depending on whether the transaction was taxable. So, it appears for EVERY transaction. If there are rules, you will need to enforce them using code although a flag in your transaction type table might help. I don't know what the rules are so I can't tell you what to do but there are three possibilities for each transaction type. Tax is required; which means it must be filled in. Tax is optional or Tax is N/A and so not allowed. If you can't make a "required" or "never" statement, Tax is most likely optional and might appear on most transaction types.

 

[HalloweenWeed]

Tax is an optional column. It is filled or not filled depending on whether the transaction was taxable. So, it appears for EVERY transaction. If there are rules, you will need to enforce them using code although a flag in your transaction type table might help. I don't know what the rules are so I can't tell you what to do but there are three possibilities for each transaction type. Tax is required; which means it must be filled in. Tax is optional or Tax is N/A and so not allowed. If you can't make a "required" or "never" statement, Tax is most likely optional and might appear on most transaction types.

HI Pat,
This is not like retail sales where every purchase has a domestic tax except for exempt mdse. That said, the tax in question is levied by the home government of the account institution (generally either a Corporation or a fund institution which may or may not be non-profit). So far, I have only seen tax on the sales and dividends of ETF's (Electronically Traded Funds, a term that generally denotes the stock is traded on a foreign stock exchange, and dealt by a 3rd party broker, it is that broker that must levy the taxes according to either that Country's laws, or the laws of the foreign exchange entity such as Hong Kong for instance). That's not to say I won't ever have a non-ETF taxed, I don't know that answer, and I also think one would need to know the future to decide such a thing.

Otherwise, so far, taxes are levied on my personal income, based on the U.S. Income tax, not the domestic trades, and that is accounted for in my personal finances, not this database.

I don't know the rules of these taxes levied on the trades/dividends by foreign entities, and it's also possible that they could change over time. So I don't intend to even learn such rules, there is no need for me to do that, simply to enter the transactions as levied. Remember, this is not accounting for a retail POS, but otherwise this is just like accounting/bookkeeping, which can generate stats. Therefore, tax is optional on a entry-to-entry basis in this database.


So far, at a glance, it looks as if only 10-20% of my transactions are taxed. That's why I moved taxes to another table. For some reason, I thought (or misunderstood?) that when a field is not used in most transactions, part of normalization was to move it to a different table (because it doesn't need to be entered on every transaction). You say it was a column. By that reasoning Fees is also a column, so I need to move fees back to the transaction table too?

 

[Pat Hartman]

I have portfolio transactions where I see tax also. The taxes are always from foreign jurisdictions at the moment. That may change since the Biden administration needs to raise more money (cutting spending is not a concept that politicians understand regardless of political party) and are looking at more ways to tax the "rich". So, we may see US taxes on portfolio transactions for retirees as well as the "rich" since the middle class always pays taxes no matter what. Either you make a separate transaction for tax when it occurs OR you have a tax field that is optionally filled in. Once you have multiple jurisdictions levying taxes, it ends up like payroll where tax is a separate line item.

Moving taxes to another table because they occur infrequently is overkill. That advice is targeted at space saving. Do you need to save space? Do you have millions of rows so that saving a small amount of space per row could be important? A second transaction is the most flexible solution because you are going to need to include.

If "fees" is the sum of all fees, then you could treat it like tax and keep it as a column. If there are different kinds of fees and I think there are, then it is best to keep them as rows in the transaction table if you need to track them separately.

If the fees and taxes need to be linked to the transaction that spawned them, then you would be justified in making a child table to hold transactions on transactions.

I would need to spend a lot more time investigating the actual rules before making a firm commitment to what IS best. But, with limited knowledge, separate transactions or a separate table of transactions on transactions would be OK. Don't arbitrarily separate columns to separate tables because they are sparse unless you really need to save space.

The key is how your portfolio manager reports them to you. Are they separate transactions or fields on a single transaction? You can take their lead.

 

[HalloweenWeed]

The key is how your portfolio manager reports them to you. Are they separate transactions or fields on a single transaction? You can take their lead.

They are separate transactions by the report from the broker. But I don't want to keep them that way, and I want to enter them simultaneously (on 1 master form).

If "fees" is the sum of all fees,

It is not, per se.

If the fees and taxes need to be linked to the transaction that spawned them, then you would be justified in making a child table to hold transactions on transactions.

There is a need, in showing statistics, to link the fees to the account (for a total or period total), not the transaction itself - except to get the date.
I need a list of taxes paid to each entity after the end-of-year. So taxes tied to entity? Should I just include a "tt_TaxEntity" field in my tax table? Or could that be easily found through the 2 links between the tax table, transaction table, and Account Directory? Seems like doing so would compromise normalization.


ALSO: I just re-added a date field to the transaction table: ta_XactnDate.

 

[Pat Hartman]

But I don't want to keep them that way, and I want to enter them simultaneously (on 1 master form).

Deviate at your peril then. They know something you don't know.

As I said, I don't know enough to make a firm decision, I would follow the lead of the reports you get from the broker. They know what the federal reporting requirements are.

 

[HalloweenWeed]

So, if I keep the tables separate, will I need to use subforms on my master form for the tax & fees? Or a single query that accomplishes the same end result? I don't want to make a new record on each one of these every time a new transaction is entered, just whenever the transaction has a tax or fee. I don't know how to do that without relying entirely on vba to record the tax table and the fees table. Wouldn't this mean that if I don't use vba, I need to move the tax and the fees back to the transaction table? I believe that when a new record is created on the master form, it would also create new records on the linked subforms, or the tables linked to by a (single) query. Am I correct?

 

[Pat Hartman]

You should use tiny subforms. All you need to do is to enter an amount, which you have to enter no matter what.

I believe that when a new record is created on the master form, it would also create new records on the linked subforms, or the tables linked to by a (single) query. Am I correct?

No, that is not correct.

Bound forms handle the problem just fine. If you don't dirty the subform, no record would be created. If you dirty it by entering a tax or fee amt, Access automagically populates the foreign key provided you have the master/child links set correctly.

In the vast majority of forms, the only VBA code you ever need is code in the form's BeforeUpdate event to validate data. Access takes care of pretty much everything else all by itself. You don't need navigation code, although some people like to use custom buttons because that is build in. You don't need save code because that is built in. The code you actually need is the code to prevent saving invalid data. For example, have you ever seen a date like 2/12/203? It happens more frequently than you might imagine. The date is perfectly valid as far as the date data type is concerned. It is just illogical unless you are working with ancient event data but it is a common type of typo that your validation code should catch because the date is out of what would be the logical range for any date in most applications.