Practical Ecommerce

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.

Brian Getting

Brian Getting

Bio   •   RSS Feed


email-news-env

Sign up for our email newsletter

  1. Legacy User September 11, 2007 Reply

    Cool…just what I need another book I need to add to my pile of books I need to read…any recommendations for good books on relational databases for beginners…I know currently we use MS SQL but that is the extent of my knowledge :-(

    — *Andy*

  2. Legacy User September 11, 2007 Reply

    Wouldn't deleting the whole product record be a bit overkill. All the work that went in to entering those products would be lost. If you remade the deleted category later or came up with a more applicable category later, you would just want to add those prodcts back to the new category without having to re-eneter all the product info.

    I know nothing about this. but it would seem maybe better to have the category_id change to some type of catch-all category like Misc with an id of like 100 so at least you can make a webpage to view all un-catagorized products (not customer facing) to make it easier to reassign uncategorized products at a later date…deleting the whole record seems like overkill…unless you really were getting rid of the products "completely"

    — *Andy*

  3. Legacy User September 11, 2007 Reply

    The information about cascading the delete command was put in there to illustrate how foreign key constraints work. I left out the "on update cascade" option so that it wouldn't get confusing.

    You are correct in that it would probably be a better idea to flag categories (and all associated articles) as "inactive" or something like that rather than deleting them. It all depends on your system, what your plans are, and how important the data is. Often times developers will never delete from the database, but rather flag it as "deleted" so that it appears to be gone to the end user.

    Obviously, in the event that some data needs to be recovered, it's right there in the database. It was never gone at all, and can easily be put back to use.

    — *Brian Getting*

  4. Carolina Konovich July 20, 2016 Reply

    Brian, thanks so much for explaining how it works. I’m the process of launching my ecommerce (women’s apparel) and I need to select the right relational database. Which one would you recommend? I know of the common ones like Oracle, Microsoft SQL Server, & IBM DB2, but they are so expensive. I also came across Tibero, not sure if you heard of them but here is the link: http://www.tmaxsoft.com/ca_en/tibero_ca_en/

    Tibero is more recent, but half the licensing fees and has great reviews.

    I would really appreciate your advice, so I can make the right decision.