DEV Community

Discussion on: How to generate random IDs that are not individually unique, but unique across 2 fields (columns) in a MySQL database table?

Collapse
kp profile image
KP Author

@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?
Collapse
peledzohar profile image
Zohar Peled

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.

Thread Thread
kp profile image
KP Author

@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?

Thread Thread
peledzohar profile image
Zohar Peled

Then why not simply use an integer?

Thread Thread
kp profile image
KP Author

Also a possibility