Design advice needed
GP
I'm working on a project that goes above my knowledge of Paradox. Rather
than advice how to exactly do things it might be better to have advice of
how NOT to do it. I'm developing an administrative database for a medical
research study, a case-control study. The cases are to be captured from 5
medical centers and once a week information (at least 30 variables) are
going to be transfered to the central database. Usually this is made by
sending a paper and someone types in the information but know they want this
done "automatically". All centers have the Windows platform, but they may
have different general skills, different mail-programs and different
internet connections. They might also be behind firewalls, as the central
database will be. As these are confidential personal records, they must also
be encrypted in some way during transfer.
Some thoughts from me: I will distribute a Paradox 9 runtime module
(complete locked application) to all centers to ensure that all are doing it
the same way. This application must also handle the centers administration
of the study, not only the transfer to the central database. Now, the first
question is where do I handle what records that are "new"? I only want to
load new records in the central database, though old records might be
updated from the medical centers (but this I think I could stop, and to be
done manually since it will not be very common). Should I send only the new
records that hasn't been transfered yet (and how can I be sure that they
have been sent since it will be a manual transfer and no feedback from the
central database?) or should I always send all records that are completed
and handle the "new" record thing in the central database? My first thought
have been to handle it in the central database since I can't design a 100%
foolprof application and I can't trust the users to do it 100% correct all
the time (for example they might load the transfer database and forget the
transfer). They would then tick a box when a record is ready to transfer,
perhaps a ready date too, push a transfer button that loads all ready
records in a transfer table (a special problem is if this table should have
different names from different centers and if it should have a time-stamp in
the filename. I can see several problems with loading tables with changing
filenames in the central database).
Next they must encrypt the table, we have been using the PGP-software and to
do it automatically there is a PGP command line interface. Regrettable I
haven't found any way of doing a self-decrypting archive (compare zip-files)
with the PGP command line interface, only one named file at a time (here is
the first problem if we have a time-stamp filename and if we don't, the last
transferfiles will be overwritten. Since the Paradox database consist of
several files it would be more convinient to have an archive). Now the
centers must transfer these files (or as I prefer ONE self-ddecrypting
archivefile) to the contact person at the central database. Since all may be
using different mailprograms, for example Lotus Notes, Outlook or Eudora it
is difficult to design an application that just pushes a button, copies the
ready records, encrypts them and sends them automatically (though it may be
preferred and by the way, FTP is out of question because the central server
is behind a firewall that does not allow the FTP-protocol, but, hm, I just
remembered we have an Unix FTP-server outside the firewall).
Now, on the central database side they must move the encrypted file(s) to
the correct directory (new one every time or the same everytime?), decrypt
them and now we have a step before it can be loaded into the central
database. The new records must be assigned a unique identity-number based
upon information transfered (they might be excluded from the study for
different reasons and then they must have special identity-numbers). I don't
really know how this step should be designed. Should I have different forms
for different centers? How do I know what are "new" records? Of course I
could have a time-stamp on the record and if it was prepared since the last
transfer (but how do I know the exact last transfer, they might transfer at
friday 12.00am and then continue to work making records ready until the
friday 17.00pm that are not sent until next friday?) I show it in the form,
they work through assigning numbers and then push a button, load into
central database and this load will check for the "new" thing (main identity
number is the swedish personal numbers, also the key-variable in the central
database, which differs from the study identity numbers).
The above update function are only one aspect of the central database, it
will update from several other sources, run a questionnaire and
bloodsampling study and send output to several other sources but the above
update functions are the key to the whole design. This study will run over
approx. 2 years (this means it will then be "throwed" away and we can't put
in resources to make a true professional application) and most of the data
in the central database will be transferred for statistical analysis.
Feel free to comment anything, small or big.
Thank you,
Gunnar Petersson
Department of medical epidemiology
Karolinska Institutet
Stockholm, Sweden
gunnar.p...@mep.ki.se
Liz
This looks like a good use for the Corel Web Server OCX and an
Apache SSL server. You may wish to go to
coreldevelopers.paradox-web and check out the threads on this
(also, the Paradox Resources page of http://www.rdaworldwide.com/
has a tutorial on using the Web Server OCX and a paper on setting
up the Apache SSL server). If your users entered their data via
the internet, your web server could enter all the data into one
set of tables (either the central database, or a separate set
which gets added to the central database). After reading your
full description, it sounds like this would be the easiest thing
- far less complex than trying to figure out how to transfer the
right files, how to update the right records, etc. etc.
Another alternative is to check out PeerDirect
(http://www.peerdirect.com/) - a replication product, or go visit
http://www.rdaworldwide.com/ (seems like one of their products
might do this - I'm sure Denn will jump in and comment further).
Regards,
Liz
---
Get the lead out before you reply
GP wrote:
>
> Hi,
>
> ...I'm developing an administrative database for a medical
Dennis Santoro
The firewall may be a bit of a problem. But if you can get Apache-ssl on the unix
server outside the firewall and give it the ability to pass data through the fire
wall (open some non standard port for it I suppose) then this would be an ideal
way to go. Transmission would be secure, and data entry would be in real time. For
additional security you could go with our Security Guard for the Internet or the
full Security Guard product which includes SGI and password protect your tables
and require the internet users to have user names and passwords so that the system
can issue real table passwords for access. You can control a lot this way. And the
overall cost is really low (1 copy of p9, 1 copy of SGI and development time).
As for the design questions, I suggest while you are at our site you also download
the papers on Database Basics and Normalization (and read them in that order) as
well as the ones on the Web Server and on proxying through Apache.
I think you will need some serious thought about a good normalization considering
all the parameters you outline. Give it some careful consideration. Also, if the
study is critical you may want to consider spending a few bucks on some consulting
help. There are others out there besides my company and my company does work with
health care a lot.
That should get you started. Jump back in with questions as you proceed.
HTH
Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
375 High Street
Rochester, MA 02770
(508) 295-7350
and
Waldweg 5
83512 Reitmehring
Germany
08071 924271
Providing solutions to health care, business, governments and non-profits since
1982
Liz
I believe you can allow access through a firewall from specified
IP addresses (or patterns thereof). In other words, with a good
internet person, I don't think the firewall will prevent anything
and will still offer all the desired protection...
Liz
Gunnar Petersson
We have been thinking over the web-possibilities for a while but we have put
it in the drawer just because we have realised that the resource input into
development would be to great considering the high security (it isn't enough
with a secure link we must assure that only authourized people can access
the webserver for example by digital signatures or smartcards). We must also
manage to pass the firewall, buying hardware for the webserver,
installing/learning Apache/Corel Webserver and develop the Paradox-interface
to the Corel Webserver. This is a fullblown development project which will
take months to implement. Surely, we need to do it but one research project
can't finance the building of a complete new technical platform and they
need their application running sooner than that (now that's typical,
suddenly someone wants something done without giving us time to do it)
I can advice them to do it like they always done (they won't be happy), that
is, manual registration, or I can try something like that I presented in my
first posting which I'm not sure will work as I want since I never done it
before. I would be happy to know if anyone that done this "automatic" table
add from different sources have any suggestions for me. Is it feasible at
all?
Surely, I will be back with web-interface questions, sometime (if we don't
choose the Microsoft platform :-)).
If we have any consultants in Sweden reading this, having the necessary
knowlegde, I would be happy to discuss a project.
Thanks,
/Gunnar
gunnar.p...@mep.ki.se
Graham Farquhar
If a web based solution is out of the question then I'd offer a few
suggestions. I've not actually done what you want to do but did start
designing a similar project which was later shelved.
Data Capture...
===============
Generate two-part keys for each record during the data entry process so
that the 'centre' is identified followed by an incrementing unique
integer. Eg A000001, B000001, A000002 etc. (Where A, B are centre
codes).
Timestamp (ie separate date and time fields) each master record when it
or any child record is updated. I think Dennis has a tool on has site
which handles this.
Add a status field to identify New, Amended or Deleted(if allowed?)
records.
It might life easier to manage if only the centre that added the record
is allowed to make updates to it but does this reflect real life?
Data Transmission...
====================
Keep a log of transmissions at the 'senders' end.
Extract the master and child records that have a 'modified' timestamp
later than date of last transmission. Keep this extraction to verify
against the returned consolidation table.
Add a file (Pdox table or text file) containing info about this
transmission (timestamp, centre, num of records etc).
Can you zip all the tables into one file, then PGP encrypt that?
Zipping can certainly be done using the ObjectPAL 'execute' command. Not
sure about PGP - you may have to find a DLL that you can call from
within Pdox.
Give the encrypted file a default name (but different for each centre) -
this will be your email attachment.
Email the file with a standard subject line to a dedicated email
address. If the email systems are MAPI compliant you can use the Pdox
'mail' type methods to construct and send emails including attachments.
I'd set Pdox up with a timer event to schedule an out of hours
transmission. Otherwise the 'simple' approach may be to rely on manual
intervention - set up a system to email the default PGP'd file at the
end of each day. (you could always get Pdox to trigger a reminder
message to prompt action!)
Data Receipt and Consolidation...
=================================
Check out 'Event Scheduler for Corel Paradox' from Rick Kelly at Crooit
Software (http://www.crooit.com). This may be just what you need to do
the following...
Automaticlaly answer emails for the dedicated address.
If the subject line matches then
-save the attachment to an 'InBox' directory
-run a Paradox routine to decrpyt, unzip and update the consolidated
database with the received tables and log the event.
-move the processed attachment to an archive directory (changing it's
name to include a timestamp)
The updating of the consolidated database is a matter of reading each
received master record and appropriately dealing with it and it's
children depending on if it's a new, amended or deleted record. The
unique keys generated by the centres mean everything can be loaded into
one central system.
And finally...
Once you've updated the consolidated database with each centre's
submission you then need to send it back to all of them. Again Event
Scheduler could probably handle this for you.
Centre user saves Incoming New DB to \NEWDATA (overwrite previous
version). Start up routine checks \NEWDATA\ISSUE.DB (see below) if
timestamp later than \DATA\ISSUE.DB then...
Copy \DATA to backup ----> \OLDDATA
Copy \NEWDATA to current ----> \DATA
*If you send back a file containing the 'LatestIssue' timestamp then you
could add some code to the start up of each Centre's Data Capture System
to read this to detect if a later issue has been received since they
last ran the program.
For 'belt & braces' it'd be worth checking that the extracted records
which formed that centre's last transmission are the same as those in
the new update. Retain those that aren't for a re-send.
--
Graham Farquhar
=============================
e-mail : gra...@farquhar.net
=============================
Dennis Santoro
See in line...
Gunnar Petersson wrote:
> Thanks, Dennis,
>
> We have been thinking over the web-possibilities for a while but we have put
> it in the drawer just because we have realised that the resource input into
> development would be to great considering the high security (it isn't enough
> with a secure link we must assure that only authourized people can access
> the webserver for example by digital signatures or smartcards).
Well, access to the web server itself should be managed by the firewall and the
permissions on the server and the web software. If you are using ssl you will be
producing digital verification and you can use one of the majors like verisign
if needed. As for access to the data, you can place that on a completely
different machine so that only the Pdox ocx web server knows where it really
sits along with the proxy on the web server to increase your security. You can
further increase the security by placing the data in a folder that is not below
the ocx root. You can enhance it even more by requiring user authentication and
even further by adding pdox table level passwords. The last two you can buy from
us with our Security Guard product. The other stuff is basically web/lan setup
stuff.
> We must also
> manage to pass the firewall, buying hardware for the webserver,
> installing/learning Apache/Corel Webserver and develop the Paradox-interface
> to the Corel Webserver.
Well you are going to need most of that even if you take the approach you were
considering. I don't think the overall development effort will be much more
extensive in the multiple systems and combining data approach. And you will not
use the corel web server but just the OCX from paradox. Check out Tony McGuire's
lessons on this on the paradox resources page on our web site.
> This is a fullblown development project which will
> take months to implement. Surely, we need to do it but one research project
> can't finance the building of a complete new technical platform and they
> need their application running sooner than that (now that's typical,
> suddenly someone wants something done without giving us time to do it)
Well, that is more an admin/political/budget issue but I suspect the development
cost for a multi system approach is not much less than doing the web based
approach. Check out the info on proxying Apache-ssl on the same page on our
site.
> I can advice them to do it like they always done (they won't be happy), that
> is, manual registration, or I can try something like that I presented in my
> first posting which I'm not sure will work as I want since I never done it
> before.
So they can have a solution they won't like, a solution you can try and develop
that may not work and that you would have to learn as you go or a solution that
should work in a fairly straight forward manner that you would have to learn as
you go. Which to chose, which to chose <Grin>
> I would be happy to know if anyone that done this "automatic" table
> add from different sources have any suggestions for me. Is it feasible at
> all?
I have done something like this in a Help desk app we wrote. But it is a pain.
And it requires site tags and key ranges and a number of other things. It may
not be possible with your data. It would depend on the data and how it is
structured. Regardless it is really a PITA.
> Surely, I will be back with web-interface questions, sometime (if we don't
> choose the Microsoft platform :-)).
Well, you can do this with IIS too (if that is the M$ platform you mean). If you
mean Access, we will probably hear from you again anyway.
> If we have any consultants in Sweden reading this, having the necessary
> knowlegde, I would be happy to discuss a project.
We aren't in Sweden but we aren't in the same country with many of our clients
if you want to talk.
Sorry for the late reply but I needed a break so I took a couple of days to go
skiing (the snow is great here in the Northeast US right now!)