Solved

Access DB Relationships

Posted on 2014-01-21
3
318 Views
Last Modified: 2014-01-21
Hello-

I am working on designing a database to map portfolio holdings data to a particular Bond Index based off some characteristics of the holdings. I have attached my initial relationship diagram but I am getting stuck on trying to map a particular holding based on its maturity & rating bucket number due to the Index table having a possible 1 or more possible rating/maturity bucket number and the holdings only having 1 of each. I am guessing I may need another table with a one-to-many relationship to show all the possible combinations of bucket numbers for each member in the the Index table but wanted to get some ideas from the Experts.

Here is some basic background on my data and flow process
There is exists a basic one-to-many relationship between a Portfolio and Indices and Indices can belong to one or more portfolios
Each holding has number assigned for each of its rating, maturity and asset class
Based off the characterics of the holdings in the portfolio they are assigned to an Index which are typically based off the maturity/rating bucket numbers as well as the Asset Class Number but some Indices can be more refined

I hope this is helpful in explaining the business logic and data characteristics. If not please let me know I will try to explain in more detail.

Thanks!
Relationships.pdf
0
Comment
Question by:Financeguy2014
3 Comments
 
LVL 57
ID: 39797329
<<I am guessing I may need another table with a one-to-many relationship to show all the possible combinations of bucket numbers for each member in the the Index table but wanted to get some ideas from the Experts.>>

  That sounds like it's correct, but it's hard to say without understanding more of this.

  One question; can the holding point to a maturity bucket, Rating, and asset class that belong to different indicies?   I believe the answer to that is no.

 In that case, you'd have the indicies table, a child table which represents every possible combination of Maturity Bucket, Rating, and Asset Class for that index and your holdings table would then point to this table in a one to many.

Jim.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39797344
Many-to-many relationships require an intermediate table which allows you to implement two 1-m relationships.  This table is frequently called a junction table or relationships table.

I couldn't see all the columns of the historic table so I can't tell what you are joining on and I know it doesn't look like it but you actually have a repeating group in the historic holdings table.  All those factors should be broken out into a separate table where you have FactorType (OAS, CVSTY, etc) and the attributes for a "factor"  This will give the model some flexibility should some new calculation be introduced.

I would also get rid of the spaces (and special characters) in the column names.  Proper naming standards use only the letters a-z (upper and lower), the numbers 0-9, and the underscore.
0
 

Author Closing Comment

by:Financeguy2014
ID: 39797380
Great, thanks for the help Jim/Pat. I appreciate the feedback and will work to implement your comments.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now