Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access DB Relationships

Posted on 2014-01-21
3
Medium Priority
?
336 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 58
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 39

Accepted Solution

by:
PatHartman earned 1500 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

597 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