Manage Subscriptions · Subscribe Now · F.A.Q.'s
HOME · Friday, July 4, 2008
Development & Programming
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 "fk_product_categories." 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.
Blinklist | Del.icio.us | Furl | Ma.gnolia | Newsvine | Spurl | Reddit | Technorati
Published on Monday, September 10, 2007
Comments:
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.
Posted by: Brian Getting
Tuesday, September 11, 2007
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 :-(
Posted by: Andy
Tuesday, September 11, 2007
Ecommerce Articles
- Browse All Articles
- Browse our complete archive of ecommerce articles.
- Accounting, Management & Legal
- Ecommerce articles related to managing a small business including ecommerce accounting, business strategy and legal considerations.
- Conversion & Usability
- Online business articles about converting web site visitors into customers and how to gauge and improve your business website's usability.
- Development & Programming
- Articles to help designers, developers and programmers create successful, search engine friendly ecommerce websites and improve existing ones.
- Hosting, Infrastructure & Software
- Articles for ecommerce businesses about ecommerce web hosting, business infrastructure, business strategy and helpful ecommerce & small business software.
- Interviews & Profiles
- Interviews with prominent ecommerce business personalities and profiles of successful online businesses.
- Inventory & Shipping
- Ecommerce articles about inventory management, ecommerce order fulfillment and product shipping considerations.
- Marketing & Revenue Growth
- Articles relating to online marketing, email marketing and using the Internet to growing your business.
- Search Engine Optimization
- Search engine optimization articles for ecommerce business owners, strategists, marketers and developers.
- Shopping Carts & Online Payments
- Articles covering ecommerce shopping cart platforms and options for choosing an online payment gateway.
- Training & Education
- Tutorials and articles providing training and education for ecommerce business owners and developers of ecommerce websites.
Search Articles
Ecommerce Community
- Ecommerce Blogs
- Read our blogs about ecommerce topics written by industry professionals.
- Community Forum
- Connect with other ecommerce professionals to trade advice and answers in our community forum.
- Podcasts
- Check out our ecommerce podcasts covering topics ranging from interviews to tutorials.
- RSS Content Feeds
- Subscribe to our RSS feeds and have fresh ecommerce content delivered to you.
Ecommerce Resources
- Free Email Newsletter
- Sign up for Ecommerce Notes, our free email newsletter for ecommerce business owners and developers.
- Ecommerce Directory
- Browse our directory of ecommerce products and services, or submit your own listing in our directory.
- Ecommerce Glossary
- Familiarize yourself with terminology or submit terms to help others with our Ecommerce Glossary.
- Events Calendar
- Find out about upcoming ecommerce events or invite other ecommerce professionals by posting your own event.
- Press Releases
- Browse ecommerce related press releases and post your own press release for distribution.
- Ecommerce Store & Back Issues
- Pick up back issues of Practical eCommerce magazine along with other merchandise from Practical Ecommerce
About Practical eCommerce
- Frequently Asked Questions
- Look at frequently asked questions regarded using our website, subscribing to our magazine and more.
- Advertising Information
- Information about advertising in Practical eCommerce magazine, on our website, or in our email newsletters.
- Editorial Sharing
- Learn about options for sharing our content with your visitors, customers or employees.
- About Us
- Learn more about Practical Ecommerce magazine and meet our staff.
- Contact Us
- Contact Practical Ecommerce at any time for more information. We'd love to hear from you.
Copyright 2007 Confluence Distribution, Inc. and Practical eCommerce.
All Rights Reserved.
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"
Posted by: Andy
Tuesday, September 11, 2007