Use
these bookmarks to jump around the tutorial:

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.

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.
☺

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.

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.

Loading Comments...

Web Development Newsletter Signup

Advertiser Disclosure:
Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.

Thanks for your registration, follow us on our social networks to keep up-to-date