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 3

Saturday, May 3rd, 2014 by Servage

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

sdf

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

Sources for Further Reading

Using Database in Web Development-Part 3, 2.0 out of 5 based on 4 ratings
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.