We help IT Professionals succeed at work.

Add Foreign Key to Table Directly or use Interim Table?

Experts,

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

Thanks!!
Comment
Watch Question

Fabrice LambertConsulting
Distinguished Expert 2017
Commented:
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 Analyst

Author

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.
Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
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.
DavisroBudget Analyst

Author

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 LambertConsulting
Distinguished Expert 2017

Commented:
The onlly sure thing is that numeric comparition is faster than string comparition.