Question

0

278

Views

Postgres: Many-to-many vs. multiple columns vs. array column

I need help designing complex user permissions within a Postgres database. In my Rails app, each user will be able to access a unique set of features. In other words, there are no pre-defined "roles" that determine which features a user can access.

In almost every controller/view, the app will check whether or not the current user has access to different features. Ideally, the app will provide ~100 different features and will support 500k+ users.

At the moment, I am considering three different options (but welcome alternatives!) and would like to know which option offers the best performance. Thank you in advance for any help/suggestions.

Option 1: Many-to-many relationship

By constructing a many-to-many relationship between the User table and a Feature table, the app could check whether a user has access to a given feature by querying the join table.

E.g., if there is a record in the join table that connects user1 and feature1, then user1 has access to feature1.

Option 2: Multiple columns

The app could represent each feature as a boolean column on the User table. This would avoid querying multiple tables to check permissions.

E.g., if user1.has_feature1 is true, then user1 has access to feature1.

Option 3: Array column

The app could store features as strings in a (GIN-indexed?) array column on the User table. Then, to check whether a user has access to a feature, it would search the array column for the given feature.

E.g., if user1.features.include? 'feature1' is true, then user1 has access to feature1.

over 4 years ago · Santiago Trujillo

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-05-26T12:56:46.793Z

Many-to-many relationships are the only viable option here. There is a reason why they call it a relational database.

Why?

Joins are actually not that expensive.
Multiple columns - The number of columns in your tables will be ludicris and it will be true developer hell. As each feature adds a migration the amount of churn in your codebase will be silly.
Array column - Using an array column may seem like an attractive alternative until you realize that its actually just a marginal improvement over stuffing things into a comma seperated string. you have no referential integrety and none of the code organization benefits that come from have having models that represent the entities in your application.
Oh and every time a feature is yanked you have to update every one of those 500k+ users. VS just using CASCADE.

class Feature
  has_many :user_features
  has_many :users, through: :user_features
end

class UserFeature
  belongs_to :user
  belongs_to :feature
end

class User
  has_many :user_features
  has_many :features, through: :user_features

  def has_feature?(name)
    features.exist?(name: name)
  end
end

0

278