Database Basics: Part 1

Tuesday Jul 23rd 2002 by Curtis Dicken

In today's world you just can't escape the database. Right now there are literally over a million databases at work storing all sorts of information from government files to company employee records to consumer shopping habits. So, should you use a database for your website? Why not? Everyone else is. It's easy to become an information junky.

So, what possible use could you have for a database on your website? After all, you are just creating a site for yourself, your friends and your family.

As I mentioned earlier, there are a myriad of uses for databases. I'm sure it is easy to imagine all of the uses for a database on a commercial web site like an online store. You could store visitor information for logging in, purchase information, order logs, bookkeeping information, company reports, pricing structures, account information and the list goes on.

But what about personal stuff? Well, imagine this. You started a site for you and your family. You have a few pages for your family, your parents have a few pages and you just added some pages about your friends. Now your site just seems to keep growing and you don't really have the time to keep up with your own stuff let alone all of the things your family and friends keep sending you.

For example, you started a page so that you and your friends can borrow each others DVDs. Your page keeps track of who has what DVD and who is the original owner of the DVD. Each time a DVD changes hands someone sends you an email and you have to change the information on the website.

Another example. Your parents have 7 grandchildren. They love to keep up with their grandchildren's' activities. So, they are constantly sending you each grandchild's schedule of events to post. You get sports schedules, school activities and recitals just to name a few.

You also created a community page for your visitors. On it you have the names, email addresses and telephone numbers of your family and friends. The trouble is your friends and family seem to keep adding their friends and family and you keep getting all of the emails asking you to add more and more people.

Getting tired just thinking about it? Can you see how a database could be your friend?

If you could create a database driven DVD checkout system, calendar of events and community contacts you could save yourself a ton of time by letting your users do the updating instead of you through some simple web forms that you create and then use your database to update your pages automatically.

What Types of Databases are There?

You wouldn't believe. DBMSs (Data Base Management Systems) come in many different shapes, sizes and flavors, however, there are two basic categories: Enterprise and Personal.

(In case you are confused, DBMS refers to the software that handles moving the data in, out and around your database while the term database refers to the actual body of data that you are storing.)

Enterprise DBMSs are designed primarily for use with corporations, governments and any other function that would require the database to handle large amounts of data coming in and out. Information Technology folks refer to this as scalability which simply means how well the database handles multiple simultaneous transactions (the handling of data). For example, a high scalability would mean that the database can handle everything from a few transactions an hour to thousands of transactions or more happening at the same time.

Some examples of Enterprise DBMSs that you might have heard of would be Microsoft's SQL (pronounced sequel) Server, Oracle or IBM's DB2.

Personal DBMSs are generally not highly scalable. They are intended to be used as learning tools or in low demand situations like you may run into while working on your personal projects. Depending on the DBMS, you might even find good uses for a personal DBMS in small business. Many Enterprise DBMSs also have personal versions like Oracle that was mentioned above. Other companies produce separate products like Microsoft's Access which are very similar to their Enterprise product.

How Do I Know Which One is Best for Me?

Well, first you need to decide what your immediate needs are. Are you going to use this on a website that could potentially have hundreds of people accessing information at the same time? Then you need to estimate your future needs. How much do you think your website will grow?

So, let's take our example website outlined above and do a little database planning.

First, the DVD exchange. Unless you are planning on running an online DVD exchange business from your site one day you can certainly expect that no more than one or two people will be accessing the system at the same time.

Second, your parent's calendar of events for the grandkids. For this one it's pretty obvious that only your parents will be accessing the calendar to make changes, however, you must also consider how many people will be viewing the calendar as well. Since the calendar is dynamically updated with information in the database you must add viewers into the equation. So, at most, you would probably expect no more than three or four people accessing the calendar simultaneously.

Third, you have created your community contact page. Here you can probably expect no more than two people adding names to the list at the same time and probably no more than five people viewing the list.

Did you notice the theme? Basically, the biggest limitation a DBMS will have is its scalability. For this example we have probably no more than twelve people accessing all of your database driven pages at the same time. Therefore, a personal DBMS like Microsoft's Access will probably fit the bill since it can handle up to around 25 concurrent users. This will also leave plenty of room for future growth.

How Do I Connect to a Database?

What the heck do you mean connect? Connecting to a database is basically what you would think. It establishes a connection between your application (in this case one of your web pages) and the database itself so that commands and data can be passed back and forth.

Depending on what database you choose, there are a few different ways that you can connect. Most of your major Enterprise DBMSs allow you to connect using an IP address, a database name, a valid user name and password.

You can also use a DSN (Data Source Name) which is something that is set up by you or your hosting service on your server. You can think of the DSN as a sort of map to your database.

Although the basic principles pretty much remain the same, each DBMS handles connectivity differently. And not only are the DBMSs different but so are the programming languages that you can choose to use. For example, the code you need to create a connection in Java is quite different from the code you would use in ASP (Active Server Pages).

Important note: Whatever you choose, you will have to make sure that the DBMS and language are available on or from your server.

How Do I Use the Database Once I am Connected?

In order to make use of your database you will have to learn a few different things.

First, you will need to learn a language like ASP that will allow you to manipulate your pages with the information you have stored in your database.

Second you will need to learn some basic SQL (Structured Query Language) commands which are the commands that allow you to "talk" to the DBMS. This will allow you to add, delete, update and retrieve the information you need.

Only certain languages support database connectivity. Probably the most popular language in use on the web these days is ASP. ASP allows you to create pages that are dynamic and easily maintained.

Obviously, there is much more that can be covered when talking about DBMSs and databases. This tutorial is mainly intended to give you an idea of the many possibilities for using databases.

Here are some DBMSs and sites to visit:

  • Oracle - The Oracle website can be viewed here. - A free trial version of the DBMS is available here.
  • Microsoft's SQL Server -Click here for the SQL Server home on Microsoft's web site. You can download the free 120-day evaluation version directly from Microsoft.
  • Access 2002 - You can check out this very popular personal/small business DBMS by clicking here.
  • DB2 - The DB2 trial version is available here.
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
Paul McCartney
Bill Murray

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
Paul McCartney
Bill Murray

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
2 Paul McCartney
3 Bill Murray

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 our contact table stores contact information for a subscription database for HTML Goodies. 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 Goodies to Go A newsletter for HTML fans.
2 Design Goodies 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.

Now, using our example above we have a table of subscribers (contacts) and a table of newsletter information (newsletters). So, how do we know who subscribed to what? We make another table that links the two tables we already have together.

Table Name: contact_newsletter_link

contact_id newsletter_id
1 2
2 1
2 2
3 1

This table then places a link between the contact table and the newsletters table using each tables unique ID number. So, by referring to the table above you can see that John Smith has subscribed to Goodies to Go, Paul McCartney subscribed to Goodies to Go & Design Goodies and Bill Murray subscribed to Design Goodies.

But what about that Primary Key thing you mentioned earlier? The ID numbers are used more than once. Nothing is unique.

That's true. In this instance we are using Foreign Keys. A Foreign Key basically means that the number used in a Foreign Key column is not necessarily unique to the table it is in but is unique to the table it is referring to. In this case contact_id is unique to the contact table but not necessarily unique to the contact_newsletter_link table.

We now have the basics of a relational database.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved