Normalization: One-to-many out of a junction table without a primary key

[rbrady]

Hello,

I have attached a screenshot of the relationships in (a section of) a database I am working on. Would anyone please be willing to view it and offer any suggestions as to whether is fully normalized or not? Here is the description:

1. An account [table, Account] consists of some money. That money is split-up and invested into several different places.
2. The funds that are available as choices are in Fund; the funds that are chosen are in InvestmentInstance. Different, unaffiliated accounts can invest in the same fund. I would like to keep track of when an account begins to invest in a fund and when it stops investing there (“InceptionDate” and “CloseDate”).
3. The generic, account-independent performance of a fund is kept in Price, where the values come from Yahoo! Finance or something like that.
4. Similar to Price, MarketValue stores the dollar-amount of an investment, specific to an account.

I think I have most of it set OK, but my main concern is about the relationship between InvestmentInstance and MarketValue. As the picture shows, I use InvestmentInstance’s “Id”* as the “one” in the one-to-many relationship, but “Id” is not a primary key. To get it to work, it was necessary to set the Index option to, “Yes (No Duplicates),” so that it was a unique index. Is this a bad practice? Should I just include “Id” as part of the compound primary key? I didn’t think making it part of the compound key was a good idea because then it would defeat the purpose of having “FundId” and “AccountId” control the uniqueness of records. Am I missing something important?

In other words, how can I correctly create a one-to-many relationship that begins in a juntion table (InvestmentInstance) and connects to a new table that was not involved in the normalized many-to-many relationship (MarketValue)?

Thank you!



* I know many like to include the table name in the ID, like, “InvestmentInstanceId,” and it is only my preference to keep it just as “Id” for a local key for simplicity, but add the table name when it becomes a foreign key. I feel “Id” is good enough for my purposes and it’s table location is usually clear from the context.

 

[Rabbie]

It would use the field ID as the primary key in each of your tables. This will be much easier for you to work with than having multifield keys. If you do this then all your relationships should work as per your diagram.

 

[rbrady]

I agree that using only ID fields as the primary keys would be easier to set-up, but wouldn’t that allow unwanted duplicate records in some cases?

For example, if “Id” was the primary key in InvestmentInstance, and “AccountId” and “FundId” were no longer part of the key, then it would be possible to create records where the same account is invested in a fund more than once, right? (I guess this would be OK if the account invested, stopped, and then started again, but that is not an option I want to allow.)

 

[Rabbie]

I agree that using only ID fields as the primary keys would be easier to set-up, but wouldn’t that allow unwanted duplicate records in some cases?

For example, if “Id” was the primary key in InvestmentInstance, and “AccountId” and “FundId” were no longer part of the key, then it would be possible to create records where the same account is invested in a fund more than once, right? (I guess this would be OK if the account invested, stopped, and then started again, but that is not an option I want to allow.)

You can continue to have your composite key in InvestmentInstance but use ID to link to MarketValue thus getting the best of both worlds.

 

[rbrady]

I’m sorry, but do you mean to keep the same composite key in InvestmentInstance (“AccountId” and “FundId”) and leave “Id” a non-primary key, add “Id” to the composite key, or something different? Can I un-key “AccountId” and “FundId” but still have them be the unique identifiers of a record and (as your first post suggests) make “Id” the primary key? That is, I am reading your most recent post and I'm not sure how you mean to change the set-up from the picture. Thank you!

 

[Rabbie]

I’m sorry, but do you mean to keep the same composite key in InvestmentInstance (“AccountId” and “FundId”) and leave “Id” a non-primary key, add “Id” to the composite key, or something different? Can I un-key “AccountId” and “FundId” but still have them be the unique identifiers of a record and (as your first post suggests) make “Id” the primary key? That is, I am reading your most recent post and I'm not sure how you mean to change the set-up from the picture. Thank you!

I will try to explain what I am suggesting. In table InvestmentInstance keep the composite Key as well as having ID as a unique number in each record. The composite key will stop you having unwanted duplicates without having to write VBA to check and prevent this. In MarketValue remove the composite fields you have in the diagram and replace them by a new field to hold the relevant value of InvestmentInstance.Id. The primary Key of MArketValue should be its own ID field. This should also be the case in your table Price .

Remember that composite keys take up more space and are less efficient than single keys.

 

[rbrady]

Thank you, I think I understand you now. To be excruciatingly redundant, you suggest:

• Keep the InvestmentInstance keys the same as in the original screenshot and the no-duplicates property on “Id” as in the screenshot, too.
• In MarketValue, make “Id” the (only) primary key.
• Relate the two tables with “InvestmentInstance.Id” and “MarketValue.InvestmentInstanceId”.

                                   _______________________
 ___________________              |MarketValue            |
|InvestmentInstance |             |=======================|
|===================|             | *Id                   |
|  Id [unique]      | 1 ------- ∞ |  InvestmentInstanceId |
| *AssetClassId     |             |  MarketValueDate      |
| *Fund             |             |  MarketValue          |
|  Symbol           |             |_______________________|
|___________________|

But I disagree about the change to MarketValue and Price with making their “Id”s the primary keys instead of keeping the composite keys. I did have it that way at first, but another user suggested the composite key way:

Normalization of periodic data, third post down​

The composite key prevents a value from being entered twice on the same date for the same thing. (It’s easier for me to think of it in the Price table.) What do you think?

 

[Rabbie]

Yes, I see where you are coming from. It boils down to whatever you are happy with. There are no absolute right and wrongs in this. It is your DB and we only give our opinions for better or worse. You could check for duplicates when adding a record but your system of Keys may well be easier.

Good luck

 

[rbrady]

Thank you very much for your help! btw, I forgot to say that I didn’t know about the space and speed effects of composite keys; good to know!