Tables relationship - a bit challenging!

[atol]

Hello,

With all honesty, this forum is a Treasure! I see so much valuable suggestions here. Thanks to everyone.

I am a bit new to Access, but wholeheartedly, I enjoy doing Access-related projects. I guess I took a bigger bit this time, and got myself on the spot….I mean I am challenged. With this in mind, I would like to ask everyone here for some advice.

Basically, the project (as you will see on the attached file) is related to a financial database for some bond-related activities. For those who are not familiar with the financial terminology, a bond is a debt instrument (or fixed income instrument) paying interest on certain cycle, e.g. monthly, semi-annually, etc.

Here is the concept on my database.
An investor buys a bond, but the bond has tranches. That is, if you buy a specific bond you basically are buying the underlined tranches (no exception). But here is an important point, the tranches may payoff, or get split, so if you have two tranches today, tomorrow they can become three, or more (but the total value stays the same, just splitting).
Every tranche has an interest rate reset (for which I want to keep a history).
Then, I also want to keep a history of any interest, or principal payments on a tranche level.

I hope most of the relations I have are correct, but what I struggle with is the linkage among Bond, Tranche, and Transaction History.
I have already created a “one-to-many” between tblBond and tblTranche, and a “one-to-many” between tblTranche and tblRateReset (which I believe resolves one of the histories – the reset history on a tranche level).

I also have a junction table linking tblInvestor and tblBond (I used a composite PK here); but I also have an autonumber (that is NOT a primary key). Bacause of its nature I thought I might use it as a key for the transaction history. But how to incorporate the “Tranche” element here? I know here is something conceptually wrong here, or perhaps my big lack of knowledge. I know that a good database starts with a good design.

I am not sure if what I am explaining here make sense….but if not, I will answer questions, if someone is willing to assist me.

Thank you very much in advance!

Atol

 

[The_Doc_Man]

I'm going to give you some generalities.

1. Access never tells you anything you didn't tell it first. (Whether it is a fact or the result of a formula where you gave it the equation and inputs, that is always true.)

2. Access will never do for you in a computer any task that you could not do for yourself on paper first. (I.e. if you don't know the problem well enough to do it by hand, you will never know it well enough to program it.)

Therefore, you need to hold some design think-tank sessions. You must build a document (that some people call a "project bible") to tell you the answers to design questions. Not only goals but methods, paths, etc.

If this were a flat-out trivial program, your project bible would be trivial. I'm guessing this one won't be in that category. Search this forum for topics on how to design a database. I've authored my share. Pat Hartman, neileg, and MANY OTHERS have put in articles on that topic. Do some reading to figure out methodology of entity identification and relationship resolution. Somewhere along the way you will get some ideas on where to go from here.

The kicker of this whole suggestion is that we don't know what is the right answer. Your answer is right for you. Because we don't know the ins and outs of your business, we have no hope of giving you a "right" answer beyond some simple level.

 

[atol]

The Doc Man – I greatly appreciate your prompt response. What you are saying makes a lot of sense. Thank you again.

atol

 

[The_Doc_Man]

I will give you this viewpoint:

In order to design parent / child / grandchild / cousin / bastard nephew "family" databases, the trick is to figure out what depends on what. Or what can have many of something else.

Case in point, and you are on the right track here: Your tranches are "children" of "bonds" i.e. many possible tranches to one bond. Watch for that many/one concept, it is crucial.

Now, per your description, each tranch has a (possibly) unique maturity date. There is a rule (this time, not an "old programmer's rule") about dependency. If a tranch has a unique attribute (i.e. maturity date) then at least some aspect of that date belongs in the table of tranches. Not as a child of tranches because I'm talking about the BASE date, not the date of each subsequent maturity event. That maturity history entry WOULD be a child of tranches, with a date field for each maturity. That's a HISTORY table. But the tranch has a date that is unique and invariant for a single tranch, and that's when it came into existence (such that the maturity clock started for it.) So that goes in the tranch table.

If you read up on normalization, you would find that the rule I just described says that you put things in a table that depend completely - and ONLY - on the prime key of that table. That is why I suggest where this base date goes.

OK, what I've just done is a stream-of-unconsciousnees thought exercise to illustrate the thinking you have to do to lay out a DB. Figure out things that have that dependency on the same thing... they go together. Figure out things that have a little something extra - so they cannot go in the same table. The contrast points were the base date of the tranch and the maturity dates after its base date. These two look similar but they are very different.

I hope this belabored illustration helps you to orient your thinking towards the type of analysis you need to do. I'll make one more suggestion. Read up via Access Help, Wikipedia, and Google searches on "database normalization" - which is where you will run across that dependency rule - and then shoot for not less than 3rd normal form (or in db lingo, 3NF). If you can get your DB to 3NF, Access will hum. You might or might not have ANY data requiring 4NF or 5NF. (They are not unheard of, but usually don't spring up in just any database.) Be aware of them, but shoot for 3NF.

 

[atol]

Sorry it took me some time to absorb all this good info. And as you suggested I did find some tutorials on the topic. Reading on the topic is inevitable, especially in this specific case where the relations seem to be more complex with many tables and relationships. I have no problem reading and investing in myself.

Just one final question: How do I avoid duplicates in a table where the PK is not composite key? Let’s say, I have an investor A that invests in a Bond 1, and this combination would be with a primary key “autonumber 1” (let’s assume); but I do not want the same “investor A/bond 1” combination to appear again under “autonumber 2”. I know if I use the composite key (of the two – BondID and InvestorID) I would be able to resolve any duplicates. And the reason why I do not want to use a composite key is because I have to create a child table to keep a history of all trades for "Investor/Bond"..…any help on this would be very very helpful!

