8 Working with

Dates

Dates are another common field type. You will enter dates as Year Month Day. For example,

you might keep track of the day you purchased each album. If you bought Brutal Planet on

January 3, 2005, you would enter this into your MySQL date column as 2005 1 3.

Create a table

We're going to keep track of albums and tracks. We'll create the albums table first. Before

creating a table, it is a good idea to think of what you will need in that table. For our albums

table, we will want:

Field Purpose

Field Name

Field Contains

MySQL Field Type

album title

album

text, up to 80 characters

varchar(80)

artist

artist

text, up to 60 characters

varchar(60)

year made

year

small number

smallint

rating

rating

tiny number

tinyint

date purchased

purchasedate

date

date

In the  tables  area of CocoaMySQL, create a new table called  albums .

In  the   structure   tab  of  CocoaMySQL,  add  new  columns  one  by  one.  We're  going  to

specify  NOT NULL  for the  album  column. This means that the  album  column can

never be empty. Every album must have a name.

If you are using the command line version, use the following commands:

create table albums (album varchar(80) not null);

alter table albums add column artist varchar(60);

alter table albums add column year int unsigned;

alter table albums add column rating tinyint unsigned;

alter table albums add column purchasedate date;

A   tinyint   is  a  number  from  0  to  255.  A   smallint   is  a  n  umber  from  0  to  65,535.

 Unsigned  means that the numbers cannot be negative. If you let the numbers be negative,

then the maximum has to drop to make room for the negatives. A  signed  tinyint goes from

 127 to +127, for example.

Record Information

It is always a good idea to have an auto incrementing ID field. This ID field will ensure that

if we choose to access this data for other users, we have a unique field with which to identify

each record. The unique ID will never change; even if the title, artist, or year purchased gets

updated, the unique ID will remain the same. This makes it possible for us to add data in

other tables, and link those other tables to this one.