How to structure some data in database for easy retrieval later

Question

I have a set of checkboxes that an Admin can tick to give users privleges. In my case, the Admin can click these checkboxes: Image on ImageShack. These checkboxes will allow users to view data for those countries/cities.

I am trying to rack my brains to do the folowing:

1) How to best insert this permissions into my database 2) How to later work out what permissions each user is allowed

At the moment, I am thinking of just adding lots of columns for each checkbox into my db and put a 1 or 0 there. Then check these using lots of if functions!! This is going to be a time waster when coding.

Is there anything else I can do? I appreciate any help!

Answer 1

You could have a permissions table with the users name and then a number of columns for each permission.

This prevents you from filling up your users table with all this data (since you probably only need it in a limited capacity), but allows you to look at each permission and see what permissions a user has:

e.g

For Each Field
     User.Permission[Field] = Field
Next Field

As long as you have a specific variables in the User structure that allows you to keep track of permissions once they are loaded (a map/associative array would be good for this.)

Answer 2

You need the SET data type. A SET is more or less a bit field where each bit has a name:

ALTER TABLE user ADD COLUMN permissions SET(
    'States',
    'Cities',
    'Africa',
    'Asia',
    'Australia',
    'Europe',
    ...
);

Selecting users that can view data for asia:

SELECT * FROM user WHERE FIND_IN_SET('Asia',permissions) > 0;

Or selecting all users that can view Asia and Europe but not Africa:

SELECT * FROM user WHERE permissions & 40 = 40 AND !(permissions & 4)

I would further define these constants in PHP, then your code should be quite readable:

define('PERMISSION_STATES', 1);
define('PERMISSION_CITIES', 2);
define('PERMISSION_AFRICA', 4);
define('PERMISSION_ASIA', 8);
define('PERMISSION_AUSTRALIA', 16);
define('PERMISSION_EUROPE', 32);
# ...

# Check if user can view data for Africa
if ($user['permissions'] & PERMISSION_AFRICA) {
    # ...

# Check if user can Asia and Europe but not Africa
if ($user['permissions'] & PERMISSION_ASIA
    && $user['permissions'] & PERMISSION_EUROPE
    && !($user['permissions'] & PERMISSION_AFRICA)) {
    # ...

Answer 3

I would go with the normalized database approach: you have a many-to-many relationship between users and permissions. You need an intersection table to store this information.

CREATE TABLE UserPermissions (
  user_id INT NOT NULL,
  perm_id INT NOT NULL,
  PRIMARY KEY(user_id, perm_id),
  FOREIGN KEY (user_id) REFERENCES Users (user_id),
  FOREIGN KEY (perm_id) REFERENCES Permissions (perm_id)
);

When you enter permissions for a user, enter one row for each permission:

INSERT INTO UserPermissions (user_id, perm_id) VALUES
 (1234, 1), -- for Cities
 (1234, 5), -- for continent North America
 (1234, 17); -- for country Canada

Now it's easy to query all users who have permission for North America:

SELECT Users.*
FROM Users JOIN UserPermissions USING (user_id)
WHERE perm_id = 5;

You can also fetch a comma-separated list of permissions for a given user:

SELECT user_id, GROUP_CONCAT(Permissions.Name) AS perm_list
FROM UserPermissions JOIN Permissions USING (perm_id)
WHERE user_id = 1234
GROUP BY user_id;