Userwise Menu Design from mysql Database.

I was designing a database driven application. I thought to manage following utilities through the application.

1. User Creation.
2. Menu Design from database.
3. Giving rights to access the menu from the database.

To accomplish this, I have to design first the database tables. Here is an brief idea how I come near to my requirements.

1) Table design for User Creation.

CREATE TABLE `users`
(
`userid` smallint(3) unsigned NOT NULL auto_increment, // The Unique Identity for each user.
`username` varchar(30) NOT NULL // User Name
`userpass` varchar(32) NOT NULL, // Password for the user. MD5 is algorithm can be used.
`division` varchar(30) default NULL, // Division of user to which he/she belongs.
`name` varchar(50) default NULL, // Name of the User.
`label_to_print` varchar(500) default NULL, // Required if you want print any data for report printing.
`valid_from` date default NULL, // User can login from this Valid_from Date
`valid_to` date default NULL, // User can login from this Valid_to Date
`ipaddress` varchar(200) default NULL, // Required if you want to put IP restriction for any user.
`created_by` varchar(30) default NULL, // User created by the the user.
`entry_date` datetime default NULL, // Data of user creation.
`status` varchar(10) default NULL, // Status of user. User can login only if the status is Active.
PRIMARY KEY (`userid`),
UNIQUE KEY `username` (`username`)
)

2) Menu Design from Database.
I have created two tables to design the menu.
1. Table mainmenu to display root menu
2. Table submenu to leaf menu

CREATE TABLE `mainmenu` (
`mainmenuid` int(11) NOT NULL auto_increment, // Unique menu id
`mainmenuname` varchar(50) default NULL, // Main Menu name to display.
`mainmenumodule` varchar(50) default NULL, // Main menu module name to which it belongs.
`mainmenustatustext` varchar(255) default NULL, // Status message text.
`image` varchar(255) default NULL, // Image to display on menu.
`height` int(11) default NULL, // Image height
`width` int(11) default NULL, // Image width.
`userid` varchar(50) default NULL, // The record entered by the user.
`entry_date` datetime default NULL, // The record entry date.
UNIQUE KEY `mainmenuid` (`mainmenuid`)
)

CREATE TABLE `submenu` (
`mainmenuid` int(11) NOT NULL, // This column refer to mainmenu Table.
`screenid` int(11) NOT NULL, // Screen id. It should be sequential.
`print_order` smallint(6) default NULL, // Order in which to display it
`screename` varchar(50) default NULL, // Leaf menu name to display.
`screenlink` varchar(255) default NULL, // Link or filename to load the file
`image` varchar(255) default NULL, // Image file name to display
`height` int(11) default NULL, // Image height
`width` int(11) default NULL, //Image width.
`screenstatustext` varchar(255) default NULL, // Status bar message
`active` varchar(20) default NULL, // Screen is Active or Inactive.
`userid` varchar(20) default NULL, // The record entered by the user.
`entry_date` datetime default NULL, // The record entry date.
PRIMARY KEY (`mainmenuid`,`screenid`)
)
3) Table to give rights to access the menu from the database.
CREATE TABLE `user_right` (
`mainmenuid` int(11) NOT NULL, // MainMenu id
`screenid` int(11) NOT NULL, // Screen id
`username` varchar(20) NOT NULL, // Username who has access to mainmenuid+screenid
`valid_from` date default NULL, // Rights to access the screen from date.
`valid_to` date default NULL, // Rights to access the screen to date.
`userid` varchar(20) default NULL, // The record entered by the user.
`entry_date` datetime default NULL, // The record entry date.
PRIMARY KEY (`mainmenuid`,`screenid`,`username`)
)

Now create a simple view to fetch the data from the above table.
DELIMITER $$
DROP VIEW IF EXISTS `menu`$$
CREATE VIEW `menu` AS select `a`.`username` AS `username`,`c`.`mainmenuname` AS `mainmenuname`,`b`.`screename` AS `screename`,`b`.`screenlink` AS `screenlink`,`a`.`valid_from` AS `valid_from`,`a`.`valid_to` AS `valid_to` from ((`user_right` `a` join `submenu` `b`) join `mainmenu` `c`) where ((`a`.`mainmenuid` = `c`.`mainmenuid`) and (`a`.`screenid` = `b`.`screenid`) and (`a`.`mainmenuid` = `b`.`mainmenuid`)) order by `c`.`mainmenuid`,`b`.`print_order`$$
DELIMITER ;

Now insert the data in appropriate tables and you have done. Just execute the following query to access the screen rights for a single user.

Select * from menu where username="";

girishpadia@gmail.com