Solved

Access DB Relationships

Posted on 2014-01-21
3
329 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 38

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

719 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