----- 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 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