Our product table looks something like this:

product id integer (primary key)
product name varchar(200)
product description text (up to 4 pages)
product price decimal/numeric


An orders table looks like this:

order id integer (primary key)
order first name varchar(200)
order last name varchar(200)
delivery instructions text (in case there are extra delivery instructions)
order delivered char(1)
order paid datetime


We've added two new data types here.

A 'char' field is exactly the same as a 'varchar' field - it can hold any type of character.

The difference is that a 'char' field is *always* the size you specify. If you don't provide enough data, then the database will pad it out. This can become a problem when searching and matching.

If you used a 'char' field for a phone number you might enter:

'555-555-555' (length 11)

The database table was created with a 'char' field of 12, so when it's stored in the database it becomes:

'555-555-555 ' (with an extra space on the end).

A varchar field will not pad out the data like this, it will leave it exactly as you enter it.

Use 'char' fields with caution. Unless you know that the data entered is always going to be the same length (eg an isbn or social security number) then try to use 'varchar' field instead.

In this particular case, we're using the 'order delivered' field to see whether the order has been successfully delivered or not ('1' means delivered, '0' is not delivered), so this isn't an issue.

The datetime field will differ between databases. In MySQL, this stores a date and a time, in PostgreSQL there is no such 'datetime' field - there are other field types to store this information (I use an 'int' to store the php 'time' variable and then convert it back in the application using the php 'date' function).