Posted on January 28, 2013 by
Spread the word...

For last few months I’ve been working on our internal project management system. As it is a quite large app, I had to ensure that I used clean conventions and scalable tools. The decision had been made that the core of the application will based on CakePHP framework and MySQL database engine. From my previous experiences I can say that to keep whole implementation process clear it’s very important to always have actual data model diagram – from the beginning to the very end. That’s why one of my favorite software-development support applications is “MySQL Workbench” and its EER (extended entity-relationship) diagram design feature. I find it very useful especially if you stick to some basic rules of CakePHP database objects naming conventions. You can set up these rules in the main menu “Model->Model Options” properties tab.

To start designing database for CakePHP application firstly you need to be aware of distinctions between database relation types in MySQL  and model associations in CakePHP.

MySQL tables relationshipCakePHP objects association
one-to-one non-identifying relationshiphasOne
one-to-many non-identifying relationshiphasMany, belongsTo
one-to-one identifying relationshipnot supported*
one-to-many identifying relationshipnot supported*
many-to-many identifying relationshiphasAndBelongsToMany

* As there is no multiple-column primary key support in CakePHP it can be said that there is no support for 1:1 identifying and 1:n identifying relationships. The only exception is associated model with one column primary key which is relation foreign key at the same time

Screenshot below presents simple EER diagram (I have highlighted the Workbench relationships creator area in red):

sample
Relationship descriptionMySQL tables relationshipCakePHP objects association
Each user may have only one profile page.
Each profile page is associated with one user.
users <-> profile_pages
(one-to-one non-identifying relationship)
User hasOne ProfilePage
Each article can be commented many times.
Each comment is created by a User.
Each user can create many comments.
articles <-> comments
(one-to-many non-identifying relationship)

users <-> comments
(one-to-many non-identifying relationship)

Article hasMany Comment,
Comment belongsTo Article,

User hasMany Comment,
Comment belongsTo User

Each user can like many articles.
Each article can be liked by many users.
users <-> users_articles <-> articles
(many-to-many identifying relationship)
User hasAndBelongsToMany Article

 

As I mentioned before it’s worth to use CakePHP naming convention during the design process. Once you’ve got proper database schema you can easily generate plain source code of your application. In other words MySQL Workbench + CakePHP code generator can be used as ORM (Object-relational mapping) design tool.

I hope you enjoyed reading and find article useful.  Please don’t hesitate to leave your comments below.

Leave a Comment

Your email address will not be made public or shared. Inappropriate and irrelevant comments will be removed.

  1. Arslan says:

    Great sharing Tomasz. You have helped a lot to make my concepts about CakePHP + MySQL Work bench. Now I am able to use that software to ease my database work. I really appreciate your sharing. I have also shared your article on my G+ and facebook pages. Thanks!