www.1-4-you.net


Database


So, youve decided that youre 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 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:

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.

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 dont ever have to worry about its uniqueness since the database knows not to ever use the same number twice.

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.

Heres an example. Lets 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.

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

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

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

Now that you have an understanding of the basic structure of a relational database we will examine the tables and the data a bit more closely.