collection DB design question

[Melissa C. Winans]

At 08:49 PM 12/12/96 -0400, you wrote:
>For the sake of efficient design of cataloging databases, consider the
>following situation. Dr. X, who is at institution Y, borrows some specimens
>from our museum, and we dutifully record her name and address (in the
>computer, advanced beings that we are). Subsequently she moves to Z, and we
>not surprisingly update our address files accordingly. While this makes
>perfect sense, I was wondering if anyone knows of a situation in which we
>would NOT want to update a person's address, i.e., for historical reasons.
>For example, if Dr. X donated a collection of specimens while at Y, should
>the _accession_ record connected to that gift be updated when she moves to
>Z, or remain a static document? This question may sound trivial, but it
>affects the way our cataloging database will be designed. I think it would
>be easier if we didn't have to juggle multiple addresses for each person in
>the database itself. What about "retiring" previous addresses, with dates,
>to a separate file whenever a change is made, so the information isn't
>lost?

Yes, there are reasons not to lose track of the previous address, just as
you describe. If you are using a relational database management program such
as dBASE or Paradox, the easiest way to do this is to divide your address
data table, which normally would contain both names and addresses, into two
tables, one for just the names and the second for the rest of the address,
with a linking table between them.  That way you can have as many addresses
for each name as you need without tying up space for extra addresses that
would not always be used.  A typical way to set this up is as follows:

Name table:
        Record_ID_Number
        Last_Name
        First_Name
        Title

Address table:
        Record_ID_Number
        First_Address_Line
          .
          .
        Last_Address_Line
        City
          .
          .

Name-address linking table:
        Record_ID
        Name_Record_ID
        Address_Record_ID
        Is_This_This_Person's_Current_Address
        Date_Changed_(if_you_think_it's_relevant)

Then in your loan file or any other table that needs to reference a person's
name and address you would have a field for the Record_ID from the linking
table that corresponds to the name/address combination you want.

One good place to see an example of this is on the Univ. Calif. Mus.
Paleontol. data model pages at:

        http://www.berkeley.edu/museum/datamodel/noframes.html
****************************************************************
Melissa C. Winans, Collection Manager (mcwinans at mail.utexas.edu)
Vertebrate Paleontology Laboratory      Phone: 512-471-6087
J.J. Pickle Research Campus               Fax: 512-471-5973
University of Texas, 10100 Burnet Road, Austin, TX 78758