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 Sequel Pro, create a new table called “albums”.
In the “structure” tab of Sequel Pro, 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.
Sequel Pro made an ID field for us, but it does not automatically increment by default.
| Field Purpose | Field Name | Field Contains | MySQL Field Type |
|---|---|---|---|
| identifier | id | number | int |
For our ID field, we must tell MySQL to “auto increment” the column. When we do this, Sequel Pro will tell us that auto increment fields need to be indexed; do we want to make this our primary key? Click the okay button and Sequel Pro will make an index for us. We’ll talk about indexes later.
If you are using the command-line, use the following commands:
alter table albums add column id int unsigned not null auto_increment primary key;
