Main Table design, storage of FKs

[Elvaar]

I have a general database design question, normally i find my answers with searching - but in this case I really wanted to lay out my specifics.

I have a database that I am putting into development. It has 25 tables, a bunch of queries and a bunch of forms.

It is a database that has major purposes, store patient information and visit details in an electronic chart format. It also generates invoices based the patient's "Plan" and the visit type.

Here is my situation:

The Visits table is my "Main Table" which collects data through a form entry to generate a invoicable claim.

The tables I am concerned with are:
Services
Locations
Plan

All 3 of these tables have a FK field stored in the Visits table.
Example:
ServicesID
LocationsID
PlanID


FKs relate back to their respective table and those records can be referenced through queries to generate invoices and whatnot.


My concern:
Because the value stored in the Visits table is just a FK autonumber, if the foriegn table ever gets changed or updated that number is no longer accurate or meaningful.

Is there something should be doing differently to ensure the integrity of the data ?

Thanks!
Bill

 

[gemma-the-husky]

the fk number (NOT autonumber, just a number, to be pedantic) represents the primary key (which is AUTONUMBER) in a master table.

the reason for using autonumber keys is precisely that they
a) provide a unique reference and
b) wont ever need to change

as long as your dbs manages the fk properly this should never be an issue

-----------

this idea is the basis of all relational databases (or even any multitable file based system).

 

[The_Doc_Man]

Let's look at it another way. Do you believe that your tables will ever have to be rebuilt in such a way as to not preserve their data correctly? If so, please advise so we can evaluate that and in return advise you of the odds of that event.

 

[Elvaar]

Honestly Doc, I don't know. I sincerely hope not. This isn't my first Access project, but this one has taken on a larger scope than I could have ever predicted.

This started out as a side project that has grown into a pretty sophisticated database. Now that we are looking at having somewhere in the neighborhood of 500+ users (There are 18 separate backends, for 18 different divisions in my company - it was necessary to segregate the data), I am starting to grow somewhat paranoid that I for sure designed everything correctly.

When I look at my main table and see a numeric value listed instead of an actual name (because it is simply a stored FK), I started to wonder if this could ever be an issue.

 

[The_Doc_Man]

When you use a surrogate key that is some form of AUTONUMBER, usually you do not need to change out anything. However, relational integrity becomes an issue if you keep the same patient but archive child records AND a junction table is used. Any other time, Access handles key uniqueness issues for you.

If you have to restructure everything, that is when you start tearing out hair in large clumps. Particularly if your ID methodology has to change. Granted, we cannot tell the future. We know that health record regulations can change in a heartbeat. But as long as that surrogate key remains valid, perhaps you shouldn't worry too much about any restructuring of child tables. I don't know that I have anything to add to your solution, but perhaps I can at least calm some of your concerns.