A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

??s Designing DB for multi users NOT on a network/server



 
 
Thread Tools Display Modes
  #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-
  #2  
Old October 19th, 2007, 03:36 PM posted to microsoft.public.access.tablesdbdesign
mscertified
external usenet poster
 
Posts: 835
Default ??s Designing DB for multi users NOT on a network/server

Look up REPLICATION in Access help. I strongly advise against storing SSN in
a database, especially an Access database. Also since you will be storing
other personal information about people, I would make sure your database is
as secure as it can be.

-Dorian

"Richard" wrote:

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-

  #3  
Old October 19th, 2007, 04:11 PM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default ??s Designing DB for multi users NOT on a network/server

Here's what I'd do:

Create a "first-time only" startup screen that asks them what their
city/county/whatever is. You could also ask for their phone number. I'd
create some kind of unique key based on some compilation of that information.
I'd also store all that information in a little stand-alone table in their
copy of the database (accompanied by the key you create).

Now, in the main tables, EVERY TABLE has a column where that key goes. When
your users first startup the application from this point forward, that key is
read and saved in a PUBLIC variable. Then, every time a new record is added
to any table, that key value accompanies it.

Okay, that's done. Now you need an "extract" routine. You should write a VBA
routine to extract all records and place them in a flat-file (.TXT) which can
be sent to you via email. The "header" for the flat-file contains all the
city/county/phone data, as well as the key. This will ultimately become your
"master" so that you can lookup data by city/county/etc. YOUR little
stand-alone table will have ALL those key-records from ALL the counties in
it. Now all the individual records follow. If there are multiple tables, each
record should have a field identifying by name, which table that record
belongs to. Your IMPORT routine would then parse this file as needed by
table, and do the puts.

On the counties' side, once a record were dumped, a flag would be set in
that record, indicating that it had already been sent to you. That would
preclude it from being RE-sent, unless the users said they wanted ALL records
to go again.

That should give you the appropriate functionality you seek, and only be a
few days of programming effort on your part. (Perhaps this is too
complicated, and others might have a simpler way. I've spent many years in
manufacturing shop-floor data-collection systems, and those require 100%
accuracy and redundancy. Because of that, my solutions tend to leave no room
for ambiguity as to record statuses or up/downloading methodology.)
  #4  
Old October 19th, 2007, 10:02 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


Thank you Dorian,

I got the impression that Replication was for use on a server or used like
Briefcase between two computers. Perhaps I was in error and I will read up
on Replication as you suggest.

As for the use of SSN, I was only giving SSN as an example of a complex
numbering system to be used as an ID. Since 99% of the individuals in the
historical db are dead and most died long before Social Security was
invented, you comment made me smile.

Again, thanks for your prompt reply. R-



"mscertified" wrote:

Look up REPLICATION in Access help. I strongly advise against storing SSN in
a database, especially an Access database. Also since you will be storing
other personal information about people, I would make sure your database is
as secure as it can be.

-Dorian


  #5  
Old October 20th, 2007, 12:07 AM 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

Thank you Dennis for your prompt reply. You are obviously more experienced
at db design than I and I will have to sit and chew on your suggestions. I
would hope that I can get back to you in the future with any questions.
Also, I am doing this in my free time so can't always be timely in
communicating.

That said, I have a couple of questions about the first suggestion you made,
with apologies for my naivety.

"Dennis" wrote:

Here's what I'd do:

Create a "first-time only" startup screen that asks them what their
city/county/whatever is. You could also ask for their phone number. I'd
create some kind of unique key based on some compilation of that information.
I'd also store all that information in a little stand-alone table in their
copy of the database


As an experiment, I did create a list of counties and towns which I
extracted from official gov. lists and cleaned up in Excel, then added
columns with abbreviations for each. I then imported that to Access, which
created two tables tblTown (with a column "Lookup to tblCounties") and
tblCounties (with sublists of all the towns for each county). Would this
suffice for the Key you suggest above? Those tbls could be part of the db.

(accompanied by the key you create).


