To start designing databases for CakePHP application firstly you need to be aware of distinctions between database relation types in MySQL and model associations in CakePHP.
|MySQL tables relationship||CakePHP objects association|
|one-to-one non-identifying relationship||hasOne|
|one-to-many non-identifying relationship||hasMany, belongsTo|
|one-to-one identifying relationship||not supported*|
|one-to-many identifying relationship||not supported*|
|many-to-many identifying relationship||hasAndBelongsToMany|
* 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):
|Relationship description||MySQL tables relationship||CakePHP 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
|Article hasMany Comment,
Comment belongsTo Article,
User hasMany Comment,
|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.