1

The similar questions has already been asked, but my question is quite different with some research done over it.
On signing up to a website, I provide
FirstName: Ajay, LastName: Kumar
Now the backend API need to create a unique-Id from these two texts. Why?
Yes, I have used the autoincrement Integer column for storing the user-id, but this is the Unique_id which will be shown in the URL of the user's profile page, the way most of the websites(fb, twitter, quora) do. I think it helps in SEO for searching the people's profile.

Approaches in my mind:

1. FirstName + LastName + mysql-auto-generated-id
API cannot know the autogenerated ID before the creation of the user.

2. Concat FirstName & LastName and us it as unique id, if the DB fails and returns id violation error, then start appending digits starting from 0 until an valid Id is found.
It increases the database roundtrips.

3. FirstName + LastName + [random integer numbers]
It also increse the database roundtrip.

4. Use UUID concatenated to FirstName + LastName.
Since UUID is 128-bit, that's quite long and I need to substring it. This could again cause duplicate IDs

5. concatenating current timestamp with FirstName + LastName.
But that value is also quite long.

The most efficient way I have is to use UUID and substring 4-5 starting characters from it. If the unique-id is already taken, try with another UUID. I think this is best in case of decreasing the database roundtrips as well.

I am curious to know how websites handle this(other than this database recursive calls until a valid Unique-id is found)? There is proper digit assingnment in unique-id(e.g in Quora). E.g tim-cook-1, time-cook-2.

The Coder
  • 3,447
  • 7
  • 46
  • 81
  • 1
    The comment under **1.** also applies to every one of the other methods. So I see no downside to **1.** – user3386109 Oct 02 '18 at 21:53
  • point one is completely different from other point. For **1**, I can't even know what ID the MySql will assign to the new user. So I cannot append that to user unique-id while the creation of user account. – The Coder Oct 03 '18 at 05:28
  • In case MySQL refuses creating the record for profile (for whatever reason), you must register this as a failure to create user account. Which should mean that creating user account ends _after_ MySQL request completes, so it's unclear what do you mean when you say that you must have URL before database record. – Abstraction Oct 03 '18 at 09:13

6 Answers6

2

I assume that you're using MySQL. Here's a possible solution dealing with concurrent requests:

  1. create a table (first_name, last_name, count)

  2. when you get a new user registration request:

    • begin a new transaction
    • issue a select for update on this table
    • if no row, then count = 1 & insert; else increment counter & update
    • commit
Cristi
  • 117
  • 1
  • 2
  • 9
  • Three DB calls just for single user creation? 1.` Select for update` 2. use the count value and create user 3. update the count value – The Coder Oct 03 '18 at 07:34
  • Unfortunately yes ... just to make sure that you don't get two users with the same "handle". – Cristi Oct 03 '18 at 09:31
0

You can just get the count+1 of the record from data base having Firstname as 'Arjun' and Lastname as 'Kumar' then just add to your user name

It will create user name in this pattern

Arjun-Kumar-1

Arjun-Kumar-2

Arjun-Kumar-3

...

rahul shetty
  • 126
  • 7
0

UUID is a good tool to use. You can also try the Crypto module while working with node.js

Navendu Duari
  • 45
  • 5
0

I cannot see the problem here. Save the object, then create the URL from the name and the already existing id and return it to the client. You don't even have to store that unique id separately, because you can build it easily any time from the other three fields.

Selindek
  • 3,269
  • 1
  • 18
  • 25
  • what about the performance? Suppose I want to search a user based on the unique-id, in that case, it will be difficult to do indexing since there are three separate columns to look for and also it would be less performant compared to a single column. – The Coder Oct 03 '18 at 05:35
0

You can use the following generator and select how long your unique string must be that you append to the firstname-surname-[uniquenumber]

https://alex7kom.github.io/nano-nanoid-cc/?alphabet=0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ&size=8&speed=1000&speedUnit=second

If you make a unique constraint on this firstname-surname-[uniquenumber] field in the database, you can detect a collision and then just regenerate a uniquenumber again.

user65
  • 25
  • 5
0

----
CREATE TABLE name_card
(id INT(10) AUTO_INCREMENT,
name_card VARCHAR(200),
value_card VARCHAR(200),
PRIMARY KEY(id));
----

----
DELIMITER $$     
DROP FUNCTION IF EXISTS unicode_function$$   
CREATE FUNCTION unicode_function (name_ VARCHAR(200)) 
RETURNS VARCHAR(200)    
BEGIN 
DECLARE var INT(10);    
DECLARE nam VARCHAR(100);    
SET var=500;    
IF EXISTS(SELECT name_card FROM name_card WHERE name_card=name_) THEN     
SELECT value_card INTO nam FROM name_card WHERE name_card=name_;  
RETURN nam; 
ELSE 
INSERT INTO name_card(name_card,value_card)
SELECT name_,(SELECT IFNULL((MAX(value_card)+1),var) value_card FROM name_card); 
SELECT value_card INTO nam FROM name_card WHERE name_card=name_; 
RETURN nam; 
END IF; 
END$$ 
DELIMITER ; 
----

Based on the given name The below Function generates unique id. Create a table mentioned, execute Function and try.

Create table and Function Script

Sai Ram B
  • 1
  • 1