Postgresql Bits manipulation design

Question

Am planning to make a database design where I store the permission, my user has in terms of bits.

Say, my user table has the column username VARCHAR(100), permbits BIT VARYING(64)

my permission table has the columns permname VARCHAR(100), permbits BIT VARYING(64)

Example rows in my user table would be
('Joe', 1001) ,
('Ram', 001)

Example rows in my perm table would be
('View Record', 0001),
('Delete Record', 0010),
('Create Record', 0100),
('Edit Record', 1000)

So Joe would have edit and view Record permission
and Ram would have view Record permission alone

My Questions are
1) What is the maximum number of permissions that can be created?
2) How do I move on with the bit manipulation (i.e. expanding user's permission and mapping to corresponding permission in permission table).
3) Is this design fine? Can anyone suggest better alternatives?

Appreciate your advices for this question.

Don't use this design if possible. A regular relational modelling like that suggested by Dwayne below is generally better - much more indexable, for one thing. — Craig Ringer
– Craig Ringer, Commented Jul 15, 2014 at 0:01

Dwayne Towell · Accepted Answer · 2014-07-14 22:34:00Z

2

The correct solution is to use a many-to-many relationship between users and permissions, via a table such as

CREATE TABLE user_permissions (
    user_id        int REFERENCES users(id),
    permission_id  int REFERENCES permissions(id),
    unique(user_id,permission_id)
);

No bit fields are used. The presence of a record associating a user and a permission indicates that user has that permission (other fields could be added to indicate why/when they received that permission, for example). The absense of a record means the user does not have that permission.

edited Jul 14, 2014 at 22:34

answered Jul 14, 2014 at 22:01

Dwayne Towell

8,6635 gold badges39 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

palaniappanm Over a year ago

Then, can you please provide me insights where bit mask is used?

wildplasser Over a year ago

"bitmask" is not needed. One bit := the existence of a tuple in the above "junction" table. (the above method also enable you to extend the bitset without schema changes)

Collectives™ on Stack Overflow

Postgresql Bits manipulation design

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related