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...

Answer 1

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.