10.1 Defining Your Database Schema

The first version of the address book contains only a person's first name, last name, and email address. However, you want to leave open the option of adding additional fields at a later time.

You also know that the data is accessed for reading, as you look up existing records. Writes will be relatively infrequent, but will definitely occur.

Based on those criteria, SQLite is a good choice as a database. Your dataset isn't gigantic, nor do you need advanced features such as replication. SQLite also retrieves data very quickly, so it's perfect for pulling records out of the database.

The next step is writing a database schema and creating a new database, as shown in Example 10-1.

Example 10-1. Database schema for address book
$sql = <<< _SQL_


                 id INTEGER PRIMARY KEY, 

          firstname TEXT, 

           lastname TEXT, 

              email TEXT);

CREATE INDEX people_firstname_index ON people(firstname);  

CREATE INDEX people_lastname_index  ON people(lastname);

CREATE INDEX people_email_index     ON people(email);


$db = new SQLiteDatabase('addressBook.db');


Example 10-1 creates a new database file called addressBook.db and adds a people table with four fields: id, firstname, lastname, and email.

The id field is an integer and the primary key. The other three fields are simply defined as TEXT. Since you need to search against the text fields, you create indexes for all firstname, lastname, and email fields using the CREATE INDEX command.

See Chapter 4 for more on SQLite, and see Section 4.4.1 in that chapter for more on indexes specifically.