Ok, so the first and really really most important thing when working with cakePHP is to get the database structure right. Of course you can always change tables and fields afterwards but then you have to manually change the model and methods. So try to keep it as final as possible from the beginning.
What helps doing this is a graphical Database Design software. If you are running Windows check the MySQL Workbench out - it’s really nice. If you are running Mac OS like me you can hope that a version for us will be released some time soon…
Anyhow for the applications that I am going to built it will be sufficient to use a sheet of paper. Actually I came up with the DB design already. So first we want to have somer users that can register at the website, so lets create a simple table users that has the following fields (replace mydb with the name of your database):
CREATE TABLE IF NOT EXISTS `mydb`.`users` (
`id` INT NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(20) NULL ,
`password` VARCHAR(10) NULL ,
`email` VARCHAR(60) NULL ,
`created` DATETIME NULL ,
`modified` DATETIME NULL ,
PRIMARY KEY (`id`) )
There are a couple of things to notice here. First tables are always plural and lowercase in cakePHP. You have to follow this (and other) conventions closely so that cakePHP can communicate with the database automatically. Second there needs to be a field called id which works as the primary key. Field names are always lowercase and if you want to work with field names that consist of several words they have to look like this: usernameSomething. Do NOT use username_something! And lastly there are two fields called created and modified. You don’t have to worry about them because cake deals with them automatically - so just add them to all your tables and enjoy the magic of cake!
We also want to have some form of content for our websites. The content may vary according to the different websites we are going to make but basically those are just entries of websites, videopodcasts or advertisments. For this reason I have called the next table entries and it looks like this:
CREATE TABLE IF NOT EXISTS `mydb`.`entries` (
`id` INT NOT NULL AUTO_INCREMENT ,
`user_id` INT NULL ,
`name` VARCHAR(255) NULL ,
`rating` DOUBLE NULL ,
`ratingCount` INT NULL ,
`url` VARCHAR(255) NULL ,
`picture` VARCHAR(45) NULL ,
`views` INT NULL ,
`created` DATETIME NULL ,
`modified` DATETIME NULL ,
PRIMARY KEY (`id`) )
Now comes a little bit of a tricky part: the relation between users and entries. One user can make several entries to the database but one entry can only have one corresponding user. This is called a hasMany association between entries and users. For this reason we have included the column user_id in the table entries.
Next we want to have tags so that we can categorize our entries. The tag table is really very simple and short:
CREATE TABLE IF NOT EXISTS `mydb`.`tags` (
`id` INT NOT NULL AUTO_INCREMENT ,
`tag` VARCHAR(45) NULL ,
`created` DATETIME NULL ,
`modified` DATETIME NULL ,
PRIMARY KEY (`id`) )
At this point in time you might wonder. Where is the association between entries and tags? Good question - at the moment there is none! We will have to create a new table for this purpose. We have to do so because entries and tags have a HABTM association. This wonderful acronym stands for hasAndBelongsToMany - which means that a tag can have several entries and an entry can have several tags at the same time. By adding this table to the database cakePHP will be able to tell that we have this kind of relationship in mind. Be careful to closely follow convention when adding this table. It has to be named entries_tags and consists of the following fields:
CREATE TABLE IF NOT EXISTS `mydb`.`entries_tags` (
`id` INT NOT NULL AUTO_INCREMENT ,
`entry_id` INT NULL ,
`tag_id` INT NULL ,
PRIMARY KEY (`id`) )
It is cakePHP convention to have the table with the higher alphabetical order second (practically tags_entries would work as well). Lastly we want our users to be able to comment on the varies entries. To make this happen we need a table comments:
CREATE TABLE IF NOT EXISTS `mydb`.`comments` (
`id` INT NOT NULL AUTO_INCREMENT ,
`user_id` INT NULL ,
`entry_id` INT NULL ,
`text` TEXT NULL ,
`created` DATETIME NULL ,
`modified` DATETIME NULL ,
PRIMARY KEY (`id`) )
This table has to associations - one with users and one with entries. So each comment belongs to one user and to one entry. When everything is set up we are ready to proceed with Baking the Models!
cakePHP
database, model, MySQL, MySQL Workbench
