----- Original Message -----
From: "Richard Quadling" <rquadl...@gmail.com>
To: "Ethan Rosenberg" <eth...@earthlink.net>
Cc: "php-db-lists.php.net" <php-db@lists.php.net>
Sent: Wednesday, October 27, 2010 4:10:52 PM GMT +01:00 Amsterdam / Berlin / 
Bern / Rome / Stockholm / Vienna
Subject: Re: [PHP-DB] Incrementing Primary Key

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

 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 : e-e.com/M_248814.html : bit.ly/9O8vFY

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Well pointed out Richard, 

The App should be as simple as possible and should not try to replicate things 
are done easily by databases. 

Using stored procedures for all your CRUD and data integrity checks will save 
you quite a lot of time. 

with kind regards,

Max Kimambo
Franz-Stenzer-Straße, 51 
12679, Berlin.
T: +493057706550 (new number)
M: +4917649520175

PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to