And The Doc Man – just want to thank you once again for your thorough explanations and guidance!

Regards,
atol

 

[The_Doc_Man]

How do I avoid duplicates in a table where the PK is not composite key?

I'm more than just a little confused. No matter WHAT your key looks like, if you declared it to be the prime key, then by Access hard-coded rule, it MUST be "No Dups Allowed."

So you are perhaps naming something wrongly. An autonumber is an example of what we call a meaningless key. (It has no meaning except as a record identifier.) I'm going to have trouble explaining this one, but here goes...

Unless you need a record identifier separately from the investor and bond info, you don't need an autonumber. If the investor info and bond info in combination are unique, then having a SECOND unique number is redundant. (OK, sometimes there are reasons for it, see below in later discussion.)

I imagine something like this...

Investors
InvestorID - PK of this table, autonumber
Investor name info (FName, MInit, LName, etc) - anything to do with the person of the investor.

Bonds
BondID - PK of this table, autonumber (unless the bond has some sort of FDIC number that is preferable as PK - and it is OK if that is the case)
Info about the bond. (You might call this something other than I do - I'm not into bonds... this is, like -... Liberty Mutual Class A bonds, or Franklin Class Z bonds or ... perhaps you would call it bond family?)

Holdings
InvestorID - Foreign Key (FK) to investor table to identify the holder
BondID - FK to bond table to identify what is held.
(Perhaps number of these bonds held by this investor?) But not required...

The Holdings table is an example of what we call a JUNCTION table. It shows where two independent entities touch. You have entries where an investor holds a bond. You have no entry for any combinations not held. And... to keep them from being duplicated, you make the combination of InvestorID and BondID a compound PK - which by definition MUST be unique. Here is where I don't see the need for an autonumber. You have a perfectly good PK already - the compound key of InvestorID and BondID.

Now the question is whether other info is needed.

If you need to record serial numbers of numbered bond issuances, the junction table can either present the PK as the compound of the two numbers, OR you can add an autonumber to the junction table and use IT as the PK for parent/child purposes. Either one works. For the case of two ID numbers, it is not such a big deal to have two LONGS as the PK - but if you are happier with having an autonumber on the junction table to do linkages, it is OK. This would be one of the exceptions to the rule that says you only need (and therefore only want) one true PK. This exception is a preferential exception as opposed to some canonical rule. In other words, you would add the autonumber to help you visualize the linkage. Not because the laws of database layout say you need it. (In fact, they would say you DON'T need it.)

In that autonumber case, though, you are using the autonumber as the PK - and in fact to have a defined relationship, it MUST be the PK. So how do you keep duplicates from occurring? You can still declare an INDEX on the junction that is comprised of the two FK ID fields and make the index a "No Dups" case... that's legal. An index other than the PK is allowed. You can have up to 10 indexes. Avoid having too many for peformance reasons. But having one to enforce the uniqueness rule is OK.

Let me make a suggestion. When dealing with what appears to be a lot of layers - and I'm seeing several here - it is a GOOD thing to draw pictures to help you visualize the relationships. I often suggest taking some time in front of a white board with dry-erase markers and sticky-note pads to simulate the operation of the database you want to build. With the advent of hi-res digital cameras, you can draw your diagram and then, when you have something to preserve, take a quick snap of it.

The "Old programmer's Rules" come into play here.

1. Access never tells you anything you didn't tell it first.

Practical meaning: If you want to be able to remember facts X, Y, and Z... you had better STORE facts X, Y, and Z. Because Access never tells you anything you didn't store or tell Access how to compute what you wanted. So remember to think ahead of time about the questions you are going to ask. That will guide what you store.

2. If you can't do it on paper, you can't do it in Access.

Practical meaning: If you don't understand your problem well enough to simulate it on paper, you don't understand it well enough to program it in Access. Because programming a solution to a problem absolutely requires you to understand the problem. You program solutions because the computer has a better memory than you do - or it does things faster than you do. (Just remember, if you don't understand the problem, Access can make MISTAKES faster than you do...)

 

[atol]

Let me apologize first if I did not explain things correctly. But regardless the fact, you absolutely got what I meant and provided me with a valuable response.
And in a recap, I have to do some “home work” on the topic – reading and practicing, and will re-evaluate the project from scratch on a white board (per your good advice). Bottom line, as I understand, the most important thing for a database is to get the relationships (in 3NF) done correctly, or else, I just could mess up the project in a long run. Thanks again! I am sure I will have more questions down the line, but thanks to you and the other great contributors here, will get the things done.
Regards.
Atol

 

[The_Doc_Man]

Bottom line, as I understand, the most important thing for a database is to get the relationships (in 3NF) done correctly, or else, I just could mess up the project in a long run.

And it seems YOU also got exactly what I was saying, too.

Good luck. Nobody every comes out of any important design process feeling that it was a piece of cake. Everyone feels like they've been fighting one of the ancient mythological beasties called a Hydra - too many heads with too many sharp teeth to reach out and bite you somewhere tender. But when you are done, you have a good feeling of accomplishment. And if done RIGHT, you also have a valuable tool and some of the best experience there is - from the school of hard knocks.

Why do we subject ourselves to such agony? (And make no mistake about it, there are times when it is sheerest agony!) Because the goal is good. Because we love a challenge. And because we get shuffled to a make-work job if we don't step up to the plate now and then. (Yeah, I know... practicality creeps in again.)

 

[atol]

Well said !..... and all TRUE. .....Thanks again, The Doc Man.... for the wise thoughts and for the encouragement.