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
Solved

Access DB Relationships

Posted on 2014-01-21
3
323 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 36

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

839 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