Avatar of Davisro
Davisro
Flag for United States of America

asked on 

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!!
Microsoft AccessMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Fabrice Lambert

8/22/2022 - Mon