TransWikia.com

What is the preferred / common way to separate "access-tokens" in a database design

Database Administrators Asked on December 12, 2021

I’m currently having a case where a user has to verify their email, is able to reset his password and can log into a web-interface (so far and not limited to). There a now 3 places where some kind of token is used to give a user access to a specific action.

  1. Web session – Access token used to access API endpoints.
  2. Email verification – Access token used to verify a (new) email-address.
  3. Password reset – Access token used to change a password.

So far I’ve come up with two possible solutions:

a) A separate table for each token:

There will be a web_session, email_verified and password_reset table.

Pros:

  • Each table can come with meta-unique properties
  • You can easily grab data from on of these tables based on their name

Cons:

  • All tables are kinda related but have very different names (and I couldn’t come up with a naming-way to "group" them)
  • Almost all columns are equal across these tables (id, user_id, token, created_at)
  • Every time something like this gets added I’d have to create a seperate table with (always / almost?!) the same columns.

b) A unified table with a type / role column:

There’s just one table with id, user_id, token, created_at and type (enum) – type can in this case be all sorts of access-type, in our case api-session, email-verification and password-reset. (Of course this could be improved in a way that a token will give general access to a specific thing)

Pros:

  • It’s just one table
  • Can easily be extended without making it hard to use (I’ll always be one table)

Cons:

  • Another conditional where you’ll have to check the type before looking for the token itself
  • Can get confusing I guess as although it’s only one table there are differences based on type

Is there a generally preferred way of doing this (I’m using mariadb if that’s important) / is there a third, better way of doing it? Or does it really not matter that much and I’m just overthinking it?

I tried to find existing schemes / db-designs but I couldn’t find anything useful as there is afaik no name for this whole thing (please tell me I’m wrong and I just cannot search correctly).

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP