MySQL Data Structure for your CakePHP Website – Building Associations

In MySQL Data Structure for your CakePHP Website – Site Planning, I used the CakePHP concept of building a blog using the popular PHP framework. However, different from what you may have read in the manual, I wanted to go into greater detail about the processes involved. Primarily, in the previous article we set up our database tables which were Categories, Posts, Comments, Users, and States. You were asked to consider the use of Metas that we could store the Posts Meta information (title, description, keywords, etc.). Metas will require relations, or associations via CakePHP’s built-in classes to Posts. This may seem complicated at first. Once you get the hang of model associations, though, you’ll find yourself writing these arrays in no time.

Let’s reanalyze our tables created thus far:

Users data structure

Field	Type	Null	Attributes	Default	Extra
ID	smallint(5)	No	UNSIGNED		auto_increment
Name	varchar(50)	No
Password	char(32)	No
City	varchar(50)	No
state_id	tinyint(2)	No	UNSIGNED
Zip	char(5)	No

Keyname	Type	Field
PRIMARY	primary	ID
name	unique	name

State data structure

Field	Type	Null	Attributes	Default	Extra
ID	tinyint(2)	No	UNSIGNED		auto_increment
StateFull	varchar(14)	No
StateAbbr	char(2)	No

Keyname	Type	Field
PRIMARY	primary	ID
StateAbbr	unique	StateAbbr

Categories data structure

Field	Type	Null	Attributes	Default	Extra
ID	tinyint(3)	No	UNSIGNED		auto_increment
name	varchar(75)	No
modified	datetime	Yes
created	datetime	Yes

Keyname	Type	Field
PRIMARY	primary	ID
name	unique	name

Posts data structure

Field	Type	Null	Attributes	Default	Extra
ID	smallint(5)	No	UNSIGNED		auto_increment
category_id	tinyint(3)	No	UNSIGNED
user_id	smallint(5)	No	UNSIGNED
name	varchar(75)	No
post	text	No
modified	datetime	Yes
created	datetime	Yes

Keyname	Type	Field
PRIMARY	primary	ID
name	unique	name

Comments data structure

Field	Type	Null	Attributes	Default	Extra
ID	mediumint(10)	No	UNSIGNED		auto_increment
post_id	smallint(5)	No	UNSIGNED
user_id	smallint(5)	No	UNSIGNED
comment	text(255)	No
modified	datetime	Yes
created	datetime	Yes

Keyname	Type	Field
PRIMARY	primary	ID
name	unique	name

Five tables – several fields each table. Looking at the info in this aspect, it’s very easy to see why associations can be intimidating. But we’ve already done the hard part. Let’s review exactly what we have set up.

We have Users.state_id. This, obviously, is where we will hold the ID for State retrieved from States. CakePHP will do the hard work for us. All we had to do was create the table and create the association which we will do shortly. Our next association comes in the Posts table. We do not make an association in our Categories table because we don’t need to. Yes, Categories hasMany Posts. But, what are we going to do? Fill each Categories with every post_id associated with it? We don’t have to. Rather, by creating a field categories_id in Posts, CakePHP will make the connection as long as we set up our associations correctly. Next we have to create our associations in Comments. This is where it gets a bit tricky. Comments belongsTo User and belongsTo Post. The thought may be to do a hasOne association. But Comments doesn’t have one User or Comments doesn’t create a User. User creates a Comment. You could say User hasMany Comment. We do not need to create a comment_id in Users. CakePHP will recognize the relations when we create the associations. We also have related Comments to Posts by creating posts_id.

Finally, we have Metas. Again, all we need to do is assign a posts_id to each Meta record. We’ll use a belongsTo statement in our Meta model. In our Post model, we’ll be using a hasMany association.  You should have set up Metas like the following:

Metas data structure

Field	Type	Null	Attributes	Default	Extra
ID	mediumint(10)	No	UNSIGNED		auto_increment
posts_id	smallint(5)	No	UNSIGNED
name	varchar(75)	No
value	text	No

Keyname	Type	Field
PRIMARY	primary	ID

Let’s clarify this to make sure we’re on the same page. Users hasOne States. Users hasMany Posts and Comments. Categories hasMany Posts. Posts hasOne Categories. Posts hasMany Comments. Comments hasOne Posts. Posts hasMany Metas. Metas hasOne Post.

When we name our association fields in our tables we must not forget that we do so based on the name of the table we are relating and the ID field of that table.  In other words, when we named Users.state_id our association tells CakePHP to retrieve States.ID.  You can tell CakePHP what field to use when you set up your association arrays.  I did not absolutely have to name that field Users.state_id. I could have named that field Users.state.  I would also have had to adjust my model associations to instruct CakePHP of the difference. This adds more code than necessary and should be avoided unless required such as a pre-existing website or poor initial planning.

All in all our query will look like this:

