Using another source's codes?

[Dwight]

I need some best practice advice.

I have a table called SectorID. It uses Standard and Poor’s classifications. They have their own system as follows:


Sector Code Sector Name
10 (Primary Key) Energy
15 Materials
20 Industrials
25 Consumer Discretionary
30 Consumer Staples
35 Health Care
40 Financials
45 Information Technology
50 Telecom Services
55 Utilities



I download an Excel spreadsheet that has individual securities and their sector codes. I set up a second table with a SecurityID field (PK, autonumber) and the SectorCode as the foreign key. It works well but I am wondering if I should not have an autonumber field in the first table and use it as a PK rather than their codes which could change.

But then I would not be linking a PK to a foreign key. I would be linking a field (still unique) to the foreign key.

Maybe this is all 6 ½ dozen but I would still like the forum’s trusted opinion.

Thanks

 

[Dwight]

Thanks Pat for the characteristically thorough response. Yes, a S&P press release would announce any changes. Actually, there are three levels below Sector (Industry Group, Industry Name, and Sub Industry Name) with successively narrower definitions. Each is more likely than the previous to be changed as the economy evolves. Perhaps “Buggy Whips” was once a Sub Industry name but it has been supplanted by “Tires and Rubber” for better or worse.

I would like to import the spreadsheet and append it to an existing table but I have not set this up yet. I will follow your advice and add the autonumber. Before I do I would like your (or anyone’s) advice on a related matter that I have been going back and forth on.

The database holds basic information on multiple security types (corporate bonds, government bonds, equities, funds, and derivatives). At first I had one big table for everything called SecurityDescription. But because each security has unique characteristics and many fields were left blank I created a more normalized structure with a separate table for each type of security. I thought this would be more flexible and efficient as the database evolves. New security types (e.g. commodities) may need to be added or additional fields on current types may be required. This would turn it into a very big and messy table.

I created another table called SecurityID to allow me to relate all the securities as follows:

tblSecurityID
Security ID (PK, autonumber)
Cusip (text)
SecurityName (text)

Then each individual security table looks like:
tblEquityID
EquityID (PK,autonumber)
SecurityID (FK)
Field1
Field2
Etc…

I want three things:

1) One list of tickers that I can use as the row source for combo boxes for users to make selections from. This is why the Cusip field is in the SecurityID table.

2) To be able to append records via an outside spreadsheet to the many side of my security tables. The problem is that I won’t have a corresponding record on the one side.

3) The user to be able to update a security’s info within Access itself. I have set up a Form/subform structure that accomplishes this. The SecurityID is the Form and then each security type is a subform on a separate tab.

Is denormilizing the answer? Or am I missing something basic? Nothing is sacred. I just want the best solution.

Any advice is appreciated.

Dwight

 

[Dwight]

Very clever. I think I set it up the way I did out of habit but it just didn't feel right. Thanks, as always, for the steering.