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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
??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
|
|||
|
|||
??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
|
|||
|
|||
??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
|
|||
|
|||
??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
|
|||
|
|||
??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
|
|||
|
|||
??s Designing DB for multi users NOT on a network/server
|
#7
|
|||
|
|||
??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 | |
|
|