Database Basics

Before we can begin working with database and making our Coldfusion websites truly interactive we need to learn some basics about Databases, how they are typically structured. With most, if not all, interactive websites the programming language is used to primarily Create, Read, Update or Delete (C.R.U.D.) information in a database.

While this tutorial will not, by itself, cover all aspects that you need to know about Databases it will give you a taste and hopefully enough to create a simple database with one or two tables and be able to understand the basics of databases.

Relational Databases

The first new term we will be learning is Relational Database. A Relational database is a structured collection of information that relates to a particular subject or purpose such as a warehouse inventory database. The database is used to store and manage information such as product name, cost, number of items on hand, etc. The information can be separated into tables, and these tables resemble (on the surface) an excel spreadsheet, meaning that the information is stored in a column/row format. Columns represent the informational name or fields of what we want to store e.g. product name, cost and the rows represent an individual product record. So for our product table each field in the table stores one piece of information, the product name is stored in one column, the cost in another etc. One row or record represents one set of related information.

The image shows the creation of a simple products table in MS-Access. Here we are setting up what information we want to store for each product we will have in our inventory, obviously there would be much more information in a real-world case. Notice the first field we defined in our table (id). We created the id field setting its data-type to AutoNumber. This means that every time we insert a record into our table the value in the id column will increment by 1. This automatically creates a unique value for each and every product in our table and will allow us to reference a product without needing to know all the other descriptive information (fields).
The next field of interest is the ManufacturerID field. In a database it is possible to spread the information you need to store in multiple tables, this gives us an opportunity to only store information once and use it in many places. Since it is likely that we will have many products in our warehouse created by the same manufacturer we don't need to store the manufacturer information in the products table, we only need to have a reference to the manufacturer, represented by it's unique id. So we will build another table that has the manufacturers information and its very own unique identifier.

About Relational Tables

Databases provide a means of specifying a relationship between two tables, so long as you have a means of actually relating the two tables. We did that when we created a field in the products tables called manufacturerId, we can then relate that field to the manufacturers table id field. If you look at the image carefully you will see the number 1 next to the connecting line on the Manufacturers table and the infinity sign next to that of the products table. These represent the type of relationship between the products and manufacturers table. It means that there is a one to many relationship between the two tables, that is one manufacturer can have many products and each product can have only one manufacturer.
So why do we want to separate the information into two tables?

1. The Manufacturer information is the same for each product for that manufacturer, so storing the information in the product table would not only be redundant it would also take up more space.
2. If the address of the manufacturer changes you only need to change it in one place. All products that refer to that manufacturer would automatically have the correct manufacturer address.

Besides it's never a good thing to store the same information multiple times, good relational database design separates repeated information into it's own tables and only the unique identifier is stored instead of the repeated information. In our example I would most likely have a table called State that would store the 2 letter code of the state along with perhaps the full state name and a unique identifier, thus in our manufacturer table we would have stateId as a field name instead of state. That field would then hold a unique id representing the state instead of the actual information.

We could then use the information stored in the state table and reference it from many other tables, such as the state field for the billing address in the customer table.

Conclusion

This tutorial is only the tip of the iceberg when it comes to databases, there is a whole lot more information that you need to know in order to be good with databases. We only just wet our big toe in the ocean that is database programming and hopefully gained some basic understanding.
I encourage you to read my SQL Primer tutorial which will give you a little more knowledge.