How to use tinyint

Databases
1

I am trying to give several permissions such as giving someone an admin, or special permissions for accessing different contents from my site… Can i use tinyint and define it as… {0:admin, 1:permission1} etc… is this format correct?

2

tinyint allows for values from -127 to 127 if signed, 0 to 255 if unsigned. How you choose to use those values are up to you. Just ensure the values are documented somewhere useful within your code that uses the values.

3

No, You cannot use that format. You cannot insert serialized data into an tinyint column. You can use 0, or 1, etc which would be the correct method for storing status type data. You would have another lookup table keyed to those values with description.

i.e (0 in main table would mean no permissions)
permission_id (auto-int) | permission_description
1 | admin
2 | permission1

4

So what is the best way to do this?

5

Do you think you will ever need the field to have more than 255 possible values?

Though there is no such thing as a “no matter what, this is always the best”, I think TINYINT should work fine.

6

I just told you exactly how to do it. What part don’t you understand?

7

I understand the 0 and 1 part for admin but what if i wanted to give the user more than 2 options? I have heard of enum… let’s say… different access level…could i do… 1:admin… 2:primer level 3: level 1?

8

You would have a joiner table with the user’s ID and the permission number. That would give you the opportunity to have unlimited permission levels. So three tables. The users table, the Joiner table, and the lookup table with the permission ID numbers and description.

9

Thanks. . I will give that a shot!

10

If you really want to do it up, learn about bitwise permissions.

11

Another solution would be to have role based access depending on the case. Imagine you want 1-N permissions linked to roles and 1-N roles linked to users. You would have the following tables:

users
permissions
roles
role_permissions (permisions linked to roles)
user_roles (roles linked to users)

Edit: there is also an article about the permission things on sitepoint: https://www.sitepoint.com/bitwise-operators-still-relevant-modern-php/ .

12

That would fit better if one insists on single column indeed.

However in that case I would use INT instead of TINYINT, as TINYINT would only be able to code 6 different permissions: 1, 2, 4, 8, 16, 32 and 64

13

Or more specifically, 8 permissions, because you’d use an unsigned tinyint and have user permissions for 1,2,4,8,16,32,64,and 128.

More to the point, once you start thinking about them as bitwise flags, stop thinking about them as ints at all, and consider them as the binary values 00000000-11111111

As Scallio says, unless you have a very small list of permissions possible, you’ll want a full int rather than a tinyint.

14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.