Solved

Access DB Relationships

Posted on 2014-01-21
3
326 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 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 37

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

739 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