RichardWhite321
New member
- Local time
- Today, 19:28
- Joined
- Jan 11, 2010
- Messages
- 3
Hi
Many thanks for reading this post.
If you have a moment, I would really appreciate a little database design advice. I have searched this forum, but have not yet been fortunate to find the advice I need.
At the end of this month I will run a survey of coffee growers in Uganda.
I have a draft database design (attached) and I am just starting to think about the design of the forms.
However I have one big concern/unknown.
Data will be collected on 9 portable computers spread over 25 rural villages during each day. The computers have no connectivity during the day, but they are brought back to a central office each evening.
I plan to merge the data from all 9 portable computers into one 'master database' at the end of each day, do any checks/fixes that are necessary in the evening, and copy the master database back onto each of the portable computers each morning before the start of each day, ready for the interviewers to pick up.
The idea is, each portable computer will have the full set of data that is a maximum of a day old. We need this so that interviewers who are interviewing a respondent during second visit, can refer back to what the respondent said during their first visit. This should also allow the code to be updated if there are any problems along the way.
My question is, how best to set up the database/tables to make sure this works reliably?
I am sure there are many problems I have not foreseen yet! But here are a couple of problems that I can already foresee:
1) There is an 'Interview' table. A record is added to this each time an interview is done, recording the date, the interviewer name, the interview location, and the ID number of who was interviewed.
To separate the records from the 9 portable computers I think I could set up a unique key for this table to be:
a) a single field called tblInteriew.intID that would start 10,000 apart on each portable computer eg 000000 to 009999 for the first portable computer, 010000 to 019999 for the second portable computer ...
b) a composite primary key equal to a field tblInteriew.int_ID which starts at 0 on all portable computers + a unique ID for each portable computer tblinterview.intCompID eg 01
Does it matter either way? Is there a better/more reliable way?
2) In table ‘tblPWC’, some of the fields are populated the first time a person is interviewed and some fields in the same record are populated the second time the person is interviewed a few weeks later.
In theory I believe that as both of the records from the two different computers will have the same unique ID number (tblPWC.intID) this merging should be successful and reliable. But am I being too optimistic? Is there a more reliable way?
3) Finally, are there any basic design tips you could give me for this sort of multi-portable-computer and once-a-day-updated system at this stage? I am sure a little advice would go a long-long way!
Many thanks,
Richard
Many thanks for reading this post.
If you have a moment, I would really appreciate a little database design advice. I have searched this forum, but have not yet been fortunate to find the advice I need.
At the end of this month I will run a survey of coffee growers in Uganda.
I have a draft database design (attached) and I am just starting to think about the design of the forms.
However I have one big concern/unknown.
Data will be collected on 9 portable computers spread over 25 rural villages during each day. The computers have no connectivity during the day, but they are brought back to a central office each evening.
I plan to merge the data from all 9 portable computers into one 'master database' at the end of each day, do any checks/fixes that are necessary in the evening, and copy the master database back onto each of the portable computers each morning before the start of each day, ready for the interviewers to pick up.
The idea is, each portable computer will have the full set of data that is a maximum of a day old. We need this so that interviewers who are interviewing a respondent during second visit, can refer back to what the respondent said during their first visit. This should also allow the code to be updated if there are any problems along the way.
My question is, how best to set up the database/tables to make sure this works reliably?
I am sure there are many problems I have not foreseen yet! But here are a couple of problems that I can already foresee:
1) There is an 'Interview' table. A record is added to this each time an interview is done, recording the date, the interviewer name, the interview location, and the ID number of who was interviewed.
To separate the records from the 9 portable computers I think I could set up a unique key for this table to be:
a) a single field called tblInteriew.intID that would start 10,000 apart on each portable computer eg 000000 to 009999 for the first portable computer, 010000 to 019999 for the second portable computer ...
b) a composite primary key equal to a field tblInteriew.int_ID which starts at 0 on all portable computers + a unique ID for each portable computer tblinterview.intCompID eg 01
Does it matter either way? Is there a better/more reliable way?
2) In table ‘tblPWC’, some of the fields are populated the first time a person is interviewed and some fields in the same record are populated the second time the person is interviewed a few weeks later.
In theory I believe that as both of the records from the two different computers will have the same unique ID number (tblPWC.intID) this merging should be successful and reliable. But am I being too optimistic? Is there a more reliable way?
3) Finally, are there any basic design tips you could give me for this sort of multi-portable-computer and once-a-day-updated system at this stage? I am sure a little advice would go a long-long way!
Many thanks,
Richard
