Is it a best practice to have a "users" table in my internal database?

I am building a simple CRUD application with a database. I want to handle authentication and authorization via Auth0. Let’s just say it’s an ecommerce site and users are allowed to post reviews on products. So there’s a “products” table and “reviews” table.

My question and looking more for peoples opinions here but is it best practice to also create a “users” table and use the internal autogenerated user ID as a foreign key to those other tables (reviews…)? My thinking here is that the “users” table also has a column “auth0_user_id” which is just used for informational purposes and also when the user logs in via Auth0 I know which database row to load.

The pros here is that I now have a loosely coupled abstraction around auth. I can easily switch to internal authentication or another provider if I really wanted to.
The con is obviously that I am now storing/duplicating extra data.

I’m wondering how other folks here are doing it?

1 Like

Hi @dannyplaza,

Welcome to the Community. I’m a big fan of Auth0’s service but I think it is important to plan for the unknown future. Personally I really like the idea of maintaining user data outside of Auth0 to hopefully ease transition to a different service if that were ever necessary, and I include generating my own unique internal identifiers (ISO standard UUIDs) in this. Benefits include:

  • Auth0 does not provide a way to properly backup your Auth0 user database, so maintaining your own user database and mapping data into Auth0 as needed allows for backups and restores on demand, useful for many reasons, e.g., taking a snapshot before performing a bulk data update operation,
  • bulk data changes via the Auth0 management API can be slow and tedious compared to making those changes by connecting directly to your data store,
  • while the Auth0 user_id is unique (within context at least) it is still an Auth0-specific internal identifier … I like having service independant internal identifiers,
  • as you note, potentially easier transition to another service if needed, even running the new service + Auth0 in parallel.

Note that Auth0 may provide a solution for backups eventually. I’ve discussed this with my TAM a few times and they know the inability to do proper backups and restores is a major concern. On the other hand, I imagine providing that capability would be extremely cloud-resource intensive, potentially incurring dramatically higher costs.

1 Like

Thanks for the excellent write-up @markd! :smile:

1 Like

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.