SharonInGeorgia
asked on
Access - Database Logic
I need some confirmation on my database logic.
tblREGION - The ONE table. Includes 2 fields to create a unique key: REGION REGU
tblACCTS - AccountCode. An Account Code can be assigned to many REGION/REGU Combinations and a REGION/REGU can have many Accounts. I'm not sure if this should be a lookup or a relationship table
tblMAIN - Includes the monthly Financial data based on the REGION/REGU/AccountCode . These three fields togeher consitute a unique record.
tblMAIN:
Region (+ related fields) Regu (+ related fields) Acct (+ related fields) Jan$ Feb$ Mar$
There are no relationships between the tblACCTS and tblREGU until they are joined together in the tblMAIN. Up to now, I updated the tblMAIN fields "REGION", "REGU", "ACCT" with the tblRegion and tblAccts values and I have been working off of the tblMAIN for everthing.
Sometimes, however, some fields in the tblREGU or the tblACCTS may have value changes. Those changes need to reflect in the tblMAIN. So, I now have to rethink the database logic and determine how to set up the relationships between tblREGION, tblACCTs and tblMAIN. Thank you in advance for your suggestions!
tblREGION - The ONE table. Includes 2 fields to create a unique key: REGION REGU
tblACCTS - AccountCode. An Account Code can be assigned to many REGION/REGU Combinations and a REGION/REGU can have many Accounts. I'm not sure if this should be a lookup or a relationship table
tblMAIN - Includes the monthly Financial data based on the REGION/REGU/AccountCode . These three fields togeher consitute a unique record.
tblMAIN:
Region (+ related fields) Regu (+ related fields) Acct (+ related fields) Jan$ Feb$ Mar$
There are no relationships between the tblACCTS and tblREGU until they are joined together in the tblMAIN. Up to now, I updated the tblMAIN fields "REGION", "REGU", "ACCT" with the tblRegion and tblAccts values and I have been working off of the tblMAIN for everthing.
Sometimes, however, some fields in the tblREGU or the tblACCTS may have value changes. Those changes need to reflect in the tblMAIN. So, I now have to rethink the database logic and determine how to set up the relationships between tblREGION, tblACCTs and tblMAIN. Thank you in advance for your suggestions!
Oops, 2nd ref. integrity line should be
Set up referential integrity with cascade update between tblAccts and tblMain(1 to many)
Set up referential integrity with cascade update between tblAccts and tblMain(1 to many)
One more item: "set up referential integrity" means to establish a relationship using Access Tools > Relationships window
seameadow: is on track . . . keep in mind as you set up indexing to facilitate query speed; clustered vs non-clustered indexes. Also, when you write your queries think in terms of seek rather than scan. Having numerical search criteria instantiates seeks, of course when indexes are present.
ASKER
From a purist standpoint, It is often suggested that ID numbers should be used instead of the actual data between tables. What you are telling me makes a lot more sense. What is the difference between the two philosophies? When do you number the data and when do you not number it?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Forgot to mention another good reason for using natural primary keys: cascade update. If for example a region name changes, then if cascade update is enabled, the DBMS will automatically change all foreign keys to match the new value of the primary key.
ASKER
seameadow:
Your explanation makes a lot more sense to me. Thank You!
Your explanation makes a lot more sense to me. Thank You!
Sharon, glad to help and good luck with your project!
Actually my last comment was slightly erroneous because with ID keys you should not store natural keys (e.g, region) in the related table at all -- that is a second normal form error. But on the other hand, with natural keys, you don't need to do a join with the primary key table if all you need from the primary key table is the value of the natural key.
Actually my last comment was slightly erroneous because with ID keys you should not store natural keys (e.g, region) in the related table at all -- that is a second normal form error. But on the other hand, with natural keys, you don't need to do a join with the primary key table if all you need from the primary key table is the value of the natural key.
tblAccts: Acct (single field in PK) (lookup table)
tblMain: REGION REGU Acct (all three fields in the primary key)
Set up referential integrity with cascade update between tblRegion and tblMain (1 to many)
Set up referential integrity with cascade update between tblRegion and tblAccts(1 to many)
tblMain itself represents the M:N relationship between Region and Accounts
With cascade update value changes in either will be reflected in tblMain