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
User table is created:
users( id INTEGER PRIMARY KEY, -- local user id external_id TEXT, -- id in the third party IDM )
Car is mapped to the local user id:
cars( id INTEGER PRIMARY KEY, ... owner_id INTEGER FOREIGN KEY REFERENCES users(id) )
- Enforce data consistency: the car cannot be assigned to a non-existing user
- In case of a IDM change, only the
userstable needs to be remapped
- 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 )
- 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
- 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
I would go for option 1 because my OCD would ask for data integrity but does option 2 exists somewhere down in this world?