----- Original Message -----
From: "Richard Quadling" <rquadling@xxxxxxxxx>
To: "Ethan Rosenberg" <ethros@xxxxxxxxxxxxx>
Cc: "php-db-lists.php.net" <php-db@xxxxxxxxxxxxx>
Sent: Wednesday, October 27, 2010 4:10:52 PM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna
Subject: Re: Incrementing Primary Key
On 27 October 2010 14:11, Ethan Rosenberg <ethros@xxxxxxxxxxxxx> 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
--
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.
------------------------------------------------
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
