A treatise on Bit based permissions in a database

Bitwise.md

so there are three tables:

hotel:

• hotel_id

user:

• user_id

Two scenarios:

permission:

• permission_id
• hotel_id
• user_id
• permission_type (string/better to have as int, faster lookup)

Max number of rows = N(hotel) * N(user) * N(permission_type)

-------------- or ----------------

permission:

• permission_id
• hotel_id
• user_id
• permission_bits (masked bits, can track 64 permissions if BIGINT is used)

Max number of rows = N(hotel) * N(user) * 1 row for collection of permissions

To check permission:

allow_view_bitmask = 0b0001
permission = Permissions.objects.get(hotel=hotel, user=user)
can_view = permission.permission_bits & allow_view_bitmask