One easy way to do it is to generate the entire range of possibilities and store in a table, and then, when a user creates a new comment, pick one random row from that table that this specific use didn't already use.
Personally, I'm a SQL Server guy so I don't know the exact specific details on how to do it in MySql, but in SQL Server you can use the same trick as shown here:
If you add an identity column to the table, and use order by newid() when inserting the records (that will be slow but it's a one time thing that's being done offline from what I understand) then you can use order by on the identity column to select the records…
and I'm guessing something like this can also be used in MySql.
The benefit of this approach is that you can simply select for each user the first row from the strings pool table where it's id is larger then the last row they used - and you don't need to sort randomly at runtime.
@peledzohar
thank you, while I'm more of a MySQL guy (I understand that SQL server can be quite different) I read your comment and the article you wrote here
Instead of pre-generating a new table, I would like to generate the commentID field (such that userID + commentID combined field is unique) dynamically at runtime. Is this at all possible in MySQL?
Yes, I'm guessing it's probably possible - as far as I know, MySql does support unique indexes on multiple columns - so all you really have to do is to create such an index, and then generate a random string and just attempt to insert. If success, great. If failed, generate a new string and repeat until success.
With 1.6M options, I don't think the Birthday problem is really a factor to consider here.
@peledzohar
good points, thanks for sharing. Instead of trying to reinsert on failure, I just thought of this to avoid collisions (even though the birthday problem isnt a pressing issue at the moment). Let me know what you think of this.
What if:
I dropped the idea of a unique userID+commentID, and ONLY made the commentID unique?
Instead of randomly generated commentID strings, what if I made the commentID the primary key, and incremented it sequentially, starting with 4 characters, and when we run out of 4 characters, move to 5 and then 6? Is there a downside to doing this? I dont think I necessarily need the commentIDs to be randomly generated since all comments are public so security isnt really a concern here.
Taking this page dev.to/peledzohar/comment/fmnb as an example, it would only load if I get both the userID "peledzohar" and commentID "fmnb" correct. Otherwise it would show a 404 page.
Thoughts on this approach? In this case, would commentID be a primary key? How would I get it to auto-increment and go from a default of 4 characters to 5, and so on? Am I still overcomplicating or over-thinking this?
One easy way to do it is to generate the entire range of possibilities and store in a table, and then, when a user creates a new comment, pick one random row from that table that this specific use didn't already use.
Personally, I'm a SQL Server guy so I don't know the exact specific details on how to do it in MySql, but in SQL Server you can use the same trick as shown here:
If you add an identity column to the table, and use
order by newid()when inserting the records (that will be slow but it's a one time thing that's being done offline from what I understand) then you can useorder byon theidentitycolumn to select the records…You might also want to read this:
Random strings. Generate on the fly, or select from a pre-populated table?
Zohar Peled ・ Sep 15 ・ 3 min read
@peledzohar thank you, while I'm more of a MySQL guy (I understand that SQL server can be quite different) I read your comment and the article you wrote here
Random strings. Generate on the fly, or select from a pre-populated table?
Zohar Peled ・ Sep 15 ・ 3 min read
Instead of pre-generating a new table, I would like to generate the commentID field (such that userID + commentID combined field is unique) dynamically at runtime. Is this at all possible in MySQL?
Yes, I'm guessing it's probably possible - as far as I know, MySql does support unique indexes on multiple columns - so all you really have to do is to create such an index, and then generate a random string and just attempt to insert. If success, great. If failed, generate a new string and repeat until success.
With 1.6M options, I don't think the Birthday problem is really a factor to consider here.
@peledzohar good points, thanks for sharing. Instead of trying to reinsert on failure, I just thought of this to avoid collisions (even though the birthday problem isnt a pressing issue at the moment). Let me know what you think of this.
What if:
Thoughts on this approach? In this case, would commentID be a primary key? How would I get it to auto-increment and go from a default of 4 characters to 5, and so on? Am I still overcomplicating or over-thinking this?
Then why not simply use an integer?
Also a possibility