We have already created the user and groups tables. To store the information of the menu and its options and also the permission that each group has, we need to create two more tables: the menu and permissions tables, as shown by the following screenshot:

On the menu table, we will store all the menu information. As each option of the menu table will be a node of a TreePanel, we will store the information in a way that represents a TreePanel. So, we have an id field to identify the node, a text field as the text that is going to be displayed on the node (in our case, we will store the attribute of the translations file since we are using a multilingual capability), iconCls representing the css class that will be used to display the icon for each node, className representing the alias (xtype) of the class that we are going to instantiate dynamically and open at the central tab panel of the application, and finally the menu_id field representing the root node (if it has one—the module nodes will not have a menu_id field, but the module items will).

Then, as the menu table has an N:N relationship with the groups table, we need to create the permissions table that will represent this relationship. We will learn more about how to assign a user to a group in the next chapter.

So to create the new table, we will use the following SQL script:

USE `sakila` ;

CREATE  TABLE IF NOT EXISTS `sakila`.`menu` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `text` VARCHAR(45) NOT NULL ,
  `iconCls` VARCHAR(15) NULL ,
  `className` VARCHAR(45) NULL ,
  `menu_id` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_menu_menu1_idx` (`menu_id` ASC) ,
  CONSTRAINT `fk_menu_menu1`
  FOREIGN KEY (`menu_id` )
  REFERENCES `sakila`.`menu` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `sakila`.`permissions` (
  `menu_id` INT NOT NULL ,
  `groups_id` INT NOT NULL ,
  PRIMARY KEY (`menu_id`, `groups_id`) ,
  INDEX `fk_permissions_groups1_idx` (`groups_id` ASC) ,
  CONSTRAINT `fk_permissions_menu1`
  FOREIGN KEY (`menu_id` )
  REFERENCES `sakila`.`menu` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_permissions_groups1`
  FOREIGN KEY (`groups_id` )
  REFERENCES `sakila`.`groups` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;
});

And we also need to populate the menu and permissions tables with some data. We can use the following SQL code to create all the modules and menu options that we are going to develop throughout this book. We are also going to grant the user we created in the preceding chapter access to all menu options, as this user is admin. Here's the code that encapsulates what we discussed in this paragraph:

INSERT INTO `menu` (`id`,`text`,`iconCls`,`className`,`menu_id`) 
VALUES 
(1,'menu1','fa fa-group fa-lg',NULL,NULL),
(2,'menu11','xf0c0','panel',1),
(3,'menu12','xf007','panel',1),
(4,'staticData','fa fa-database fa-lg',NULL,NULL),
(5,'actors','xf005','panel',4),
(6,'categories','xf013','panel',4),
(7,'languages','xf1ab','panel',4),
(8,'cities','xf018','panel',4),
(9,'countries','xf0ac','panel',4),
(10,'cms','fa fa-film fa-lg',NULL,NULL),
(11,'films','xf1c8','panel',10),
(12,'reports','fa fa-line-chart fa-lg',NULL,NULL),
(13,'salesfilmcategory','xf200','panel',12);

INSERT INTO `permissions` (`menu_id`,`groups_id`) VALUES 
(1,1), (2,1), (3,1), (4,1), (5,1), (6,1), (7,1),
(8,1), (9,1), (10,1), (11,1), (12,1), (13,1);

Throughout this book, we are going to create the screens of our application, and we will need to run some update queries. For now, whenever we click on any menu option, the application is going to open an empty panel so that we can test it.