MySQL Data Structure for your CakePHP Website – Website Planning

In the first part of MySQL Data Structure for your Website, I wrote a nice simple intro on how you can save data storage and bandwidth by properly setting up your application’s database.  We’re going to continue in more depth but also focus on gathering all of the requirements we need.  Since CakePHP loves to tutorialize about a blog, I figure we’ll work towards that.  But this isn’t any old blog tutorial.  Rather, think of it as the beginning of something bigger; another Wordpress, if you will.  The great thing about CakePHP is no matter how small you start you can always go big!

We decided we were going to set up our Users with the following data structure:

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

We’re going to use CakePHP’s Model Associations to define the relationship between States and Users.  Our plan is to pull State.StateFull or State.StateAbbr from States by matching the User.state_id to State.ID.  That is all we have to do for now. Before we get deeper let’s analyze our website idea.  We first must define our basics.  What is the purpose of a blog?  The ability to write and edit content instantaneously.  We want our blog articles organized by their content-type or category.  We want the ability to add related meta tags.   We want to manage who can read what articles.  We want to be able to save a rought draft or alter the Publish date.  We want the blog to allow comments.  From registered members or occasional visitors?  Do we want to monitor our stats?  Do we want polls?  Do we want to integrate Google Maps?

Now we’re getting a too far ahead of ourselves.  For now we’ll stick to being able to write and post content with the ability to comment on certain posts and comments.  We also want the ability to add meta tags (search engine optimization is paramount for any website).

Our posts will be assigned to categories. Each category can have multiple posts. But a post can only have one category. Since the categories are of most importance in the structure it is only fitting that this is the first table we create:

Categories data structure

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

Keyname	Type	Field
PRIMARY	primary	ID
name	unique	name

And that’s that!  Some of you may wonder why I chose to name Categories.name as opposed to Categories.title or Categories.category.  CakePHP likes fields named name.  I’ll show you how it is beneficial later on.  And it will make things so much easier.

Why do we not create more fields in the Category table?  We can.  CakePHP also likes DATETIME fields such as modified and created.  If you have those fields created in your database then whenever you create a new Category CakePHP automatically populates Category.created with the date and time.  When you edit that Category CakePHP automatically populates Category.modified with the date and time.  You do not have to add any additional coding.  Let’s modify Categories:

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

Notice for both Categories.modified and Categories.created can have Null values.  CakePHP prefers this and will generate unsuspected errors of Null is set to No.

If we want to add more fields such as a brief description we can later.  Remember what I said earlier, the nice thing about CakePHP is that we can always start off small and expand big.  Since we are just getting started, let’s keep it simple.  Also, we’re going to avoid SQL queries right now.  We are still setting up our tables.  Showing the queries will come in the next article.

Pages: 1 2