Make Database driven Menu items visible on a Per User basis using PHP and MySQL?

Question

I am building a menu for an admin panel using PHP and MySQL. Each menu item is a MySQL record.

I need to be able to set the visibility of each menu record/item on a per user basis.

I realize that most people would instead do it on a "user group" basis, however my boss requires that instead each menu item is on an individual per person basis to determine who can view which menu items.

I know how to pretty easily make a database driven menu with visibility based on a per user group basis however I am not sure how to go about doing it on a per user basis?

If there is 20 menu items and 20 users in a database. I will need to be able to set a setting/permission to indicate if each and every user can view or not view each individual menu item record.

Any ideas and help on how that might look in a MySQL database structure?

Here is my current Menu SQL Schema...

CREATE TABLE IF NOT EXISTS `intranet_links` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  `description` text NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `permission` varchar(50) NOT NULL DEFAULT 'admin',
  `notes` text,
  `active` int(2) NOT NULL DEFAULT '1',
  `sort_order` int(11) DEFAULT NULL,
  `parent_id` int(10) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

The menu system will look like this image below. There will be Parent Menu items and child menu items which make the Parent menu records behave like a Folder/directory...

enter image description here

Are the permissions different for each of the said 20 users ? — Maximus2012
– Maximus2012, Commented Apr 21, 2015 at 21:26
A table that would JOIN the user's table on the user's id. A column for each of the links in that table where each can accept a BOOLEAN. — Jay Blanchard
– Jay Blanchard, Commented Apr 21, 2015 at 21:27
What about a straight forward map table with 2 cols: user and menu item id's? — Mike Miller
– Mike Miller, Commented Apr 21, 2015 at 21:29
@MikeMiller It sounds like that is probably the only or at least most practical route to go, was hoping there was some super easy shortcut but this seems practical — JasonDavis
– JasonDavis, Commented Apr 23, 2015 at 16:38
If your working in a framework you can probably find a library to shortcut things. Starts to get involved if you have different levels in your hierarchy.. — Mike Miller
– Mike Miller, Commented Apr 23, 2015 at 16:55

maque · Accepted Answer · 2015-04-21 21:54:28Z

Having user_to_item table seems like obvious solution yet I still would go for group based permissions. Why?

Imagine you have 100K users and you are adding new menu item to be visible by all of them, with user_to_items table you have to do 100K inserts, then when removing permission, again 100K deletes and suddenly your mysql starts to choke by fragmentation. Also you will have definetly some common area to see by everybody and some parts only for admin - again pont for using groups.

My sugestion would be having user_to_group permissions, say 'Server Admin' group and 'Other Services', and then have individual user_to_item table which you will check only then if user has permission to group. Also remember to have something like child<->ancestor relationship, so you can quickly find all childs for certain parent ex:

item_id | name                
 1      |'Server Admin'       
 2      |'phpMyAdmin'          
 3      |'phpMyAdmin_subItem' 

item_id | ancestor_id
1       | null       
2       | 1
3       | 1
3       | 2

this way by searching by ancestor_id = 1 you can quickly find that phpMyAdmin and phpMyAdmin_subItem belongs to Server Admin directory, and then perform your permission checks.

Collectives™ on Stack Overflow

Make Database driven Menu items visible on a Per User basis using PHP and MySQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related