5.1. Planning the Entry Database Table

One of the most important steps with any new application is the planning of the tables that will hold data. This has a huge impact on the ease of scaling our application later. Scaling is the expansion of an application to handle more information and/or users, and it can be a tremendous pain if we don't look ahead when starting a new project.

At first, your blog needs to store several types of entry information to function:

• Unique ID

• Entry title

• Entry text

• Date created

Using a unique ID for each entry in the entry table enables you to access the information contained with just a number. This is extremely helpful for data access, especially if this dataset changes in the future (if you add an "imageURL" column to the table, for example).

The first step is to determine the fields you will need for the entries table. Your table needs to define what type of information is stored in each column, so let's take a quick look at the information each column needs to store:

• id: A unique number identifying the entry. This will be a positive integer, and it makes sense for this number to increment automatically because that ensures the number is unique. You will also use this as the primary method for accessing an entry, so it will double as the primary key for the table.

• title: An alphanumeric string that should be relatively short. You'll limit the string to 150 characters.

• entry: An alphanumeric string of indeterminate length. You won't limit the length of this field (within reason).

• created: The timestamp generated automatically at the original creation date of the entry. You'll use this to sort your entries chronologically, as well as for letting tour users know when an entry was posted originally.

Now it's time to create the database. Do this by navigating to http://localhost/phpmyadmin/ and creating a database called simple_blog using the "Create new database" field on the homepage (see Figure 5-1).

Figure 5.1. Creating a new database in phpMyAdmin

The new database is created after you click "Create." Next, you're shown a confirmation message and given options for interacting with your new database (see Figure 5-2).

Figure 5.2. The simple_blog database confirmation screen and options

The next step is to write the code that creates your entries table:

CREATE TABLE entries
(
    id      INT PRIMARY KEY AUTO_INCREMENT,
    title   VARCHAR(150),
    entry   TEXT,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)

To create the entries table, click the SQL tab at the top of the page and enter the command that creates your table (see Figure 5-3).

Figure 5.3. Creating the entries table in phpMyAdmin

After you click the Go button at the bottom right of the SQL text field, the entries table shows up in the left-hand column of the screen. You can see a table's structure by clicking the table you're interested in (see Figure 5-4).

Figure 5.4. The structure of the entries table in phpMyAdmin

At this point, you have created the entries table, and you're ready to create your input form.