Hi Ethan,
I am working currently as clinical developer and have developed multi site databases for clinical trials.
I would say the approach you take will depend on number of factors.
How is the site connectivity?
I had a scenario where sites had erratic and slow connectivity so i had to setup MySql server at each site and let that data sync over to a central location, with help of mysqldump then i put all the data together into one database from 3 replicas.
The application is more responsive over LAN and users are less likely to complain when entering data(can be a major difference between success / failure)
If connectivity is not an issue and the app will reside on a central web server then putting everything in a single database is an option.
Of course your database design will have to reflect that using appropriate keys to be able to tie the CRF data to a particular site patient visit combination.
If you chose to go with replication, then you have to take care on how you auto increment values so that there are no collisions, best is to assign a range to each site this is the simplest way.
1001 - 5000 Site A
5001 - 10001 Site B
Of course you will need some input from your statisticians on how they want the data, that will also drive your design decisions on what works best.
Separate db or not:
I would separate data for each study into its own database, its much easier in terms of administration and locking the study.
Is the study single blinded, double blinded ?
How will randomization take place ?
These are all questions you need to know before you can decide how you structure your database and application therefore.
If you have other questions feel free to get in touch.
with kind regards,
Max.
------------------------------------------------
Max Kimambo
Franz-Stenzer-StraÃe, 51
12679, Berlin.
T: +493057706550 (new number)
M: +4917649520175
------------------------------------------------
----- Original Message -----
From: "Ethan Rosenberg" <ethros@xxxxxxxxxxxxx>
To: "php-db-lists.php.net" <php-db@xxxxxxxxxxxxx>
Sent: Wednesday, October 27, 2010 3:11:00 PM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna
Subject: Incrementing Primary Key
Dear List -
Thanks for all your excellent help.
I am setting up a database for medical research, which will be
conducted at various sites. The sites will be identified by a letter
{A,B,C ....}. The medical record number [primary key] will start at
1001 and increment by one(1) for each patient at each site; ie, A
1001, A1002, B1001, B1002 ...... How do I do this?
Do I need a separate database for each site?
Ethan
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
