View Single Post
  #1  
Old October 19th, 2007, 02:51 PM posted to microsoft.public.access.tablesdbdesign
Richard
external usenet poster
 
Posts: 1,419
Default ??s Designing DB for multi users NOT on a network/server

My situation: I am designing a db to be used by one Historical Society in
one town. This db could be given to all towns to use for their historical
records since the table design will be the same - only the data will be
expanded. The State Archivist would like to have a copy of the data to
combine into one large db, with the provision that periodically, each town
could submit their current db to the state and the state's db could then be
updated.

The db will have tables doing the following:
Personal information regarding an individual [names; birth; death];
Military service [subtables of time served; where served; KIA; MIA;
promotions; etc];
Spouse [subtables marriage records; children; etc];
Historic records [location of artifacts, docs and pics relating to
individual];
Location where person is interred [cemetery name, town, county}

The problem as I understand it:
In Access, each record, while unique in the individual town database,
would lose its uniqueness when combined with another db from a different
town.
In other words, there would be many PK1s from Access's point of view -
one for each of the records of the 1st entry in each town's db.
Thus Access, unless each table in every town had the town and county keys
in them, would not know that picture #3 in Town A should ONLY be linked with
an individual in Town A.

I started by setting up tables for county relating to town, town relating to
individual, but was told that that was not good enough because of the PK
problem in Access with regard to all the subtables.

One idea that I had was to have a unique ID (think SSN or long distance
phone type of number) based on a county number, town number, and then
individual number for each person, however I don't know how to go about
setting that up, and I don't know whether that will eventually solve the
problem.

My question: How do I design this db so that each entry's uniqueness would
be preserved when combined at the state level? I would like a discussion on
whatever ways you think might work, and any comments by others about those
suggestions.

Many thanks to all who respond, with an appreciation for your time and
efforts. R-