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.