Database Basics: Part 2

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:

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

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.

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.

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.