O'Reilly logo

PHP Hacks by Jack D. Herrington

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #34. Design Better SQL Schemas

Most PHP applications use an SQL database. Here are some hints to help you avoid common problems.

PHP applications usually use MySQL databases for the back end. I've worked on a bunch of my own applications, as well as with open source application databases and some commercial ones. In my travels, I have seen a few common problems appear repeatedly; here are a few of those problems, along with easy solutions.

Bad Primary Keys

To find a unique record in a database table, you need a primary key. This is usually a unique, nonrepeating integer that starts at 1. All databases have the ability to handle this for you, but it seems that some engineers aren't aware of it.

Take the simple schema in Example 5-1. You have an author table with an id and a name.

Example 5-1. SQL without a primary key

DROP TABLE IF EXISTS author;
CREATE TABLE author (
		id INT,
		name TEXT
		);

But who ensures that the ID is unique? Often the PHP code that uses a table like this will first do a SELECT to find the maximum value of the ID field, and then create a new record with that value plus 1. But that takes an extra SQL statement and assumes the PHP developer remembers to take this step. It's much better to let the database handle this (rather routine) task.

A much better version of the schema from Example 5-1 is shown in Example 5-2.

Example 5-2. Adding an auto-incrementing ID field

DROP TABLE IF EXISTS author; CREATE TABLE author ( id INT NOT NULL AUTO_INCREMENT, name TEXT, PRIMARY KEY( ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required