?
Solved

Access - Database Logic

Posted on 2007-10-04
9
Medium Priority
?
222 Views
Last Modified: 2013-11-29
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!
0
Comment
Question by:SharonInGeorgia
  • 6
  • 2
9 Comments
 
LVL 4

Expert Comment

by:seameadow
ID: 20018078
tblRegion:  REGION REGU (both fields in primary 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



0
 
LVL 4

Expert Comment

by:seameadow
ID: 20018087
Oops, 2nd ref. integrity line should be
Set up referential integrity with cascade update between tblAccts and tblMain(1 to many)
0
 
LVL 4

Expert Comment

by:seameadow
ID: 20018142
One more item: "set up referential integrity" means to establish a relationship using Access Tools > Relationships window
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 8

Expert Comment

by:Bradley Haynes
ID: 20018145
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.
0
 

Author Comment

by:SharonInGeorgia
ID: 20018525
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?
0
 
LVL 4

Accepted Solution

by:
seameadow earned 2000 total points
ID: 20020983
Well there's always an argument between those who prefer meaningless IDs to those (like myself) who believe that databases should use (whenever possible) a "natural" key -- i.e., the primary key is composed of actual data values.

The main reason that I prefer natural keys is that entity integrity ("no duplicates") is automatically enforced. Consider a simple accounts table with an arbitrary ID key:

ID    AccountNo  AccountTitle
10    12345        Paper and Supplies Expense
11    94959        Postage Expense
12    12345        Paper and Supplies Expense

This structure enforces entity integrity only on the meaningless ID key. We have to code additional checks if we really want to prevent duplicate accounts. On the other hand the natural key (AccountNo) automatically enforces entity integrity and prevents the entry of duplicate accounts

The only time I have used an arbitrary ID instead of an available natural key is when the natural key is huge: 4, 5 or more separate fields and there are related tables that have foreign keys. Even then only after carefully considering the trade-offs in code.

Further more, natural primary keys are more efficient: "seek rather than scan." The ID key creates a primary key index containing meaningless values so you have to execute an inefficient query to find the row containing the natural key of interest.
0
 
LVL 4

Expert Comment

by:seameadow
ID: 20021481
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.
0
 

Author Comment

by:SharonInGeorgia
ID: 20022216
seameadow:

Your explanation makes a lot more sense to me.  Thank You!
0
 
LVL 4

Expert Comment

by:seameadow
ID: 20022506
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.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question