3

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?

  • Can a car change owners? Can an owner have many cars. Can a car have more than 1 owner at a time? – Sir Swears-a-lot Oct 29 '18 at 8:17
  • @SirSwears-a-lot, the answer is "yes" to all tour questions. The owner-car relationship is just an example and as you highlighted, I should mention that the question is extended to any kind of relationships – Al-un Oct 29 '18 at 11:35
2

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.

  • I have to say that specifically for Auth0, I take the limitation of request per second into account as I am using a free account. So my thoughts are not purely objective: In case of option 1, I need to submit a request to Auth0 whenever I want to know someone else information (name, nickname, email...) which is limited to 2 requests per second for free account – Al-un Oct 29 '18 at 11:39
  • I think this is becoming more of a question of architechture than db schema design. Unless you have really compelling reason to keep the data in two different places i'd advise you keep it in the database. If you have limitations with your authentication and security design then you should address those. – Sir Swears-a-lot Oct 30 '18 at 3:44
  • I see. When you mentioned data, are you talking about user information (name, email and such) or just the local ID <-> external provider ID mapping? For my application specific users, I can indeed store everything in my database. I have a naive question: how about social user info? I could keep it in the database but nothing ensures that the data are up to date. – Al-un Oct 30 '18 at 12:14
  • @Al-un You will need to store the mapping somewhere... keeping it in the database along with the user account it is associated with makes sense. The decision whether or not to replicate data is always difficult. keeping it syncronised and up to date is always a challenge. Personally I prefer to avoid duplication wherever possible. This problem is worthy of a whole question on it's own. – Sir Swears-a-lot Nov 1 '18 at 3:39
  • I do agree with this comment. Many thanks for sharing your thoughts ! – Al-un Nov 1 '18 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.