Use
these bookmarks to jump around the tutorial:
[So,
How Do I Create a Table?]
[How
Do I Design a Table in a Database Tool?]
Now that you have a feel
for how to organize the contents of your database I’ll bet you’d like to create
some real tables.
What
is a Data Type?
Well, before we get
started making a table there is one thing you need to understand first, Data
Types. Data Types are pretty straight forward. They indicate the type of
data that you are storing in a given table column. Amazing, huh?
So, what are the
different Data Types? Here is a list of some of the most common ones and what
type of values they hold:
| CHAR | This will hold between 0 and 255 characters. You have to specify the maximum amount of characters that you will be putting here when you create your column.
|
| LONG VARCHAR | This will hold as many characters as you like up to 2 gigabytes of space.
|
| BIT | This can hold a value of 0 or 1. It is primarily used for yes/no and true/false issues. It is also referred to as a Boolean or Yes/No field.
|
| FLOAT | This type is used to store decimal numbers. It is primarily used for mathematical purposes.
|
| INT | This type indicates that you are storing whole numbers here. You can store any whole number between -2147483648 and 2147483648.
|
| SMALLINT | Same as above except you are limited to numbers between -32768 and 32768.
|
| DATE | This stores a date. I know you’re shocked.
|
| DATETIME | This will store a date and time. It is also commonly referred to as a TIMESTAMP since it is primarily used to Time Stamp entries or updates to a row or table. |
Granted, there are a
whole lot more but these will get you started and are among the most common. You
will also find that every DBMS has its own quirks and syntax. That means the
same Data Type can be referenced by different names in different DBMS’s. You’ll
have to check your DBMS documentation for specifics.
back to
top
How
Do I Create a Table?
Creating a table is very
easy. I’m going to show you 2 different ways to accomplish this task. The first
will be to use a SQL statement. The second will be using a Graphical User
Interface (GUI) tool. For our GUI example we are going to
use Access since it is one of the most common databases out there today and,
more importantly, it’s already installed on my machine.
☺
We’ll use the example we
started in Part 1.
First the SQL Statement:
CREATE TABLE
contacts (
contact_id INT IDENTITY (1, 1)
NOT NULL ,
first_name CHAR (30) NULL
,
last_name CHAR (50) NULL
,
email VARCHAR (75) NULL
);
At first glance this
statement can be a little bit intimidating but it really makes sense once you
break it down.
Alright, CREATE TABLE
makes sense. This tells the DBMS to make a new table called "contacts".
Now for the part in the
parentheses. First we have "contact_id". If you remember, "contact_id" will hold
our unique index number that we call our Primary Key. That means we know that
column will always be a whole number so we make it an INT (Integer). Next we use
IDENTITY which tells the DBMS that this is our Primary Key. Now, the (1,1) means
that we want to start with the number 1 and we want to increment each new ID
number by 1. So, in our example, John Smith would have an ID of 1, Paul
McCartney would have an ID of 2, etc. Lastly, there is the statement NOT NULL.
This means that this column must have a value and can never be
empty. This is a required element of any Primary Key.
Next is "first_name".
Here we are stating that "first_name" is a character column (CHAR) that will
store 30 or less characters in it. We are also saying the "first_name" can be
empty if we want it to be. That is what the NULL statement is for.
The creating of "last_name"
is exactly like "first_name" except we are allowing up to 50 characters in this
column.
Lastly, we have "email".
Just to be different I used VARCHAR instead of CHAR. Essentially, it is the same
as the CHAR in the statement and serves the same purpose, storing a set of
alpha-numeric characters (a string). The real difference between CHAR and
VARCHAR is the VARCHAR is variable, hence the VAR. This means that when someone
new is entered in the database the "first_name" and "last_name" columns
will have a fixed size, 30 and 50 respectively, no matter how little information
you put in them. So, if you entered the name "John" in the "first_name" column,
it would take up a 30 character block of storage instead of only the 4
characters it really needs. With VARCHAR it would only take up as much space is
needed for storage. VARCHAR does come with a minimum storage requirement, so you
will have to check your DBMS documentation to determine whether of not VARCHAR
is going to be a benefit to you. VARCHAR has one other important feature, it can
store a heck of a lot more than 255 characters if you want it to.
That’s it. Alright, so
it wasn’t really easy but at least it is logical.
back to
top
How Do
I Design a Table in a Database Tool?
Now we’ll use some
pretty pictures to illustrate the process of creating a database and table in
Access. There are many other DBMS’s out there besides Access, so be sure to do a
little homework and download some demos before you decide which DBMS is right
for you.
Once Access has loaded
the first order of business is to create a blank database. Once you have
selected the blank database option, you will need to give your database a name
and a location to save it.
Now that you have your
database created, select the Create Table in Design View option in the middle
window by double-clicking on it.
This will send you to
the design view window. From here will will be able to create all of the columns
in your table. The first column we will create is the "contact_id". We want "contact_id"
to be our primary key, so we will need to let Access know that we want this
field to be automatically numbered. To accomplish this, first type "contact_id"
in the first Field Name space.
Vocabulary Note:
Field and Column are often used to refer to the same thing.
Depending on the DBMS, you will likely see both of these references.
Next, tab over to the
Data Type space. Here is where you will declare this field’s data type. You will
notice there is no INT option or anything that refers to IDENTITY either. This
is an excellent example of how different DBMS’s handle things differently. In
Access, you will select AutoNumber for the Data Type. AutoNumber is sort of an
all-in-one thing. It takes care of making the field an INT and an IDENTITY.
You will notice that
selecting AutoNumber gives you some different options in the bottom left section
of the window. First is the Field Size option. This option tells Access whether
you want this field to be a Long Integer or a Replication ID. A Long Integer
will allow you some flexibility like determining whether the field is indexed
(which makes it much more efficient at searches) or if the numbers are generated
randomly or incrementally. We will choose Replication ID, however. By choosing
Replication ID, the field will be automatically set to be used as an ID number
which is exactly what we want.
Lastly, notice that
there is a space for entering a description of the field that you are creating.
As a matter of practice, you should get used to entering a short description of
what the field holds. It can be very handy in refreshing your memory when you or
others come back to view a table.
The next 3 entries are
all very straight forward. Enter "first_name", "last_name" and "email" in the
next 3 Field Name spaces. When selecting the Data Type for each one you will
notice that there is no CHAR or VARCHAR available to you. Again, just one of the
differences. In this case you will select Text. Text is the simplified Data Type
that Access uses for anything that is to store some text like a name or email
address.
You will notice that
choosing Text gives you a different set of options in the bottom left of the
window. There are a whole lot of options here but the only one we will be
concerned with right now is the Field Size. Be sure to set the Field Size length
for each Text field equal to the limits we set in the SQL example above:
first_name – 30
last_name – 50
email – 75
Now you have a table
that is almost done. The only thing left is to set a Primary Key and save it. To
set your Primary Key, right-click on the Field Name that you want to be your
key, which, in this case is "contact_id".
You will see several
menu choices including Primary Key. Select that and it’s done.
To save your new table
all you have to do is hit your close button in the top right of the window and
you will be prompted to save your table if you want to. Enter the name of the
table, "contacts", and you’re done.
Like I stated earlier,
take some time to research different DBMS’s and see what you think. Each one is
unique but the basics remain the same. The trick is usually learning the
individual syntax and the different procedures of each DBMS.
back to
top
What’s Next?
Now that you have a
database and table to work with we’ll take a look at how to get some data into
it.
In Part 3 of this series
we will:
-
Learn about the INSERT
statement -
Learn about the UPDATE
statement -
Learn how these
statements relate to different DBMS’s
