How to structure some data in a database for easy retrieval later
I have a set of checkboxes that an administrator can specify to grant privileges to users. In my case, the admin can click these checkboxes: Image on ImageShack . These checkboxes will allow users to view data for those countries / cities.
http://img205.imageshack.us/img205/4170/screenshotao.png
I am trying to puzzle myself to do the following:
1) How best to insert these permissions into my database 2) How to determine what permissions are allowed to each user.
At the moment I'm thinking about just adding a lot of columns for each checkbox to my db and putting a 1 or 0 in there. Then check them out using lots of if functions! When coding, this will be time wasted on time.
Is there anything else I can do? I appreciate any help!
source to share
You need the SET data type . 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',
...
);
Choice of users who can view data for Asia:
SELECT * FROM user WHERE FIND_IN_SET('Asia',permissions) > 0;
Or select all users who can view Asia and Europe, but not Africa:
SELECT * FROM user WHERE permissions & 40 = 40 AND !(permissions & 4)
I would additionally 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)) {
# ...
source to share
I would go with a 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 entering permissions for a user, enter one line 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
It is now easy to query all users with permission for North America:
SELECT Users.*
FROM Users JOIN UserPermissions USING (user_id)
WHERE perm_id = 5;
You can also get 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;
source to share
You might have a permission table with a username and then a number of columns for each permission.
This prevents you from populating the users table with all of this data (since you probably only need limited capacity), but it does allow you to view each permission and see what permissions the user has:
eg
For Each Field
User.Permission[Field] = Field
Next Field
As long as you have certain variables in your user structure that allow you to keep track of the permissions after they are loaded (a map / associative array is fine for that).
source to share