I'm integrating with services that use OAuth, and the data I need to save to the database is very similar. However, there are slight differences with the first two implementations.

They both have a token, expiration, refreshToken, and refreshTokenExpiration. However, one has a "realmId" and the other has a "redirectUri" that needs to be saved with it.

The way I see it, I have 3 choices:

a) Add both realmId and redirectUri to the same table and one will be populated and the other null, depending on which service is being used. Of course, who knows if I will have to keep adding additional columns as more services are added that have different requirements?

b) Normalize the data such that there is one table for the similar data, but separate tables that hold the unique data for each service and have foreign keys to the table with the similar token data.

c) Don't even bother trying to store the similar data in a single table. But rather create a completely separate table for each service.

Being more of a pragmatists than an ideologue, I'm leaning towards c). An additional reason for this is that one service grants tokens based on accounts whereas the other service grants tokens based on users which may potentially be over multiple accounts.

I know this is certainly a common problem, but I just don't know how to describe the problem such that I can find useful information about it in my searches.

  • 1
    Ideally, you would model your schema & application based on the OAuth specification, rather than specific implementations. The spec will better answer questions about how the metadata is used, and how configuration elements relate--thus driving the data model. In particular, the OAuth spec will tell you how much variation you might expect from future implementations. Pertinent info from that spec would be helpful for answering this question.
    – AMtwo
    Oct 17 at 6:35
  • 1
    As AMtwo indicated, understand your model and who controls it. And perhaps more importantly, how do you intend to use this information? "Data" is particularly useful until it gets transformed into "information". Don't agonize of this. There is no absolute or perfect design - just one that works for your situation.
    – SMor
    Oct 17 at 12:13

I would suggest two tables. Im assuming that if you give your application a realmID when it expects a redirectURL, it will break your application, so you definitely need them in seperate columns.

If you have the two applications using two seperate tables, you may save yourself some trouble down the road with deadlocking. You may want to treat the data differently for each application, too. For example you may need to keep the redirectURL's for 5 days and the RealmID's for 2 months. You'll save your future self a lot of headaches if you keep things seperate early on.

You can always create a view for yourself that unions the two tables giving you any advantages of having the data in one table, without any of the downsides.

  • Thanks, this is what I ended up deciding also. Theoretically I could have kept the similar data in a table and put the different data in separate tables, but I couldn't think of any real benefit to be gained and it would actually make it a bit less straightforward and more work to support.
    – BVernon
    Oct 18 at 4:32

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.