... but all previous update information history is also required, i.e., the information from report Q4 2015 and Q2 2017 must both be stored (despite Q2 2017 being the most "up-to-date" information used for generating reports). For more backstory, I'm trying to model a database for an investment company. Here's a quick "plain-english" breakdown:
The company requires a fund manager tracking database. They want to track of information about funds, their mandates (which are essentially 'portfolios of investments'), and information contained on a quarterly manager report.
- Each fund can have any number of mandates, but each mandate belongs to only one fund.
- Each mandate receives quarterly updates in the form of a manager report. Each mandate will have many quarterly reports, but each report belongs to only one mandate.
I've attached a photo of my tables relationship. To explain the problem I'm having a little better: say the company has three different funds that contain 15 different mandates, and thus receives 15 manager reports each quarter. I wanted to use 'Quarter and Year of Report' as my primary keys for the Manager Report entity. However, I realized that each of the 15 reports received in Q4 2016 would have the same primary key identifier. Would this be a situation where a surrogate key is required? For instance, if I created a "QuarterID" attribute classified as a AutoNumber, would that suffice?