A second opinion on PK field

[Dwight]

I have a database that stores information on government bonds. These need to be classified into 3 categories based on time to maturity: 1) Short Term 2) Medium Term and 3) Long Term.

Since a bond’s classification can change as its maturity nears I set up a simple IIF based formula that compares the bond’s maturity to the current date and then assigns it a value i.e. 1, 2, or 3. I plan to link this with a table called AssetClassID which will have the values in one column and their text descriptions in another column.

My question involves how to structure the table correctly. Specifically whether I should create a third column that holds an autonumber PK field. This column would be meaningless other than making each record unique. Or I should make the value fields (1, 2, or 3) the PK and leave it at that. I think I should have the additional PK field.

Structure 1
AssetClassID (PK, autonumber)
AssetClassNumber (FK to query, number)
AssetClassDescription (text)

Or

Structure 2
AssetClassNumber(PK and FK to query, number)
AssetClassDescription (text)

My dilemma is because I know every table should have an autonumber PK field. And even though it may not be logical I feel like I want control over the AssetClassNumber field since my IIF statement is based on it. This tells me I should use Structure 1. But at the same time it seems unnecessary to have the extra field.

I plan on doing this a couple of places in the database so I guess I am looking for confirmation that my planned method of attack makes sense.

For example, corporate bonds have a range of 9 lettered ratings and I want to have an IIF statement to classify them as Investment grade or Speculative grade. So I thought I would give each letter a number (1-9) and then the IIF statement could say if it was < 5 Investment Grade and >= 5 Speculative Grade. Once again would I need the extra PK field in my table? And is it okay to base the IIF statements of the AssetClassNumber column where I control the numbers?

It is always nice to have another opinion.

Dwight

 

[namliam]

I have a database that stores information on government bonds. These need to be classified into 3 categories based on time to maturity: 1) Short Term 2) Medium Term and 3) Long Term.

Seeing as the categories are based upon the time to maturity=> Why Hard code it? I can imagine that short term would be less than 30 days
med term 31 to 90 days
long term more than 90 days

if you make a table
from=To===Description
0===30===Short
31==90===Med
91==1000=long

you can the query them together using where MaturityDate-Date() between from and to

Easy, flexible, no PK and maintainable...

Only one catch:
0===30
20==60
Will get you into problems so after inputing check for that kind of thing

Hope this helps

Regards

 

[Dwight]

Right, that's pretty much what I am doing. The query contains a forumula based on the maturity date and the current date and is therefor dynamic. It just assigns a value (1, 2, 3) based on the time left as you mentioned.

By linking the query to a table I think it makes it more powerful. For example, I can change Medium term to Intermediate term in my table and then it updates everywhere or it allows me to sort the asset classes easily.

Also I can have more asset classes in the table. Investment grade could be 4 and Speculative Grade could be 5 etc. Then I can have another formula that looks at the bonds rating (which can change over time and are in a separate table) and assign it to an asset class.

Am I crazy?

Dwight

 

[namliam]

Are you crazy ? Yes but thats not the point here

For the bonds themselves i would go that way but my way as i described above

Maybe making it dependand upon another field which tells you what it is.

Bond 90 days is "short"
Options 90 days is "long"

get what i am going at?

Regards

 

[Dwight]

No I don't really understand you post and it gets further away from the topic I am trying to address. But I appreciate the input.

I think having a table/query structure is very powerful and flexible. I just wanted to know if I should use a separate PK field within the table. I think I should.

I guess I am looking for a third opinion now.

 

[jeremie_ingram]

I would include the Pkey like in example 1 in your initial post. The only time I would even give consideration to NOT having a Pkey for any table would be if there is size constraints that I need to meet. Otherwise, every table gets one, even if I dont end up useing it. It is just good pratice.

 

[Dwight]

That's what I'll do then. Thanks for the advice.