I also created a county town code system based on a two letter for county,
three letter for town. Thus the designation for Leeds Historical Society
would be "AnLds", a cemetery would be "AnLds14" for the 14th cemetery on the
town list, both in the town of Leeds, Anderson county. Would this suffice as
the Key you suggest?

My thought was that this same AnLds plus a 5 or 6 digit number could be used
for the individual person ID, although I don't know if Access will
accommodate that type of mixed letter/number as a PrimaryKey. (Also, it was
suggested to me that a eleven digit number [county 12, town 127, individual
1-99,999] would be easier for Access to handle, but I would have to
investigate how that would be set up.)

If I understand you correctly, you suggest it is better to include the Key
in every table to insure a correct link?
If so, you say "a little stand-alone table" implying that the town and
counties should be in ONE? table with an additional field with this AnLds
code?
*************
Well, from this point on I will show my ignorance if I don't read up about
things like Public variables, etc though I have a sense that you are on the
right track.
Once again, thanks so much for all your time and creativity. R-

Now, in the main tables, EVERY TABLE has a column where that key goes.
When
your users first startup the application from this point forward, that key is
read and saved in a PUBLIC variable. Then, every time a new record is added
to any table, that key value accompanies it.




Okay, that's done. Now you need an "extract" routine. You should write a VBA
routine to extract all records and place them in a flat-file (.TXT) which can
be sent to you via email. The "header" for the flat-file contains all the
city/county/phone data, as well as the key. This will ultimately become your
"master" so that you can lookup data by city/county/etc. YOUR little
stand-alone table will have ALL those key-records from ALL the counties in
it. Now all the individual records follow. If there are multiple tables, each
record should have a field identifying by name, which table that record
belongs to. Your IMPORT routine would then parse this file as needed by
table, and do the puts.

On the counties' side, once a record were dumped, a flag would be set in
that record, indicating that it had already been sent to you. That would
preclude it from being RE-sent, unless the users said they wanted ALL records
to go again.

That should give you the appropriate functionality you seek, and only be a
few days of programming effort on your part. (Perhaps this is too
complicated, and others might have a simpler way. I've spent many years in
manufacturing shop-floor data-collection systems, and those require 100%
accuracy and redundancy. Because of that, my solutions tend to leave no room
for ambiguity as to record statuses or up/downloading methodology.)

  #6  
Old October 20th, 2007, 07:20 PM posted to microsoft.public.access.tablesdbdesign
Dennis
external usenet poster
 
Posts: 1,222
Default ??s Designing DB for multi users NOT on a network/server

First, you can contact me directly at: , should you wish
to take this discussion off-line.

As to your questions:

Regarding keys IN GENERAL - they have to be a 100% unique way to identify a
record or set of records. Let's say you have a county, Marion, in Texas. The
office for that location (where you've disctibuted that copy of the database
for their use), might end up with a key like "MarTX", and every record that
comes in would be coded with that key. The HEADER record that accompanied the
download might have:

|MarTX|Marion County|Texas|333.555.1212|

Or whatever other indentifying information you want. Now, when the flat-file
is read, the first record identifying the offoce/location will be checked
against your "master county offices list". If that office is not on file,
it's added before the first detail record is processed. In reality, a unique
identifyer for each office location is completely arbitrary. As long as you
KNOW that no other office will have that key-value, you're good to go. This
data should be stored in a little one-record table all by itself. The user,
upon running the application for the first time (no record in that table
would be the trigger), would be prompted via a little popup screen to enter
that information.

Now, you said:

My thought was that this same AnLds plus a 5 or 6 digit number could be used
for the individual person ID, although I don't know if Access will
accommodate that type of mixed letter/number as a PrimaryKey. (Also, it was
suggested to me that a eleven digit number [county 12, town 127, individual
1-99,999] would be easier for Access to handle, but I would have to
investigate how that would be set up.)

My response:

Access can handle ANY combination of alphanumerics information that you
decide to be a key. And the ket DOES NOT have to be a "primary key". That is
normally a simple auto-number Long Integer (that most people just call "ID")
that uniquely identifies a record to ACCESS. You, as the designer/programmer,
don't really care what Access does under-the-sheets. So use a default
auto-number for your PK in each table, but create a custom "key" (whose
contents are of your own design) to uniquely identify data as coming from a
specific office.

