User management: third party Identity Management vs local user

Question

Context

Let's considering a standard web application handling cars. Each car has an owner. So the car structure looks like:

cars(
  id INTEGER PRIMARY KEY,
  ...
  owner_id *???*
)

For simplicity reasons, I am using an external Identity Management, in my case Auth0. My question is "how to represent users in a database when completely externalising Identity Management?". All user profile related information (such as personal information) are handled by the third party IDM.

Option 1: mapping of local users vs external users

A local User table is created:

users(
  id INTEGER PRIMARY KEY, -- local user id
  external_id TEXT,       -- id in the third party IDM
)

Then, Car is mapped to the local user id:

cars(
  id INTEGER PRIMARY KEY,
  ...
  owner_id INTEGER FOREIGN KEY REFERENCES users(id)
)

Pros

• Enforce data consistency: the car cannot be assigned to a non-existing user
• In case of a IDM change, only the users table needs to be remapped

Cons

• Back-end needs to catch the sign-up event to store the external id in its database
• After authentication, the external IDM needs to send the information OK, external_id XXX is authenticated, his/her local ID is YYY.
• the database/back-end needs to know when a new user is created
• Kind of duplicate the user ID information

Option 2: use external ID

users table is not created and cars ownership is defined by:

cars(
  id INTEGER PRIMARY KEY,
  ...
  owner_id TEXT NOT NULL -- the ID in the external IDM
)

Pros:

• Back-end is completely free from Identity management (new user, login, password forgotten...)
• after authentication, if using JWT, the ID provided by the external IDM can be used and there is no mapping to do

Cons:

• IDM-dependent. I notice that on Auth0, even social user ID depend on your tenant so if a new tenant is used or worst, IdaaS provider changes, a lot of remapping has to be done.
• Data integrity is not ensured for car ownership as there is no way to set a constraint at database level

My understanding

I would go for option 1 because my OCD would ask for data integrity but does option 2 exists somewhere down in this world?

Answer 1

There is no single correct answer. The right answer for you depends upon your requirement and how you chose to model the problem.

Depending upon the importance of the owner details they may be an entity in thier own right (requiring thier own table) or they could just be an attribute of car.

If you wish to capture/store any owner details beyond thier id, I would reccomend the first approach. If you wish to maintain owner details externally then go with the second.

Just because its odd or unconventional doesnt mean its wrong.