Results 1 to 2 of 2
  1. #1
    ChaseC is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    18

    Question about database structure when quarterly updates are required...

    ... 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?
    Attached Thumbnails Attached Thumbnails TablesRelationships.PNG  

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    You shouldn't look at table key fields like that, they are just meant to be internal unique numbers to link to other records. I would do this structure:
    tblFunds: FundID(autonumber), FundName(Text)
    tblMandates: MandateID(autonumber), FundID(FK), MandateName, SECID...
    tblQReports: QReportsID(autonumber), MandateID(FK), ReportQuarter, ReportYear, or could do YearQuarter instead so would sort correctly like 2017Q1, 2017Q2, etc.

    So link on the ID fields for the system to keep track of the related records. Also this should let you have multiple reports with the same Quarter and Year that are tied to different Mandates by the MandateID field.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-30-2015, 07:53 AM
  2. Possible outline structure for this data is required
    By Resorte in forum Database Design
    Replies: 3
    Last Post: 08-03-2012, 12:21 PM
  3. Question on database structure - limiting linked records
    By andrewb in forum Database Design
    Replies: 2
    Last Post: 03-22-2012, 05:41 PM
  4. Question on how to structure a database
    By ExterminatorJeff in forum Database Design
    Replies: 3
    Last Post: 03-17-2012, 06:34 AM
  5. Database Structure | Best Practice Question
    By davidson12 in forum Forms
    Replies: 0
    Last Post: 11-05-2009, 03:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums