PHP for Domino Developers, Part 3, Tables

Jake Howlett, 8 October 2003

Category: LAMP; Keywords: MySQL data-types

This article is the third part of a series of articles that aim to introduce the LAMP platform to Domino Developers. In the first two articles I looked at interacting with the MySQL server and uploading files with FTP. The remaining articles will look at how to use PHP to work with the data stored in MySQL. Before we do that however, this article will go in to more detail on MySQL. It will look at creating tables and the different data-types they can store. Before you do any PHP coding for your application you are going to have to plan and design your backend data storage. Best that we cover that before we get in to the code too much.

MySQL from a Notes perspective:

As Domino Developers, the principles involved in working with MySQL tables won't be completely alien. We are all familiar with the idea of field types. e.g Rich Text, Number and Date. Well, creating columns in SQL tables is pretty much analogous to adding fields to a form. The difference with SQL is that we need to create extra columns to store things like the ID of the entry. Whereas Notes is nice enough to take care of the unique identifiers, author name, creation dates and suchlike, we need to do this ourselves in MySQL.

When we plan a Notes database in our heads we first think of the forms required and then plans what fields we will need on each of these forms. With MySQL it's a little more complicated. It's a relational database (RDBMS) system and so we need to plan our tables a little more carefully than we would our Notes forms. Database modeling is a huge topic in itself and not one I even pretend to fully understand. In my examples I will be keeping things simple and confined to one table. However, more complicated applications obviously require more tables. When this is the case you need to also plan the relationships between them. This is where you start getting in to the realm of Entity Relationship (ER) modeling and things like cardinality rear their ugly heads. The perfect design of a relational database is an art form in itself. An art form I am yet to fully master. For the time being I will keep it simple so we can move on to PHP.

Building our database:

Keeping with my assumption that you are a Domino Developer I have decided to replicate the functionality of a Notes template we should all be familiar with - Personal Journal (journal.ntf). You can download both the R5 and R6 versions of it from the bottom of the article. Although your local client install should include it by default, as should your server. If not then I've created a database using the template on this server, for you to play with. You won't be able to save any documents though. Sorry but I don't want you all uploading files!

I chose this database for its simplicity. It is about as basic as a Notes database gets. One form with a few field types on it and a couple of categorised views. Using this we should be able to cover Forms, Views and Searching before the end of this series of articles.

Note: There are a few features of the template I won't be replicating, so as to keep things simple. These include attachments, categorised views, multiple category journal entries, folders and "trash". All of which could easily be achieved in PHP but are also "out of scope" of these articles. Some of them, like attachments, I will cover in future "advanced PHP" articles.

Let's look at the form used in the Journal database to see what we're working with:

Journal Entry Form

From this we can remove quite a few of the fields that won't be relevant to us. From which I arrived at the following list of fields that we need to store in our MySQL table.

Name	Notes Field Type	MySQL Data Type
Subject	Text	varchar(250)
Categories	Mulit Value Dialog List	varchar(250)
DiaryDate	Date/Time	date
Body	Rich Text	mediumtext
TimeCreated	Date/Time	datetime
TimeModified	Text	datetime

Note: We will need an extra column in our table called entry_id which will act as a unique identifier for each entry.

Creating The Table In MySQL:

With Notes, when you add fields to a form, you don't really have to worry about the way in which they are stored. The user enters the data and it just gets stored in a big storage bucket of data. In MySQL the data is stored in a table that you need to define and relate to any other tables in the database.

The easiest way to create a table is by using the MySQL Control Center I talked about in the first article. Launch it now and we'll get started creating the table to store Journal entries.

First we need to create the database that will hold the table. Make sure you are connected to the server. Then choose New Database from the Actions menu, as in the shot below. Enter the name "journal" in the box that appears and press OK.

Creating Journal Database

A new entry, called journal, should appear in the list of databases in the left hand pain. Double click it to force the client to connect. It should turn green and expand to show the tables branch. Select the tables branch and, from the Actions menu, choose New Table as in the shot below.

Creating Entries Table

One by one, add all the fields listed in the table earlier. You can change their names if you like but it will be easier to follow along with me if you don't. Similar to the Notes convention, characters like space are not allowed. As you add each column, change the properties of things like the maximum length of the varchar subject field to 250 characters. Also add the entry_id field. This one is special as it's the Primary Key. It's important that this column can never have a null entry. It should be an integer that auto increments from a default value of 0.

When done, click save and enter the table name "entries" in the dialog box that appears. You should end up with something that looks like the table in this screen-shot:

The finished entries table

Don't feel too pleased with yourself just yet. We cheated a little in using this GUI table designer. A die-hard SQL database designer would probably be happier coding the SQL required to create it from scratch. For the record, here's the SQL command required to create is. You can download the SQL file from the bottom of this article. There are various ways you can import the SQL to MySQL that I won't go in to for the sake of keeping things simple. After all, if you can create it in an IDE, why bother with the code?


# Host: Epswebsvr01

# Database: journal

# Table: 'entries'

# 

CREATE TABLE `entries` (

`entry_id` int(11) NOT NULL auto_increment,

`subject` varchar(250) NOT NULL default '',

`body` mediumtext,

`category` varchar(250) NOT NULL default '',

`diary_date` date default '0000-00-00',

`time_created` datetime NOT NULL default '0000-00-00 00:00:00',

`time_modified` datetime,

PRIMARY KEY  (`entry_id`)

) TYPE=MyISAM;

As an afterthought I decided to add an index to the table to make searching by the category faster. You can do this in the IDE or you can do it with some simple SQL. Here's how:

ALTER TABLE `entries` ADD INDEX `categories` (`category`);
Whichever way you choose to go about it, you should now have yourself a database and a table, in to which we can start adding and retrieving data. Something like this:

The finished setup

Normalisation:

In trying to keep this example as simple as possible I have designed the table badly. If I were doing it for real I would probably have used two tables. The extra table would be called "categories" and it would simply hold a list of acceptable categories for journal entries. This way we avoid the duplication of data across rows of the entries table. The categories table would have two columns - category_id and category_name. The category_id column would be the key and this is the value that would then be stored in a new column in the entries tables, also called category_id. The two columns would be related. When you load the data for an entry from the entries table you also have to get the category name from the categories table.

There are pros and cons to this approach. It depends how you look at it when you come from a Notes background. With the Notes template, if you edit the name of the category in one document, the rest remain unchanged and you have one extra category in your view. If you wanted to change the name of the category in all documents you would have to write an agent or a view action to do it for you. In SQL, with this extra table, you can edit the name of a category and all entries will then belong to this category, instantly. This is one of the beauties of RDBMS. But then you argue that the way Notes does it is acceptable, in its own way. How you do it is down to what you want to achieve and how you want the database to behave.

Summary:

Database design is a huge area. Well outside the scope of anything I could ever hope to write. If you want to get to understand it fully you will have to read at least a book or two. Which is exactly what I plan on doing. Having had no formal training in the area I can't guarantee that everything I've told you in this article is 100% accurate.

What the article should have done is get you to the point where you have a basic table ready for data input. With this we can move on the next part of this series of articles where we actually start talking about PHP coding. More importantly, how we create the forms and views required for date input and retrieval.

Further reading:

More information about MySQL column types
PHPBuilder.com article on Database Normalization