# Is this the right db design for the most flexible and modular CMS with user management in CakePHP?

I would like to ask you guys if you could review my database design:

I think it is quite self-explanatory, but to be absolutely clear:

My goal is to make an application which has a super flexible user management (which is why the groups are in tree-form and the groups and users have a habtm relationship) and a super modular way to build pages (which is why the pages consist of widget-blocks).

The reason I made users and profiles separate is because the users table will not change and is only needed for authentication and authorization. However, the profiles table will change according to the wishes of the client. So it might not have a signature, but an avatar field instead. Or maybe it will be completely empty / not exist at all.

A widget could be anything, it could be a poll, it could be a piece of content, it could be a navigation, it could be a collection of comments, whatever.

The reason I chose to make subdomains, locales and layouts separate tables instead of just putting the names into pages is because I want to limit the options that are available to the client. Just because I have a three-columns.ctp in my layouts folder doesn't necessarily mean I want the client to be able to choose it.

Same goes for the widgets. And besides limiting choice, not every plugin, controller and action in my plugins-folder is a widget, so I need a table to clarify which are.

A block is a widget on a page which sits in a container (e.g. the right column in a 3 column layout) at a particular position which is decided by the index (lower index means higher).

So that's my explanation, what do you guys think? Is this as good as it can be? Or do you have (a) suggestion(s) to make it even more flexible and modular.

Oh and to be clear, the widgets will of course have their own tables to store the information they need to store.

 On request, here is the SQL code:

-- ---
-- Globals
-- ---

-- SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- SET FOREIGN_KEY_CHECKS=0;

-- ---
-- Table 'users'
--
-- ---

DROP TABLE IF EXISTS users;

CREATE TABLE users (
id INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
username VARCHAR(32) NOT NULL,
password VARCHAR(32) NOT NULL,
created DATETIME NOT NULL,
modified DATETIME NOT NULL,
PRIMARY KEY (id)
);

-- ---
-- Table 'groups'
--
-- ---

DROP TABLE IF EXISTS groups;

CREATE TABLE groups (
id INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
parent_id INTEGER NULL DEFAULT NULL,
name VARCHAR(32) NOT NULL,
lft INTEGER NOT NULL,
rght INTEGER NOT NULL,
created DATETIME NOT NULL,
modified DATETIME NOT NULL,
PRIMARY KEY (id)
);

-- ---
-- Table 'groups_users'
--
-- ---

DROP TABLE IF EXISTS groups_users;

CREATE TABLE groups_users (
id INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
group_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
PRIMARY KEY (id)
);

-- ---
-- Table 'profiles'
--
-- ---

DROP TABLE IF EXISTS profiles;

CREATE TABLE profiles (
id INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
user_id INTEGER NOT NULL,
email VARCHAR(64) NULL DEFAULT NULL,
birthday DATE NULL DEFAULT NULL,
signature MEDIUMTEXT NULL DEFAULT NULL,
PRIMARY KEY (id)
);

-- ---
-- Table 'pages'
--
-- ---

DROP TABLE IF EXISTS pages;

CREATE TABLE pages (
id INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
parent_id INTEGER NULL DEFAULT NULL,
subdomain_id INTEGER NOT NULL,
locale_id INTEGER NOT NULL,
layout_id INTEGER NOT NULL,
path VARCHAR(128) NOT NULL,
name VARCHAR(32) NOT NULL,
visible bit NOT NULL DEFAULT 0,
lft INTEGER NOT NULL,
rght INTEGER NOT NULL,
created DATETIME NOT NULL,
modified DATETIME NOT NULL,
PRIMARY KEY (id)
);

-- ---
-- Table 'locales'
--
-- ---

DROP TABLE IF EXISTS locales;

CREATE TABLE locales (
id INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
name VARCHAR(3) NOT NULL,
PRIMARY KEY (id)
);

-- ---
-- Table 'layouts'
--
-- ---

DROP TABLE IF EXISTS layouts;

