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

Question

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.

I would be tempted to use many-to-many (#1) for the source of truth (i.e. all of your admin forms where you manage the user feature associations) and then denormalize (via callback?) to an array column on the user (#3), when that user is logged on you just check the array for access. I think you could just use rails serialize :features, Array for that column, nothing special needed at the database level since you never actually query that column. — house9
– house9, Commented Apr 29, 2017 at 0:10
since you never actually query that column seems like a bad assumption. — max
– max, Commented Apr 29, 2017 at 0:21

max · Accepted Answer · 2017-04-29 01:38:09Z

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

Collectives™ on Stack Overflow

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

Option 1: Many-to-many relationship

Option 2: Multiple columns

Option 3: Array column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Option 1: Many-to-many relationship

Option 2: Multiple columns

Option 3: Array column

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related