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

    Can anyone review my database structure before I move on to creating more in-depth forms/reports

    Good morning/afternoon/evening to you all,



    First: I'm relatively new to Microsoft Access. I've done a fair bit of research (read through every link in the post by Micro found here) and I have some database management experience from when I was in University. The first thing I did was to write in plain English the requirements of the database. Here is what I came up with:

    The company requires a fund manager tracking database. They want to track of information about funds, their mandates (which are essentially 'portfolios of investments'), the companies that the individual mandate has invested in, transactions for each mandate from their internal accounting system, and information contained on quarterly manager reports. For generating reports, the company also needs to keep track of daily foreign exchange rates and benchmark returns (however they do not require relationships to the data (at least from my understanding, as the numbers just need to be referenced in currency exchange calculations when reports are generated).

    · Required fund information: fund name
    · Required mandate information: mandate name, SECID, type, geographic focus, vintage year, domestic currency, total commitment, and our ownership percent within the mandate
    · Required investment information: company names, mandate ownership percent, sector, geography, date acquired, equity owned, realized proceeds, current fair value of the company, and date last updated
    · Required transaction information: transaction date, transaction amount, and a description of the transaction
    · Required quarterly report information: quarter and year of the report, date report was received, current value of the fund, cumulative commitments, unfunded commitment, quarterly and cumulative capital calls, and quarterly and cumulative distributions
    · Required foreign exchange rate information: date, USD to CAD, EUR to CAD, GBP to CAD, etc.
    · Required benchmark returns: date, S&P500, MSCIWorld, MSCIEuro, MSCIEAFE, etc.

    The relationships are as follows:

    · Each fund can have any number of mandates, but each mandate belongs to only one fund
    · Each mandate will have a mandate report each quarter, and will have at any given time 0-50 investments.

    I’ve attached a photo of the database structure I have come up with. Please take a look and see if there is any glaring issues or improvements you think I could make.

    Thank you in advance!
    Attached Thumbnails Attached Thumbnails Relationships.PNG  

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    (I'm a novice too) One thing that I see is the ReportAttachmentN fields. Would it be better to put those in a separate table? (One MandateReport has many ReportAttachments)

  3. #3
    ChaseC is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    18
    Hmmm... that does seem to make more sense. I will make that change, thanks!

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    First thing I don't get - where is the master table for the funds? Where does fund manager come in all of a sudden? That sounds like a person, altho I may have got it wrong. You need a table that contains fund name and fund ID, maybe start date and end date - but only information that does not change. A fund manager seems to me to be a changeable commodity.

    Never carry report information on a table, that data will be created when the report is run. All the data must be dispersed among the correct tables, and NEVER store calculated values. You may have a snapshot table which stores what a report looks like, but only it you absolutely need it for future reference. Queries with a date range will work just as well.

    Not sure about the transactions, don't you need to carry which investment the transaction relates to?

  5. #5
    ChaseC is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    18
    Quote Originally Posted by aytee111 View Post
    First thing I don't get - where is the master table for the funds? Where does fund manager come in all of a sudden? That sounds like a person, altho I may have got it wrong?
    The easiest way to explain it is the Fund Manager oversees their mandates. So, for example, say there was a private equity investing company Chase & Partners. They create a mandate (or portfolio) and begin calling around to raise capital to bankroll their portfolio. They could have any number of mandates ongoing at a time. We (the company I work for) are the suppliers of that capital. We care less about the actual fund manager and more about the going-ons of each specific mandate that the fund manager oversees.

    Quote Originally Posted by aytee111 View Post
    Never carry report information on a table, that data will be created when the report is run. All the data must be dispersed among the correct tables, and NEVER store calculated values. You may have a snapshot table which stores what a report looks like, but only it you absolutely need it for future reference. Queries with a date range will work just as well.
    I'm not sure if you're referencing something specific you see but none of the attributes I have are calculated, they are all pulled directly from manager reports or Bloomberg terminals.


    Quote Originally Posted by aytee111 View Post
    Not sure about the transactions, don't you need to carry which investment the transaction relates to?
    We don't directly invest into any company, we invest into a fund manager that then invests in any company. We keep track of their investments for compliance and due diligence purposes, but we're not overly interested in each individual move of the company. We're really interested in the overall mandate movements, so one example of a few transactions would be:

    Date Amount Description
    August 14, 2017 -$1,000,000 Capital Call for Management Fees
    August 14, 2017 $450,000 Capital Distribution for Realized Proceeds
    August 14, 2017 -$500,000 Capital Call to Purchase 1.5% of Fictional Company

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    My point about the fund manager was that if it is a person then it is a temporary/changeable value. If it changes (a different manager is appointed to that fund) then you will lose all history related to that fund and will have to begin again from scratch.

    So all the reporting data except for the mandate ID comes from external tables?

    Still doesn't make sense to me that they can have a transaction that does not apply to any of the investments on your investments table, but you know your requirements.

  7. #7
    ChaseC is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    18
    Quote Originally Posted by aytee111 View Post
    My point about the fund manager was that if it is a person then it is a temporary/changeable value. If it changes (a different manager is appointed to that fund) then you will lose all history related to that fund and will have to begin again from scratch.

    So all the reporting data except for the mandate ID comes from external tables?

    Still doesn't make sense to me that they can have a transaction that does not apply to any of the investments on your investments table, but you know your requirements.
    Ah I see your point about the fund. No, a fund will always be managed by the same company for it's entirety. Transactions can apply to each investment (like the third transaction example above), however we are not interested in keeping track of transactions as they relate to each individual investment, only the overall transactions of the entire mandate.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Back to the "ReportAttachmentN fields" for a minute.

    I would also suggest another table - having multiple fields for one item is not a normalized structure.
    Also, WHAT will be in the attachment field? If it is something like a PDF file, using attachment fields are a bad idea because the Access file will reach the max size of 2 GB very fast.

    I would suggest having a folder for the PDF file (for example) and using a hyperlink field to store the path to the file(s).

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

Similar Threads

  1. Replies: 2
    Last Post: 12-16-2016, 12:55 AM
  2. Replies: 8
    Last Post: 08-12-2012, 06:05 AM
  3. Replies: 1
    Last Post: 07-15-2011, 11:00 AM
  4. BD Structure (review)
    By Bryan021 in forum Database Design
    Replies: 0
    Last Post: 05-26-2011, 11:39 AM
  5. Creating Watermark in Access Reports / Forms
    By Alex Motilal in forum Reports
    Replies: 0
    Last Post: 11-21-2008, 07:11 AM

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