TechSupport: "What's A Relational Database?"

Q: Our web developer tells us that our ecommerce site uses a relational database, but I don’t understand what this means. Could you please explain?

At the foundation of nearly all web applications, including dynamically-generated websites, lies a relational database that stores and manages all of the data for the application. Whether you are using MySQL, PostGRES or another of the many database applications, your data will be stored into various tables in the database. Having a clear understanding of how you intend to use that data is crucial when creating a database for your application, particularly in understanding the relationships between tables of data. In an effort to explain what relationships are, I will illustrate the most commonly-used type, called “one-to-many relationships.”
A one-to-many relationship is, as the name implies, a relationship between one record in one table and many records in another table. As an example, let’s say we have a shopping cart application that allows us to assign products to particular categories. In this case, our database would have one table that holds data for the categories, probably with the following fields:

Each product will be assigned to a category, effectively giving us the statement “one category will have many products in it.” In order to do this, we need a way of telling the database program which category each product belongs to. Let’s take a look at our other table that holds information about our products:

As you can see, we have a field for the name of our product, the price, a description and also another “id” field for the primary key of the products table. However, unlike the categories table, our products table also has an additional field called “category_id,” which holds the primary key value of the category the product is assigned to. This is called a “foreign key,” and it allows the database application a way to correlate our two tables together.

To illustrate, we can now find all the products that are in the “Widgets” category by asking the database to retrieve all records from the product table that have a value of “1” in their “category_id” field. Effectively, we have created a one-to-many relationship in the database between our categories and our products.

In addition to creating this relationship, we can also add constraints to it. Since it is a one-to-many relationship, the assumption is each category will have zero or more products assigned to it. But what happens if you delete a category? Without any planning, the database will simply delete the record from our categories table. But that would obviously create a problem, since we could end up with products that are assigned to a category that does not exist. One solution is to go through and manually delete all of the products that are assigned to the category we want to delete. A better way is to let the database application handle that. By adding a constraint on our products table, we can tell the database application what to do when we delete a category:

alter table products add constraint fk/product/categories foreign key (category/_id) references categories(id) on delete cascade

This looks a little daunting, and it helps to talk it out. We are telling MySQL (or whatever database you are using) to add a constraint to the products table called “fkproductcategories.” I made this name up, but it’s good to name it something useful so you can remember what it is. From there, we tell the database the foreign key in the products table called “category_id” references the primary key field in the categories table called “id,” essentially mandating the relationship that we have defined. Finally, we tell the database to cascade delete requests, which means when a request is made to delete a record in the categories table, the database should also go through and delete any records in the products table that are assigned to that category. The database uses the one-to-many relationship and the foreign key field in the products table to accomplish this.

While this has just been a quick overview of one kind of relationship, it is important to understand how relationships work in databases. In addition, while I didn’t go into detail in this article, it is also important to understand how constraints and indices work in databases to ensure your next web application will perform well and be scalable. Keeping a clean database can eliminate troublesome roadblocks in the development cycle, many of which could be costly to overcome.