I have to create a unique code to every user registering. The thing is that the unique code needs to be generated based on which city the user is from.
So the tables I have are:
CREATE TABLE `microreg`.`users` ( `user_id` INT NOT NULL
AUTO_INCREMENT , `user_code` CHAR(10) NOT NULL , PRIMARY KEY (`user_id`),
UNIQUE (`user_code`));
user_id is for unique identification of users in DB.<br/>user_code is
the readable code generated for the use of users. They access their
account with that unique code.
CREATE TABLE `microreg`.`districts` ( `district_id` INT NOT NULL
AUTO_INCREMENT , `district_name` VARCHAR(50) NOT NULL , `district_code`
CHAR(4) NOT NULL , PRIMARY KEY (`district_id`), UNIQUE (`district_code`));`
This contains as about 20K districts with unique code assigned.
say,
Trichy ITRY
Coimbatore ICBE
Kolkata IKOL
Delhi IDEL
...
New York UNYK
Washington UWDC
So, when user registers with Trichy as district, he will be generated with
user_id : 1
user_code : ITRY000001
second user from same city
user_id : 2
user_code : ITRY000002
another one from New York
user_id : 3
user_code : UNYK000001
user_code is unique with respect to district. [User with id 1 and 3 are not same]
So far, I fetched the maximum value of the selected district of the user
SELECT RIGHT(MAX(user_code),6) FROM `users` WHERE user_code LIKE 'ITRY%';
and then increment it by 1 and then add it back with district_code appended to it.
INSERT INTO `microreg`.`users` (`user_id`, `user_code`) VALUES (NULL, 'ITRY000003');
This method works perfectly when there is only one user updating at a time. But this started creating problem when multiple users concurrently started registering.
Since unique is set, two users with same user_code was generated and creates overhead.
Previously, for another application, I created unique tables for every code (like district here) and added the user to that table and then appended back to original table (like users here).
In that case I had only 4 types of code (like district here). But for this app, I have got about 20K districts. I couldn't get a spark for this. (For information, I use PHP with mysqli for this application) Thanks.
