Servage Magazine

Information about YOUR hosting company – where we give you a clear picture of what we think and do!

Using Database in Web Development-Part 2

Friday, May 2nd, 2014 by Servage

2 Now that you have a feel for how to organize the contents of your database I 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. ☺

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.

Sources for Further Reading

Using Database in Web Development-Part 2, 5.0 out of 5 based on 1 rating
Categories: Guides & Tutorials

Keywords:

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

No comments yet (leave a comment)

You are welcome to initiate a conversation about this blog entry.

Leave a comment

You must be logged in to post a comment.