A while back, I started a series on CodeIgniter.
Even though it’s been 4 months since I last posted in this series (oops!), these posts have quickly turned into my post popular posts. I figure, give the people what they want. Right?
Setting up the database
We’ll need to set up the database.
NOTE: I’ve written a separate post on getting MAMP set up on Mac.
First things first, let’s talk a little bit about database strategy.
The most important rule about creating databases is making sure they run efficiently. That plays out in 2 ways: (1) making sure there’s no duplicate information and (2) being able to access all the information in your database. What good is it to put information in, if you can’t get it out?!
So, how does this play out?
I have 1 database per site, but, a database can have multiple tables. A table can contain any kind of information, from blog posts to comments. Here’s an example of a table (think in terms of excel grids):
The important thing to note: in this table each row has a unique id. It’s a perfect way to get the information out that we need. I just ask the database:
“What’s the title of the blog post with the ID of 2?”
Have you ever been to a WordPress site and noticed the URL? http://www.mygreatsite.com/?=p=50. That p=50 on the end of the URL is one of the ways you can ask for the ID. If you change the number in the URL, it should serve a different blog post with the corresponding ID.
Onward and upward.
There are several different types of relationships that happen within a database.
You may have a table for blog posts, a table for comments, and a table for categories. You get the idea. Well, how do they all relate to each other? There are a couple different options:
One to One Relationship
This relationship works the same way the name suggests. You have one entry in one table that relates to one entry in another table. Let’s pretend we have a list of users in one table and a list of jobs (admin, editor, writer, or user) in another table. Each person in the database can only have 1 job.
We could add an extra column in the users table to specify what their role is, but that’s not very efficient. What is I accidentally misspelled something? or what about Admin (capital A) verses admin (lowercase a)? Or what if in the future, I wanted to add additional information about those roles to my database? A description of that job? Pages they have access to? Well, then, adding an additional column is definitely not efficient then.
Instead, I add a column that references the ID in the other table.
Ahh, much better!
Many to Many Relationship
Again, the name tells you all you need to know. Many things related to many things. (so profound)
An example, please. We have a table of blog posts and they’re all related to tags. A post can have 1 tag or it can have 50.
So, how does work? We can’t add a column to the blog posts table. We don’t know how many tags each post has. Putting a comma deliminated list in a table cell doesn’t seem to be very efficient. Trying to read the list would require quite a bit of extra code. Same thing with the tags table. We don’t know how many posts will be associated with a given tag. What’s a developer to do?
Well, you can add a relationship table. The sole purpose of that table is to you how 2 tables relate to each other. In this case, the table will tell you the ID of the blog post and the ID of the related tag.
Not too shabby.
The Tables for Our site
When I’m planning out a database for a site, I’ll just list out all the information that I need to keep track of and then I’ll list all the elements and figure out how they relate.
Here is all the different types of information I’ll need. These will eventually turn into my database tables:
- Blog Posts
OK, now all the elements that relate to this type of information:
Pretty easy. Now, how do they all relate? Well, we need to know which blog post the comment is related to. So, I’ll add a “Blog Post ID” to the list. I need to know which author wrote a given post, so I’ll add “Author ID” to the blog post table. I also need to know what category the blog post is listed under (each post can only have one category), so I’ll add “Category ID” to the blog post table as well. Here’s my final structure:
A few things to know about naming
You can’t have any spaces and with the exception of hyphens and underscores, I try and leave symbols out of the picture.
When I’m naming my databases, I try and include a suffix that tells me what kind of database it is. In this case, we’re running Code Igniter, so I’ll name my database awesome_ci. But, if it was a WordPress site, I’d call it awesome_wp, Expression Engine awesome_ee, you get the idea. Why do I do this? Well, I mess with enough platforms, that (1) it’s helpful to tell at a glance and (2) sometimes I’ll actually experiment with multiple platforms for a given site. So I could easily have an awesome_wp and an awesome_ci. This helps me keep everything straight, but again, this is personal preference.
When I’m naming my tables, I try and take cues from CakePHP.
- They use plural names for all table names and singular names for all column names. Instead of calling a table person, it becomes people.
- Relationship tables list the two tables that are being related in alphabetical order. Going back to our tags example from earlier: posts_tags. At a glance, I know which tables contain information and which tables are relationship tables. Again, this goes back to personal preference, but it has also help me develop personal standards.
- With the exception of underscores and hyphens, table names and rows don’t contain symbols
Going back to our list, here’s how I’m going to name everything:
Enough Talking, Let’s Do!
My personal preference is Navicat. But, phpMyAdmin and SequelPro all do the same thing.
In Navicat, right click on the connection and select New Database.
Name your database.
With you database selected and open (in Navicat the icon will turn green next to the database name), you can start adding tables.
It’s pretty straightforward. But, there are a few things that might cause some hiccups along the way.
Every table column must have a type. There are several different types to choose from. For the most part, I stick to 4 different types:
- int – this stands for integer. It can only be a whole number.
- varchar – this is essentially a string. It can contain letters, numbers, and symbols. It’s going to be a lot shorter than a blob.
- blob – this is very similar to a varchar, except it can be much longer. This is great for when you have a blog post or comment.
- timestamp – this is for holding the date. I’ve also used date or time as alternatives, it just depends on what information you need to store. In this instance, I want both date and time, which makes timestamp the perfect solution.
Remember talking about the ID? Well, you need to specify that that’s the key for the table. In Navicat, just select the row, and click on the “Primary Key” heading in the toolbar. You’ll also need to check auto increment (with the ID row still selected). This means that it will automatically increment the value for ID. For example, if the last ID entered was a 6, it knows that the next row is going to be 7.
I checked allow null for the title and body fields. I’m telling the database, it’s OK, if these are left blank, they’ll equal NULL.
Status is kind of weird. You’ll notice we didn’t create a table to hold all the different kinds of statuses we might have. I figured, a post is either on or off. Technically, this isn’t an integer, it’s a boolean. A boolean, can have two values: true or false. In computer language it translates into 0 and 1 (which is where the integer part comes into play). 0 is off/false, 1 is on on/true.
As I side note, have you ever wondered about the symbol on the power switch? —Hey, it’s a 1 and 0!
I try and remember it like this: if it’s off or false, you’ve got nothing, 0.
OK, so just go through our list that we made. Adding tables and rows for the entire database we listed out.
Hopefully this all makes sense. Don’t hesitate to ask questions or add your personal experience in the comments below.