CREATE TABLE `cake_users` (
	`ID` TINYINT( 3 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	`name` VARCHAR( 75 ) NOT NULL ,
	`state_id` TINYINT( 2 ) NOT NULL ,
	UNIQUE ( `name` )
) ENGINE = MYISAM ;

CREATE TABLE `cake_states` (
	`ID` TINYINT( 2 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	`name` VARCHAR( 14 ) NOT NULL ,
	UNIQUE ( `name` )
) ENGINE = MYISAM ;

CREATE TABLE `cake_categories`(
	`ID` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`name` VARCHAR(75) NOT NULL,
	`modified` DATETIME NULL,
	`created` DATETIME NULL,
	UNIQUE( `name`)
) ENGINE = MYISAM;

CREATE TABLE `cake_posts`(
	`ID` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`category_id` TINYINT(3) UNSIGNED NOT NULL,
	`user_id` TINYINT(3) UNSIGNED NOT NULL,
	`title` VARCHAR(75) NOT NULL,
	`post` TEXT NULL,
	`modified` DATETIME NULL,
	`create` DATETIME NULL,
	UNIQUE (`title`)
) ENGINE = MYISAM;

CREATE TABLE `cake_comments`(
	`ID` MEDIUMINT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`post_id` SMALLINT(5) UNSIGNED NOT NULL,
	`user_id` TINYINT(3) UNSIGNED NOT NULL,
	`comment` TEXT NOT NULL,
	`modified` DATETIME NULL,
	`created` DATETIME NULL
) ENGINE = MYISAM;

CREATE TABLE `cake_metas`(
	`ID` MEDIUMINT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
	`posts_id` SMALLINT(5) UNSIGNED NOT NULL,
	`name` VARCHAR(75) NOT NULL,
	`value` TEXT(255) NOT NULL
) ENGINE = MYISAM;

You’ll notice I prefixed all of my table names with “cake_”. This is according to my own CakePHP config file which I have edited. Remember to change that when pasting your code else you will have errors.

Now we can start creating our Controllers and Models. We won’t worry about Views for right now. We have an easier way to mandate that. And don’t worry about inputting data. We don’t need to mess with that right now. Let’s first worry about getting our associations set up.

There are four kinds of model associations with CakePHP: hasOne, hasMany, belongsTo, and hasAndBelongsToMany. It’s simple when you put it in perspective. Somehow, people seem to make it too complicated. Let’s use Users and Posts as an example. Could we say Users hasOne Posts? Well, perhaps. For a new Users anyway. But once Users posts more than one Posts, what will happen? No errors will generate. CakePHP will do exactly what we instructed it to do. Users hasOne Posts therefore our automatic query will return one result. Will an error generate if we use Users hasMany Posts but Users only has one Posts? No. Only one result will be returned. So in our models directory, we can create our Users model and set the association:

var $hasMany = 'Posts';

That’s not the full extent we can take our associations. But it will work for what we want to do right now. Notice the plural naming convention of the variable’s value. We gave it the name of our Controller class and/or our database table. What about all of the extra junk in the CakePHP manual about setting classNames and foreignKeys? Simply put: we didn’t have to. Remember earlier when I discussed why we named the Users.state_id the way we did? It’s an automatic process handled in the CakePHP framework. By setting var $hasMany = ‘Posts’, we’re telling CakePHP that we did our due diligence and set up our fields appropriately. In other words, Posts has a field called user_id. And by creating that field we created the first part of our association. CakePHP takes

var $hasMany = 'Posts';

and through the built-in functions searches Posts.user_id and returns all results where Posts.user_id = User.ID. We’ll get into the finer details such as classNames and foreignKeys later. Let’s be happy with what we’ve got!

Now, we have to work on States and Comments table. We know that

var $hasMany = 'States';

won’t work. Why? Well, for one, States does not have a user_id field. So, why didn’t we create it? Because Users doesn’t hasMany States; Users hasOne States. This may seem very obvious. But I’ve seen too many posts on groups from programmers asking why their associations aren’t working. This typically tends to be the reason; they’re not using the right associations in the right models. We know Users hasOne States. But, States also hasMany Users (it’s illogical to assume we’ll only have 50 unique users from each of the states). Do we need to define a hasMany association in our State model? No. Are we going to go through the hassle of creating a Controller so our users can view every state for which we have registered users, and filter those users by states? We can do that elsewhere; our User model, for starters. It’s unnecessary. If we did, however, then yes, we would create the association in the State model. For the purposes of our application we only need to define an assocation in our User model.

var $hasOne = 'States';

We are saying User model hasOne States. CakePHP will adjust it’s SQL query accordingly and return one result for each Users where Users.state_id = States.ID.

Our Comments will just about be the same. Do we want the ability to post all of our users comments in the Users Controller? Not really. So, technically, we can skip this association. After all, we’ll create it in the Comments Controller and pull Users into our Comments model.

Are you noticing a theme here? While we can go through all of the tables and make all of the associations we’ve set ourselves up to define, we don’t need to! Sure, we can create a Users hasMany Comments association. But unless we want to post all of that Users Comments in our Users Controller, we don’t have to. The other part of associations is: where do you want the data to appear?

Let’s say we did create a User hasMany Comments in our Users Controller. What would happen? CakePHP by default would return all fields in Comments and make them available for display in Users Controller. Again, if we wanted to display comments, or even posts Users has contributed to the site, we can do this. But, we don’t want to (at least, not now). We could set up our hasMany array and define the key fields to return nothing. But, by doing so we would think to leave the key fields value set to empty, correct? Yet, that, in turn, returns everything.  There is a way to get around this. But don’t worry about all of that right now! None of this is that difficult. Should we wish to change it in the future (and we will), it will be very easy.

Point being, know what you need to do but also know your limits. Building a website requires savings in every possible place. And you have to plan accordingly to know what data you want, where you want it, and what you’re going to do with it.

I believe you can continue with the associations on your own. Remember, don’t try to overestimate it. This stuff is easy. The hard part is over with: you set up your tables and set up the fields. Now it’s all a question of writing the associations in the models. So, go do it! In our next article, we’ll go over it and make sure we’re all on the same page. If you feel it makes it easier to create sample data, go ahead and setup your Controllers and Views. We can easily edit it later. And it’ll be a great learning experience for you. If you have it set up correctly, you should be able to add a User. Select their State. Write a Post under that User. Write a comment. And all of your information should come automatic. Ok, maybe we’re getting too far ahead of ourselves. Just worry about the associations right now.