Performanced Optimized CAKEPHP ACL Schema

gistfile1.sql

/* ACL Tables */

CREATE TABLE acos (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	parent_id INT DEFAULT NULL,
	model VARCHAR(255) DEFAULT '',
	foreign_key INT UNSIGNED DEFAULT NULL,
	alias VARCHAR(255) DEFAULT '',
	lft INT DEFAULT NULL,
	rght INT DEFAULT NULL
) ENGINE = INNODB;
-- table name is quoted because it is a reserved word
CREATE INDEX idx_acos_lft_rght ON `acos`(lft,rght);
CREATE INDEX idx_acos_alias ON `acos`(alias);
CREATE INDEX idx_acos_model_foreign_key ON `acos`(model(255),foreign_key);

CREATE TABLE aros (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	parent_id INT DEFAULT NULL,
	model VARCHAR(255) DEFAULT '',
	foreign_key INT UNSIGNED DEFAULT NULL,
	alias VARCHAR(255) DEFAULT '',
	lft INT DEFAULT NULL,
	rght INT DEFAULT NULL
) ENGINE = INNODB;
-- table name is quoted because it is a reserved word
CREATE INDEX idx_aros_lft_rght ON `aros`(lft,rght);
CREATE INDEX idx_aros_alias ON `aros`(alias);
CREATE INDEX idx_aros_model_foreign_key ON `aros`(model(255),foreign_key);

CREATE TABLE aros_acos (
	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
	aro_id INT UNSIGNED NOT NULL,
	aco_id INT UNSIGNED NOT NULL,
	_create CHAR(2) NOT NULL DEFAULT 0,
	_read CHAR(2) NOT NULL DEFAULT 0,
	_update CHAR(2) NOT NULL DEFAULT 0,
	_delete CHAR(2) NOT NULL DEFAULT 0
) ENGINE = INNODB;
-- table names are quoted because they are reserved words
CREATE UNIQUE INDEX idx_aros_acos_aro_id_aco_id ON `aros_acos`(aro_id, aco_id);
ALTER TABLE aros_acos ADD CONSTRAINT FOREIGN KEY (aro_id) REFERENCES `aros`(id);
ALTER TABLE aros_acos ADD CONSTRAINT FOREIGN KEY (aco_id) REFERENCES `acos`(id);