Database Basics: Part 1
July 23, 2002So, 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: