Designing Table Structure for a MySQL Database

Continuing to learn how to program with the PHP language. As a 'First PHP Project' the course is taking me thru the steps of designing & building a Content Management System (CMS). From scratch.

PHPI've long been interested in CMS'es (e.g. Drupal + Joomla + MODx) .. from a USER and Administrator standpoint. Approaching it from the developer's perspective has been very enlightening.

From a developer's point of view, a CMS can be reduced to two main functions:

  1. get data INTO a database (.. via autheniticated users logged into the staff area)
  2. retrieve data FROM the database (.. and display it on the web looking pretty, styled with CSS)

Whenever I've installed a CMS myself (such as Drupal or MODx), I've always first CREATED a database (in cPanel) .. with 3 specific things:

  1. name of database
  2. user_name
  3. password

But I've never created any tables. (A database consists of a collection of tables.) When you *install* any CMS (such as Drupal), the installer connects to the database specified (using the 3 pieces of info mentioned above) and creates all the tables for you. (Drupal, for example, will create 68 tables in your virgin, tableless database.)

But .. when you design & create your own CMS (from scratch), you have to design & create all the tables yourself. (A new experience for me.)

The tool-of-choice for rolling your own tables in/for a MySQL database is » phpMyAdmin. Configuring the database structure with tables is the first step in creating a PHP-based CMS from scratch (.. after 'blueprinting' the project based on goals and desired features).

••• today's entry continues here below •••

Take, for example, the various incarnations of Ye Olde Rad Blog I've posted over the years (since 2003). There have been 4. In reviewing their DATABASES, I can see that:

Again, these are the sizes of the *databases* associated with each blog-installation. This info would suggest that the ENTRIES themselves for all versions except the original .. have been entered into the database. Not sure about the original. Seems difficult to squeeze 350 entries into a 3 MB database, no?

The forum, on the other hand, does not use a database. Altho the boys at YaBB are working (as we speak) on a new version (YaBB 3.0) that *will* support databases. [SMF is a PHP-based fork of YaBB that DOES support databases. ]

MySQL databaseIn order to store entries with more than 255 characters in a database (I learned) you must select the 'TEXT' data-type .. which allows a virtually unlimited sized entry.

I've always wondered how they got such large entries into the database and if they had to break them up first. [ The VARCHAR data-type allows up to 255 characters. ]

The REASON for using a database this way in conjunction with a CMS (or a blog) .. is so that users with little or no knowledge of HTML/CSS will be able to add content to a web site. I'll share more on this later, but it seems like the whole purpose of learning PHP is twofold:

  1. get data into a database
  2. retrieve data from a database

For more along these lines, here's a Google search preconfigured for the query » create mysql database tables phpmyadmin php cms

About this Entry

This page contains a single entry by Rad published on May 30, 2010 5:30 AM.

Cormac McCarthy & The Road was the previous entry in this blog.

The Deepwater Horizon Memorial is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.