I use a somewhat different philosophy than James when it comes to
history. I tend to use an effective date structure and look up records
based on the latest, i.e. current, date. Either system works, but I
prefer to maintain a single table. With the indexing that R:Base now
has, retrieval time is not significant. A single table view allows
lookups based on only the most current date with all other records still
available for review when needed. If the person is no longer with the
organization, an inactive switch in the master record will allow you to
keep them off selection lists for current needs.
In a Customers table, for example, I would keep only the basic minimum
records and include a column CustActive TEXT (1) DEFAULT 'T'. Lookups
for new shipments and invoices would not see any customer where the
CustActive = 'F'.
Pick and choose. We all have our own ideas on how to handle this sort of
thing, and only you know which is the most comfortable method for you to
use.
Albert
On 22/02/2012 8:52 AM, James Bentley wrote:
Bill,
For you address lookup needs checkout products from
http://www.melissadata.com/.
Bill I am not privy to you current database structure but here are a
few suggestions to consider.
It seem there will be many segments to you Patients, vendors, Schedule
master etc.
For Patients the key should be an unique patient identifier. This will
be the link to all subordinate tables
There would be one PatientMaster with such items as PaitentId,
PaitentFirstName, PaitentLastName etc.
There could be subordinate tables
PaitentCurAddress
PaitentInsurance
PaitentCurPhone
PaitentPrvAddress
PaitentTreatmentHistory
various other patient info tables.
All linked by the PaitentId
Since you might be treating multiple patients with the same address
and might want to create a freestanding
address file with a structure some what like this:
PatientMaster -- PaitientId
PaitentJoinAddress-- PaitentId,AddressId
Addressmaster -- AddressId
I believe at some point maintaining such a structure could become
unwieldy.
Patients Move, Some Patients cease being a patient, post office
changes mailing information etc
You may need to have address history for various regulatory reasons
The number of information records grow over time as you collect say
address changes or patient visit records.
Most of the time you query and report current information. With
inquiry into historical records.
To speed processing and simplify query statements I recommend keeping
current information segregated from historical
information coupled with developing a stored procedure to initiate
transfer between current and history tables.
In the early versions for the Society database I did not maintain an
address history table.
Also, if a member became "lost" or "died" certain basic information
was dropped from the database
and certain details sored in non-database text files.When we started
recording contribution and dues
information we juped through hoops to keed a linke betwee the
contribution record and the contributor
information. This would be akin to you patient basic information and
transaction information such as
patent visits, diagnosis information, billing information. Now we flag
those events and for example
transfer address information to an address history table. Other
information such as contributions remain in
a single table.
Jim Bentley
American Celiac Society
[email protected]
tel: 1-504-737-3293
------------------------------------------------------------------------
*From:* William Stacy <[email protected]>
*To:* RBASE-L Mailing List <[email protected]>
*Sent:* Wednesday, February 22, 2012 8:48 AM
*Subject:* [RBASE-L] - Re: Too relational?
I'm leaning toward a construct where there is a permanent address
table, as usual keyed by an autonum column that should rarely need
changing at all (as in an eminent domain condemnation or some
such), a second table which contains that autonum column along
with a person/company link and two date columns, dtin and dtout so
that historical records are preserved. The same thing should work
well with telephone numbers which of course should also rarely
need editing themselves; only the linking tables need frequent
changes. As for spiderwebs and terabytes, I'm pretty sure that
going more and more relational actually ends up saving bandwidth
over flat files. And there is something beautiful about
spiderwebs. Finally, I'd love to put every address within 20
miles in my database so people could actually pick their (properly
formatted) addresses by entering a few numbers and a couple of
letters without having to type and retype it with all the errors,
etc. Maybe the post office has it for downloading, or mapquest???....
On Wed, Feb 22, 2012 at 5:33 AM, <[email protected]
<mailto:[email protected]>> wrote:
Further to what Albert suggested: One app that I wrote relied
heavily
on people spending time at 2 different homes. The address
table was
separate, with a text column (free-form) that indicated the
"type" of
address it was (winter, summer, school, mother).
You'd go into their record and select a "current" address and
enter
an "until date" if you knew one. That record would be marked
with an "X"
for being current. Obviously when you enter a new person the
first
address is marked with an "X".
At the first startup of the day, it would look at "until date"
<= today and
send a list to the printer so the users would know they should
either
select another address to be current or change/erase the date.
Karen
In a message dated 2/21/2012 10:52:01 PM Central Standard
Time, [email protected] <mailto:[email protected]>
writes:
Yep, I would have a Persons master table, a PersonStatus
table and a
PersonAddress table containing address(es) with a switch to
indicate
Active or Inactive status, and follow that along. If a person
lives in
Hawaii for the winter and in British Columbia in the summer,
both
addresses would be in the PersonAddresses table with one only
allowed to
be active at any given time. Saves a lot of keying. You could
go as far
as PersonPhones if you really need to, adding a phonetype
column to
indicate cell, land, satellite, fax ...
Albert
--
William Stacy, O.D.
Please visit my website by clicking on :
http://www.folsomeye.net
