M
Marc Jennings
I have an architectural question that I would appreciate some input
on, please.
Earlier this year I spent some time in Sri Lanka working with a
rpoject to help after the tsunami. A part of the work I was doing was
centered around an Access database to keep track of the residents of
the IDP (Internally Displaced People) camps in the Galle area. The
solution I had to implement was to take data from each of a dozen or
so "local" instances of the database out in the field, and copy the
data back to a central instance.
Since I returned home, the database has been modified extensively, and
deployed to the aid workers in it's new format. I am now faced with
re-writing the code to take the new format into account. In
principle, this is quite simple, but I want to be able to accomodate
any future ammendments to the database schema.
I have come up with the following as a strategy to cope with the
additional complexities, but I would appreciate any feedback that you
guys might be able to provide.
1) Each machine with an instance of the database will have an
additional C# Command Line app installed. The app.config for this exe
will have a unique identifier integer to allow tracking of which
database the changes came from.
2) The fact that the databases are already deployed means that it is
not possible to retro-fit triggers to track which data has been
edited. I propose that in order to figure out what data has changed,
the exe will persist the data state from the last execution, and use
that for comparison against the current run. Any data that has been
added or edited will trigger the export of the appropriate record.
Deletion of data is going to be more tricky to acomplish, but records
*should* not be deleted
3) Exported records will be uploaded to an FTP server, ready to be
downloaded by the central server and processed into the central copy
of the database.
Of course, this being the real world, there are additional
complexities to consider.
a) the database is keyed locally on an integer field. When multiple
records with the same ID are imported to the central copy, I will need
a mechanism to handle multiple keys (probably combining the ID and
database identifier mentioned in 1) above.
b) There are likely to be instances where local infrastucture does not
allow for FTP uploading. The data should be stored in a manner that
allows the copying of files to a USB keydisk device for import to the
central database.
c) The data is stored across multiple tables in the database. All
data needs to be copied across.
Possible process flow
("Client")
1 - Connect to the data
2 - Retrieve a list of all the table names
3 - for each table in the database:
a) extract the data
b) compare the current data to the previously persisted data
c) export modified records to a file
4 - overwrite the persisted data ready for the next run.
5 - upload or copy the data to FTP / Keydisk
("Central")
1 - Retreive data from store (FTP or Keydisk)
2 - figure out the ID fields for a specific records in the central db
3 - update the data.
I'd really appreciate any comments anyone can make on these thoughts.
Any extensions to the process will be most welcome.
TIA
Marc.
on, please.
Earlier this year I spent some time in Sri Lanka working with a
rpoject to help after the tsunami. A part of the work I was doing was
centered around an Access database to keep track of the residents of
the IDP (Internally Displaced People) camps in the Galle area. The
solution I had to implement was to take data from each of a dozen or
so "local" instances of the database out in the field, and copy the
data back to a central instance.
Since I returned home, the database has been modified extensively, and
deployed to the aid workers in it's new format. I am now faced with
re-writing the code to take the new format into account. In
principle, this is quite simple, but I want to be able to accomodate
any future ammendments to the database schema.
I have come up with the following as a strategy to cope with the
additional complexities, but I would appreciate any feedback that you
guys might be able to provide.
1) Each machine with an instance of the database will have an
additional C# Command Line app installed. The app.config for this exe
will have a unique identifier integer to allow tracking of which
database the changes came from.
2) The fact that the databases are already deployed means that it is
not possible to retro-fit triggers to track which data has been
edited. I propose that in order to figure out what data has changed,
the exe will persist the data state from the last execution, and use
that for comparison against the current run. Any data that has been
added or edited will trigger the export of the appropriate record.
Deletion of data is going to be more tricky to acomplish, but records
*should* not be deleted
3) Exported records will be uploaded to an FTP server, ready to be
downloaded by the central server and processed into the central copy
of the database.
Of course, this being the real world, there are additional
complexities to consider.
a) the database is keyed locally on an integer field. When multiple
records with the same ID are imported to the central copy, I will need
a mechanism to handle multiple keys (probably combining the ID and
database identifier mentioned in 1) above.
b) There are likely to be instances where local infrastucture does not
allow for FTP uploading. The data should be stored in a manner that
allows the copying of files to a USB keydisk device for import to the
central database.
c) The data is stored across multiple tables in the database. All
data needs to be copied across.
Possible process flow
("Client")
1 - Connect to the data
2 - Retrieve a list of all the table names
3 - for each table in the database:
a) extract the data
b) compare the current data to the previously persisted data
c) export modified records to a file
4 - overwrite the persisted data ready for the next run.
5 - upload or copy the data to FTP / Keydisk
("Central")
1 - Retreive data from store (FTP or Keydisk)
2 - figure out the ID fields for a specific records in the central db
3 - update the data.
I'd really appreciate any comments anyone can make on these thoughts.
Any extensions to the process will be most welcome.
TIA
Marc.