Modeling user table for single database multiple application

Question

Say we have 3 different applications - serviceapp, subscriptionapp, ecomapp, all written in ruby on rails and uses the same database in backend and tables in the backend. So the user table for all these three applications are same. If a user is part of serviceapp using the same email and credentials he can login into subscriptionapp or ecomapp and vice versa.

The reason behind choosing same user table and other table for all the application is puerly business perspective - same single crm and ticketing system for sales and cdm team to track everything. Devise is being used in all three applications along with LDAP so login and signup works fine without any issue.

Problem:

Till now users' last_login_at is a single column so we really can't tell which app he last logged in at. But now we have to start logging these details separately, like when did he last login at serviceapp, ecomapp, subscription app separetly.

Also we are starting to use a new crm of one particular app - subscriptionapp and for the clients(users) of that particular app we have to store extra information like unq_id from crm and so on.

My intial thought is to add these columns in the user table itself. But in the future we might add few extra information to user table which are app specific. Hence adding it to the main user table won't be a good idea for this. How shall I proceed in this case? I though of creating three different tables like subscriptionapp_client, ecomapp_client, serviceapp_client had associating them with the user table like user has_one ***_client.

If the association is present like if user.subscriptionapp_client.present? he is a client of that app and we can store the last login at, crm_uniq_id and all in there in that table itself.

Is there anyother good approach that might fit the problem here? I am reading about MTI but it looks like it won't solve the problem.

Answer 1

Single table inheritance with JSON.

class CreateClientAccount < ActiveRecord::Migration[5.2]
  def change
    create_table :client_accounts do |t|
      t.references :user
      t.string :uid # the unique id on the client application
      t.string :type
      t.integer :sign_in_count
      t.datetime :last_sign_in_at
      t.jsonb :metadata
      t.timestamps
    end
    add_index :client_accounts, [:user_id, :type], unique: true
  end
end

---

class User
  has_many :client_accounts
  has_one :service_account, class_name: 'ServiceApp::ClientAccount'
  # ...
end

class ClientAccount < ApplicationRecord
  belongs_to :user
  validates_uniqueness_of :user_id, scope: :type
end

module ServiceApp
  class ClientAccount < ::ClientAccount
  end
end

module SubscriptionApp
  class ClientAccount < ::ClientAccount
  end
end

module EcomApp
  class ClientAccount < ::ClientAccount
  end
end

This avoids the very unappealing duplication of having X number of tables in the schema to maintain and the JSONB column still gives you a ton of flexibility. However its in many ways just an upgrade over the EAV pattern.

It also has a lot in common with MTI. In MTI you would use an association to another table which fills the same purpose as the JSON column - to make the relational model more flexible. This can either be polymorphic or you can have X number of foreign keys for each specific type.

One table for each type.

class User < ApplicationRecord
  has_one :subscription_account
  has_one :service_account 
  # ...
end

class ClientAccount < ApplicationModel
  self.abstract_class = true 
  belongs_to :user
end

class SubscriptionAccount < ClientAccount
end

class ServiceAccount < ClientAccount
end

# ...

This is the most flexible option but if you want to add a feature you will have to create migrations for each and every table. And this also means that you can't query a single homogenous collection if you want all the types. You have to perform X number of joins.

This is not really that appealing unless the requirements for each type are wildly different.