Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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

enter image description here

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.

[edit] 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`);
share|improve this question
    
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
add comment

2 Answers

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.

share|improve this answer
add comment

I'm not a database designer so I can only speak to the question as an application developer.

  1. I don't see any mechanism for limiting access to pages or groups of pages. It would seem like one use of user groups would be to give all members of a group certain privileges for certain pages and/or groups of pages.

  2. The entire mechanism looks like it's based on content rendered for a specific medium. Any deviation in the properties of the medium could cause problems. For example: web pages intended for a desktop browser can get cramped on a laptop and worse on a mobile device. My desktop, for example has a 30" 2560x1600 display - stuff that looks great on it can look very odd on smaller monitors. And what about printing?

  3. I don't see where the actual content is located or what types of content are supported.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.