Add Foreign Key to Table Directly or use Interim Table?


I routinely build Access Databases as desktop processes to analyze data. I'm looking to understand the best way to handle the following situation:
Every query I run joins tables on Cost Center. My Cost Centers are 10 Char Alphanumeric. I've already learned from previous questions that I should create a Cost Center master table and use the Autonumber (or other short numeric) field as a CostCenterID for use in joins because its more efficient.
So now let’s say I have a
1.      Cost Center Table with 2000 cost centers and a CostCenterID (Autonumber) field that I’ll use as my primary key, and a
2.      Transaction Table with 1mil records that I want to join on the Cost Center table, but the CostCenterID does not yet exist in the Transaction Table.

How is performance effected if I:
1. Use an Update Query to add the CostCenterID from the Master Cost Center table as a foreign key to any Transaction Tables, so Cost Center and Transaction tables can be joined directly, or
Use Interim Query/Tables
2. Use a Select query to create an interim relationship.
SELECT tbl_Trans.CostCenter (alphanumeric), tbl_CostCenter.CostCenterID (Autonumber)
FROM tbl_Trans INNER JOIN tbl_CostCenter ON tbl_Trans.CostCenter = tbl_CostCenter.CostCenter

Open in new window

3.      Create an interim table with a make table query
SELECT tbl_Trans.CostCenter (alphanumeric), tbl_CostCenter.CostCenterID (Autonumber) INTO tbl_Bridge
FROM tbl_Trans INNER JOIN tbl_CostCenter ON tbl_Trans.CostCenter = tbl_CostCenter.CostCenter

Open in new window

One opinion I was given is that option 1 is best. I just want to make sure as I will be basing many processes going forward off of this method

DavisroBudget AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
The Primary key / Foreign keys model is best, period.

But, on existing databases, refactoring the model (add Primary keys, set relationships, compute Foreign keys) have a cost.
Up to you to decide if the time spent refactoring is worth it or not.
DavisroBudget AnalystAuthor Commented:
I agree. I've been asking myself if its worth it to add the manual steps to add foreign keys to my transaction tables. For databases that I use myself, its easy to do. If I'm developing processes for other people to use, then I have to program buttons to make these updates and such.
I'm curious what the quantifiable difference is in terms of query performance.
Scott PletcherSenior DBACommented:
Step #3 isn't necessary, because it would be redundant -- the  tbl_CostCenter table already contains both the CostCenterID and the CostCenter (Code).

I don't think you'll really gain any performance here by adding the CostCenterID to the trans table.  Since the trans table won't have CostCenterID as the first key in a primary key, the lookup won't be that much different just using CostCenter (Code) rather than CostCenterID.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DavisroBudget AnalystAuthor Commented:
After reading a bit, it seems that I've stumbled upon the perennial Surrogate vs. natural/business keys question.

I suppose the consensus is
1. People have been debating this a long time
2. Surrogate keys of Integer type may have some performance improvement over string types
3. If you feel the marginal improvement is worth adding surrogate foreign keys into your data then have at it

So I guess my answer will be in trying both and testing performance. Thx
Fabrice LambertConsultingCommented:
The onlly sure thing is that numeric comparition is faster than string comparition.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.