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 1

Thursday, May 1st, 2014 by Servage

So, you’ve decided that you’re interested in using a database in your web development, huh? Well, then the next step would be to learn about how the database works.

How Does My Data Get Stored?

How the data is stored in a database is probably much simpler than you might think. Databases use a series of Tables to store the data. A table simply refers to a two dimensional representation of your data using columns and rows. For example:

John      Smith                  jsmith@huh.com

Paul       McCartney          paul@beatles.com

Bill        Murray                gopher@caddyshack.com

John Smith jsmith@huh.com
Paul McCartney paul@beatles.com
Bill Murray gopher@caddyshack.com

 

So, then , how does the database keep things straight? Well, first each database table is given a unique name. Without a unique name the DBMS (DataBase Management System) would get very confused.

Next, each column in the table is given a unique name. In our example above it would be something like first_name, last_name, email. This doesn’t mean each column that you name has to be unique within the entire database. It only has to be unique within the table you have created. Also notice that the names don’t use any spaces. When naming tables and columns be sure to keep it simple with letters and numbers. Spaces and symbols can be illegal characters that will mess up the whole works, so if you need to clarify a name use the “_” instead of spaces.

Let’s update our table now:

Table name: contacts

first_name last_name email
John Smith jsmith@huh.com
Paul McCartney paul@beatles.com
Bill Murray gopher@caddyshack.com

How Do I Organize My Data?

The next thing to understand about your table is the Primary Key. The Primary Key simply refers to a column in your table that is guaranteed unique. The Primary Key is then used for the purposes of indexing your table which makes it much more efficient to search, sort, link, etc.

So what is the Primary Key in our example? Good question. There is none. In our example, there is nothing that is going to be guaranteed unique. Obviously there are many people that share the same last name and/or first name that may be added to the database in the future. The email address is much more likely to be unique but what if two people shared the same email?

To avoid the uncertainty of using a data column as a primary key, many developers will create their own column which contains a computer generated unique number, an ID number of sorts. This way you don’t ever have to worry about its uniqueness since the database knows not to ever use the same number twice.

Table name: contacts

contact_id first_name last_name email
1 John Smith jsmith@huh.com
2 Paul McCartney paul@beatles.com
3 Bill Murray gopher@caddyshack.com

 

How many tables should I use? That depends on how you can logically break down your data. There is no limit to the number of tables, or columns for that matter, you can create. Keep in mind, though, that one huge table will be very inefficient while a bunch of little tables can be nearly impossible to keep straight. The best solution usually lies somewhere in the middle.

Here’s an example. Let’s say your contact table stores contact information for a subscription database for your website. Now we need to store what newsletter(s) each person wants to subscribe to. We could simply add another column in our contact table that would store the name of the newsletter. This would allow us to save the information we need but cause names and email addresses to be duplicated, once for each different newsletter a person subscribes to. That would be highly inefficient.

What about making a second table for the names of the newsletters. This way each newsletter name and description would be stored only once.

Table name: newsletters

newsletter_id name description
1 Web to Go A newsletter for HTML fans.
2 Design Web A newsletter for web & graphic designers.

 

How Does It All Relate?

That’s great. I have the people in one table and the newsletters in another table. How the heck am I supposed to know who is subscribed to what?

This is the best part. This is where the Relational Database gets its name. Relational Database? You never mentioned that.

So, far you have learned some of the basic elements of a database. Now you will learn how to take those basic elements and make them relate to one another so that the information you are storing remains logically linked together. Hence the new and improved Relational Database.

Sources for further reading

Using Database in Web Development-Part 1, 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.