Generating unique id for users

[sunilmp]

Hi all,
I m designing a hospital management project, and I need to provide unique id to every patient. The requirement from our client is that the format of this unique id is: ddmmyyxxx. For example, 080710001, 080710002 etc
Here is what I did:
concat dd mm and yy strings. now concat auto increment field which is initialized with 000. so the first record will be ddmmyy000. and it will continue.

My problem is, after every day, the initial value of auto increment field(last three digits) shud be reset to 000. i.e., for 09/07/2010, the first record shud start with 090710000 and not some 090710xyz.
How this can be done? Shud I need to alter table every day to reset the auto increment value?

Thanks in advance.

[d5e5]

Are you quite sure the hospital will never have to admit more than 999 new patients during a single day, at least not until you have retired?:)

You could create another table to store the last patient id used. Add only one row to this table. Whenever a row is inserted into your patient table, a trigger function could update the single row in your last-patient-id table. This function would compare the date portion of the last patient id to determine if it represents the current date. If so, increment the numeric suffix of that id. If not, update the id to current date followed by the reinitialised numeric suffix. (Or the date portion and numeric suffix could be in two separate columns in your last-patient-id table, if that's easier.)

I haven't written trigger functions in MySQL yet, so am not certain but fairly sure you can write one to do that.

A similar question is discussed at https://www.scriptcase.net/forum_en_us/index.php?topic=1339.0

[pritaeas]

When you create a new patient-id you can also join today's date with the number of id's starting with that date already in your table. If there are no records for today yet, the count will be zero.

[tesuji]

Hello,

I am using such a self-written auto-number generator on MS-SQL Server and Sybase databases for generating compound numbers, e.g. suffix.sequence-number.prefix. The numbers are generated from a user definded function for a specified table, say UDF genKey('table_name').

UDF genKey is able to manage various types of compound numbers, for example numbers which are taken from a given interval, numbers incremented by 10, numbers which are reset to start value if max. value exceeded. The data of all number types are stored in an own database table, say genkeys.

-- In principle, using such a number generator proceeds as follows:

create table genkeys ( ... )

insert into genkeys (... data of your specific number generator ...)

create function genKey (in keyTable varchar(25))
returns keyDomain

-- Generate new key for table keyTable depending on generator data stored in genkeys

return (new_key_for_table_keyTable);

end; 

-- Usage of genKey

begin transaction
  ... ;
  Insert into myTable (pk_ofmyTable, ... ) values (genKey('myTable'), ...);
  ... ;
commit;
   
-- Because UDF genKey() modifies table genkeys, this update and the insert/ updates 
-- using genkeys must build a unit of work, therefore begin transaction and commit are
-- absolutely necessary, ok, also sometimes rollback instead of commit.

The outlined generator system works efficiently on the noted databases. The UDF keyGen is written in SQL standard language PSM (Persistent Storage Modul, really weird name for a SQL procedural language derived from Oracles PL/SQL, now has been standardized since 1999) and is portable for most database systems.

If required, I can post some parts of UDF genKey() and generator table genkeys. However, I don''t have a UDF for generating YYMMDD.nnnn compound numbers up until now. Yet, on the basis of my existing genKey() one can easily extend it for this sort of compound numbers, especially the algorithm for generating new number, adding suffixes/prefixes, resetting the number if date changes can easily be adapted.

Btw, I personally don't like those compound numbers because overruns are always threaten as already stated by other poster, therefore simple autoincrement key is almost more harmless.

-- tesu

[pritaeas]

Btw, I personally don't like those compound numbers because overruns are always threaten as already stated by other poster, therefore simple autoincrement key is almost more harmless.

Nice post. I agree with the above. In addition I always use a normal autoinc key, next to the customer's required id. At least then you can keep track of overflows, and your keys won't fail.

[d5e5]

Nice post. I agree with the above. In addition I always use a normal autoinc key, next to the customer's required id. At least then you can keep track of overflows, and your keys won't fail.

That makes a lot of sense. Give the customer the field they want. I don't remember who told me that a good key should NOT have meaning, but I think this was what they were getting at. The customer wants a patient id that means something to them such as "date of admission and this was the nth patient admitted that day." Fine. Provide that. But the real key should be something that doesn't prevent adding new patients even in unlikely situations (such as another hospital merges with this one and thousands of patients need to be added to your database.)