At 10:10 AM 10/27/2010, Richard Quadling wrote:
On 27 October 2010 14:11, Ethan Rosenberg <> 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

 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
  SET LastMedicalRecordNumber = 1 + LastMedicalRecordNumber
  WHERE UniqueID IN (Inserted.SiteID)

 UPDATE MedicalRecords
  SET MedicalRecordNumber = Sites.LastMedicalRecordNumber
   MedicalRecords ON INSERTED.UniqueID = MedicalRecords.UniqueID
   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

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 Quadling
Twitter : EE : Zend
@RQuadling : :

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



MySQL 5.1 PHP 5 Linux [Debian (sid)]

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to