CODEWITHSUNDEEP
performancepostgresqldatabase-designoptimization
Ozzy
Ozzy

Reputation: 10643

Multiple rows vs multiple columns vs array in PostgreSQL

I am creating a granular permissions system and am having trouble deciding the best way to go about it. The database I am using is Postgres.

Basically, I will have 3 tables:

permissions
-----------
p_id, name, description

permissions_groups
------------------
pg_id, name, description

permissions_users
-----------------
pu_id, user_id

Anything ending in '_id' will be an integer, the rest will be varchar or text.

The permissions system will be a part of a greater system where I rely on the user ID from another table.

Heres where I am stuck. I have two ideas:

Idea 1

permissions_group will have a column called permissions which will be an integer array. This will contain all p_ids for that permissions group.

permissions_users will have a column called pg_ids which will be an integer array containing all permission groups a user has, and a column called permissions (int array) which have all p_ids of assigned permissions which do not belong in a group.

Sample data:

permissions
-----------
1, add_user, Can create a user
2, delete_user, Can delete a user
3, view_users, Can view all users
4, random_perm, Some example permission

permissions_groups
-----------------
1, user_management, User management, [1,2,3]
// This group contains permissions 1,2, and 3

permissions_users
-----------------
1, 1, [1], [4]
// This user links to user with id of 1, has permissions group 1 and permission 4

Idea 2

The second idea is more classic SQL. The 3 tables will stay the same. There will be 2 new tables:

permissions_groups_link
-----------------------
pgl_id, pg_id, p_id

permissions_users_link
----------------------
pul_id, pu_id, p_id, pg_id

Now it will work like this:

permissions_users[pu_id]
  |
  V
permissions_users_link[pu_id]
  |
  V
p_id        OR        pg_id
  |                     |
  V                     V
permissions[p_id] <- permissions_groups[pg_id]

Sample data:

permissions
-----------
1, add_user, Can create a user
2, delete_user, Can delete a user
3, view_users, Can view all users
4, random_perm, Some example permission

permissions_groups
-----------------
1, user_management, User management

permissions_groups_link
-----------------------
1, 1, 1
2, 1, 2
3, 1, 3
// Assign permissions 1,2, and 3 to group 1

permissions_users
-----------------
1, 1

permissions_users_link
----------------------
1, 1, 4, NULL
2, 1, NULL, 1
// Assign permission 4 to user 1
// Assign group 1 to user 1

Summary

In the end, all this data will be aggregated into a list containing all permissions a user has, regardless of group. So with the above examples, the server-side code will aggregate all of that into:

Permissions for user 1:
1 => add_user
2 => delete_user
3 => view_users
4 => random_perm

The groups will serve only for visual distinction and easy application of permissions per user.

My questions

Which one of those ideas would scale best and be the fastest? Assume that in a live environment, there are 10000 users and 1000 permissions and each user has on average 500 permissions.

Or are both ideas really bad and have I overlooked some fundamental RDBMS concepts that would make it easier?

Upvotes: 2

Views: 1841

Answers (1)

thaJeztah
thaJeztah

Reputation: 29007

I would not use array columns to store these permissions, especially not if you're going to have 500 permissions per user. Array columns won't allow you to have any foreign key constraints on permissions/group and permissions/user relations

Of the 3 options I would pick the last option;

User N:N Permission Group N:N Permission User N:N Group

If permissions are not updated regularly, you may consider to use a 'NESTED' or MPTT table to store the permissions, which would allow setting permissions on several levels, e.g. a user has 'all' permissions on users and/or having groups inherit permissions from other groups.

You may find this explanation on ACL's interesting: http://book.cakephp.org/2.0/en/core-libraries/components/access-control-lists.html

Upvotes: 1

Related Questions