CREATE TABLE layouts (
id INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
name VARCHAR(8) NOT NULL,
PRIMARY KEY (id)
);

-- ---
-- Table 'subdomains'
--
-- ---

DROP TABLE IF EXISTS subdomains;

CREATE TABLE subdomains (
id INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
name VARCHAR(8) NOT NULL,
PRIMARY KEY (id)
);

-- ---
-- Table 'widgets'
--
-- ---

DROP TABLE IF EXISTS widgets;

CREATE TABLE widgets (
id INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
plugin VARCHAR(32) NOT NULL,
controller VARCHAR(32) NOT NULL,
action VARCHAR(32) NOT NULL,
PRIMARY KEY (id)
);

-- ---
-- Table 'containers'
--
-- ---

DROP TABLE IF EXISTS containers;

CREATE TABLE containers (
id INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
layout_id INTEGER NOT NULL,
name VARCHAR(8) NOT NULL,
PRIMARY KEY (id)
);

-- ---
-- Table 'blocks'
--
-- ---

DROP TABLE IF EXISTS blocks;

CREATE TABLE blocks (
id INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
page_id INTEGER NOT NULL,
container_id INTEGER NOT NULL,
widget_id INTEGER NOT NULL,
parameters BLOB NULL DEFAULT NULL,
index INTEGER NOT NULL,
created DATETIME NOT NULL,
modified DATETIME NOT NULL,
PRIMARY KEY (id)
);

-- ---
-- Foreign Keys
-- ---

ALTER TABLE groups ADD FOREIGN KEY (parent_id) REFERENCES groups (id);
ALTER TABLE groups_users ADD FOREIGN KEY (group_id) REFERENCES groups (id);
ALTER TABLE groups_users ADD FOREIGN KEY (user_id) REFERENCES users (id);
ALTER TABLE profiles ADD FOREIGN KEY (user_id) REFERENCES users (id);
ALTER TABLE pages ADD FOREIGN KEY (parent_id) REFERENCES pages (id);
ALTER TABLE pages ADD FOREIGN KEY (subdomain_id) REFERENCES subdomains (id);
ALTER TABLE pages ADD FOREIGN KEY (locale_id) REFERENCES locales (id);
ALTER TABLE pages ADD FOREIGN KEY (layout_id) REFERENCES layouts (id);
ALTER TABLE containers ADD FOREIGN KEY (layout_id) REFERENCES layouts (id);
ALTER TABLE blocks ADD FOREIGN KEY (page_id) REFERENCES pages (id);
ALTER TABLE blocks ADD FOREIGN KEY (container_id) REFERENCES containers (id);
ALTER TABLE blocks ADD FOREIGN KEY (widget_id) REFERENCES widgets (id);

-
From the FAQ: Does my question contain code? (Please include the code in the question, not a link to it) –  Donald.McLean Aug 2 '12 at 8:49
I thought an image or diagram is much more clear than a bunch is SQL code. And I can't insert an image cause I'm too new so I have to make it a link. –  Evert Aug 2 '12 at 10:11
I understand that you're trying to work within your current limitations. The code requirement is there because this question is expected to hang around for as long as Stack Exchange exists and there is no guarantee that any code or images you link to will be around for that long. Moderators sometimes close questions that do not meet the posted requirements (such as having code). I would recommend leaving the link to the image, but include the SQL. –  Donald.McLean Aug 2 '12 at 13:27
Okay, I added the code. :-) So do you have any tips? (A) –  Evert Aug 2 '12 at 13:52
In my experience, the terms most flexible and best / right / optimal rarely belong in the same sentence ;) –  Peter Mar 6 '13 at 14:24

Sorry for having this as an answer, should be a comment but not enough rep.

#1 in Donald McLean's answer could be solved with Acl, the database's structure looks well set up for it.

#2 is often solved CakePHP-wise by using a different layout... But that also relies on #3, content.

I'm seconding #3, that's a problem.

-