From: "Ethan Rosenberg" <ethros@xxxxxxxxxxxxx>
To: RQuadling@xxxxxxxxxxxxxx, "Ethan Rosenberg" <ethros@xxxxxxxxxxxxx>
Cc: "php-db-lists.php.net" <php-db@xxxxxxxxxxxxx>
Sent: Thursday, October 28, 2010 4:55:34 AM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna
Subject: Re: Incrementing Primary Key
At 10:10 AM 10/27/2010, Richard Quadling wrote:
>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
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)]
Hi Ethan,
This will set a new auto increment value for a table .
ALTER TABLE RecordNum AUTO_INCREMENT=1001
Regards,
Max.
--
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