You said:

If I understand you correctly, you suggest it is better to include the Key
in every table to insure a correct link?


My response:

You absolutely need a matching key in every table. Otherwise, how can you
identify what records go with what? That's the very essence of the Relational
model. But in reality, you'll have TWO keys in every record. One key will
identify the office from which the data is coming from, and will be IDENTICAL
in all records for that location's office. The second key will be to tie
individual records together in different tables. You have to be tricky with
this, and you cannot use auto-numbering key fields for this, because when you
get the various data-uploads from the counties, your copy of the application
CANNOT assign a key-value; those MUST come from the individual tables
themselves. (Actually, you really don't even need the office location ID in
each record, since you could simply add it in when the file-dump routine is
executed. I'd just add it for redundancy, a fact which is likely to drive the
Relational purists on this group absolutely NUTS. ;^) )

For example, we might have two tables, Table1 and Table2.

Table1:
ID (autonumber internal Access PK)
LocCode (your concatenated location code, like MarTX)
RecNo (record number. This will be used in the other tables to allow Table1
lookups to reference the other tables properly. Normally, this field would
not be necessary, and ID used instread, but in THIS CASE, you need to keep
track of a "next record number" in a "meta-data" table, and incerment it upon
each new record write.)
Data....
Data....
Data....
(etc)

Table2:
ID (autonumber internal Access PK. EVERY TABLE should have one of these)
LocCode (your concatenated location code, like MarTX)
RecNo (from Table1; again, this is how Table1 and Table2 are tied together)

You don't really need to use the other fields for keys IN THE LOCAL VERSION
of the database. Those will be necessary for YOUR version, when you perform a
lookup for a specific county/town/whatever. Also, when you dump this data to
flat-file, the ID fields WILL NOT GO. They are meaningless to YOUR copy, and
besides, your copy will assign new IDs anyway.

Anyway, simply repeat this procedure for all your tables.

PUBLIC variables are simply dimensioned vars that are accessible by ANY form
or query at ANY time you need to. If you fill a public var with the keys you
need, you'll aready have them at data-entry time, or when the extract file is
created. Really, though, it's kind of redundant, because you could use a
DLookup to get that information just prior to any time you need it. I'm just
lazy, and choose to always have things like that at my (programmatic)
fingertips.

Now, let's say a location creates a flatfile for email to you. That file
might look like:

|MarTX|Marion County|Texas|333.555.1212| (header)
|Table1|MarTX|00001|data1|data2|data3|(etc)|
|Table2|MarTx|00001|moredata1|moredata2|(etc)|
..
..
..
|Table1|MarTX|00002|data1|data2|data3|(etc)|
|Table2|MarTx|00002|moredata1|moredata2|(etc)|
..
..
..
|End|

Remember that in the above example, the "00001" and "00002" identify groups
of records that go together. On your Master copy, You'd use those identifiers
to tie sets of related data together. So when you get these files in your
email, you just save them to a specific directory, and then run a routine you
write where you provide the filename of the new file. It's read-in, and once
completed, the now-processed file is moved programmatically to a "completed"
folder, and becomes your log-file. You could also append a line to the bottom
of it when it's done, something like:

*** Processed MM/DD/YYYY ****

Anyway, that's what I'd do for my initial pass at it. As I'd go along, I'd
add refinements as necessary (there are always little changes in how you want
to do something as you go along.)

FYI, and on a completely different note, I work for the government. I won't
say where on the open newsgroups for privacy reasons.

Dennis
  #7  
Old November 5th, 2007, 01:25 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

Dorian, any chance that you know of a comprehensive discussion or review of
this process of reconciliation, esp. one not written by Microsoft? I would
like to understand what happens in this process well enough to explain to my
boss who is worried about what happens if there is a conflict between many
iterations of the same name (15 Joe Smiths in 12 locations, each with many
similarities in data.) Unfortunately, Access help is not very definitive.
Thank you, R-

"mscertified" wrote:


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:20 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.