At 10:10 AM 10/27/2010, Richard Quadling wrote:
On 27 October 2010 14:11, Ethan Rosenberg <eth...@earthlink.net> wrote:
> 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
I'd use an INSERT trigger to generate the value.
I use MS SQL - no idea what DB you are using - and so here are what I'd do ...
Table: Sites
UniqueID int identity(1,1)
SiteCode char(1)
LastMedicalRecordNumber int default 0
Table:MedicalRecords
UniqueID int identity(1,1)
SiteID int // Foreign key to Sites.UniqueID
MedicalRecordNumber int default 0
The trigger would be something like [UNTESTED] ...
CREATE TRIGGER NewMedicalRecord ON MedicalRecords FOR INSERT AS
UPDATE Sites
SET LastMedicalRecordNumber = 1 + LastMedicalRecordNumber
WHERE UniqueID IN (Inserted.SiteID)
UPDATE MedicalRecords
SET MedicalRecordNumber = Sites.LastMedicalRecordNumber
FROM
INSERTED
INNER JOIN
MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID
INNER JOIN
Sites ON INSERTED.SiteID = Sites.UniqueID
The app need not have any part is assigning something as important as
the unqiue id of a row. That sort of integrity needs to be part of the
database.
The client app really wants to be as simple as possible. Using stored
procedures and views (which are tuned once by the SQL Server) benefit
the app in returning the required data faster and with less
utilisation. Compare that against every identical query being compiled
from scratch every single time.
If you want to put the SiteCode on the MedicalRecord rather than the
SiteID, you could. And then break the link between the MedicalRecords
and Site tables. The trigger would use the SiteCode to link rather
then the SiteID / Sites.UniqueId to get INSERTED connecting to Sites.
As far as the app goes?
You tell it which of the available sites the medical record is for and
insert it (along with any other user supplied data). The integrity is
preserved by the DB. "Just doing my job, sir!"
Richard.
--
Richard Quadling
Twitter : EE : Zend
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY
Thank you.
I'm using MySQL, which I do not think has the
ability to auto-increment a primary key from any value other than 1.
Here is some pseudo code. Please help me to set it up properly.
Table Intake
SiteID char(2) primary key not null, //
This is A for site1, B for site 2....
RecordNum int(10,0) primary key not
null auto_increment, // increment starts from 10001
etc.....
Thanks
Ethan
MySQL 5.1 PHP 5 Linux [Debian (sid)]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
