0

I am using Next.js with PostgreSQL (Prisma as ORM).

I need to assign a unique six digit number to my "survey" table in my database when creating a new survey.

Is it better to:

  1. Generate a random number and check if it is already used by another survey in the DB (this seems inefficient).

  2. Prepopulate a table in the database with all the possible numbers between 000000 and 999999 (number needs to be six digits). Then picking one at random when creating a new survey and deleting that record to be certain it can't be used another time.

If option 2: how do I prepopulate a table using next.js and postgresql ?

Or is there a better way?

I haven't tried either solution; I am looking for expert advice on the best option and how to proceed.

user21297752
  • 13
  • 4
  • 1
    Is there some reason you can't just have the survey number be an auto-increment integer field in the DB, and assign numbers sequentially/automatically? The smaller integers won't have an explicit *six* digit, but the frontend can always format them that way by prepending zeroes for display. Side-note: A six digit limit means you're capped at one million entries. Are you *really* sure you'll never need more than that? Think *really* carefully; getting locked in now could burn you badly later. – ShadowRanger Jun 21 '23 at 19:49
  • This number will be used to get to an answering page to the survey (survey-code). For that reason I thought it would be best not to have it auto-increment, so that only the persons who have the code can answer it (and you can't just guess a number that brings you to an answerable survey). Is it right to worry about that? To prepare for the (best) case using option 2, imagining I would have more that one million entries. Could I add an expiring date and then add the number back into the table to choose from ? Would that be a better solution ? – user21297752 Jun 21 '23 at 19:59
  • Security by obscurity is not a great way to go here. By your own admission, you expect to fill most of the numbers eventually, so guessing (or typoing inadvertently) will in fact work sometimes. Even if the numbers were sparse, a malicious individual could programmatically guess all the possible numbers given enough time; you need actual authentication to avoid issues like this. If you want security, use actual security techniques. – ShadowRanger Jun 21 '23 at 20:05
  • Thanks for that inside. I have been searching for a better way to have a short but more secure uid. I have come across a post that indicates that _nanoid_ is a good solution for short unique id's, using a length of at least 11 for example (https://stackoverflow.com/questions/6248666/how-to-generate-short-uid-like-ax4j9z-in-js). Would you advise using this library or to implement the function directly as I do not have any other special requirements? – user21297752 Jun 22 '23 at 09:42
  • Sadly, aside from software and library recommendations being explicitly off-limits here, I know nothing about `nanoid`, so I couldn't say one way or the other regardless. My general suggestion is to find a widely used trusted library for this purpose, because rolling your own code for security is a *lot* harder than people think it is, and the more people using the code, the more likely it is that *someone* has vetted it; code you write will not have that benefit. – ShadowRanger Jun 22 '23 at 17:03

0 Answers0