Understanding 4 Database Types, for Ecommerce

As I wrote in “How Databases Work with Ecommerce Applications,” you’re likely using a database for your ecommerce store even if you don’t know it.

Ecommerce sites typically use databases for:

• Transaction tracking;
• Product catalogs;
• Non-product content, such as blog posts and “about us” pages.

In this post, I’ll address four common database types.

1. Relational Databases

A relational database is similar to a set of spreadsheets. Each spreadsheet has columns and rows of data.

Databases are similar to spreadsheets, except the rows and columns are called tables. But, unlike spreadsheets, in a relational database the data can, well, relate to other data.

For example, a relational database for an ecommerce store can have a table of customers and, separately, a table of orders. In the orders table, there is a relationship to customers, so that each order relates to a single customer. This relationship is expressed in the form of a simple data element that points to the specific customer row.

In a relational database, you only have to update information in one place instead of multiple locations.

The benefit of this is that when the customer data changes, you only have to update one piece of data. For example, if a customer obtains a new email address, you only have to update the one row in the customer table and not all of the rows in the orders table. All orders for that customer automatically relate to the new email address.

Relational databases work well for transactions and non-product content. They also work for most parts of product catalogs, which can be difficult to design when there are a large number of combinations, such as multiple colors and sizes. (Given the popularity of relational databases, however, they are no longer difficult to use, as there are many product-catalog solutions.)

Common relational databases include PostgreSQL, MySQL, and Oracle.

2. Document and Key/Value Databases

Each unique item in a document database is assigned a “key” that is used as an identifier.

A second type of database is a document or key/value. Document and key/value databases are technically different, but for purposes of this article, I’ll group them together and call them, simply, “document databases.”

Document databases are straightforward. Picture a directory on your computer full of different types of files. Each file has a unique name and contains unique data.

Document databases work on this model. Each piece of data is given a name, called a “key.” That key is used to find and retrieve that data. You can define different types of data, much in the same way your computer has different types of files. A ecommerce store, for example, has order, customer, and page types.

With document databases, you can design what types of data to include. This makes document databases flexible and powerful

This flexibility can be difficult to manage, though. If you need to change how the data is stored, you could end up having to update every piece of data in the database. Thus, document databases usually require long-term thinking when deciding how to structure and store the data.

Document databases work well for product catalogs and non-product content, such as blog posts and “about us” pages. Ecommerce transactions are not ideally suited for document databases, however. Relational databases work much better for transactional data.

MongoDB, CouchDB, and Redis are examples of document databases that are used by stores.

3. Cloud Databases

Cloud databases are more recent. They are not a true database type, but more of hosting process.

Cloud databases are hosted by a third party, typically at a very large scale. This can benefit an ecommerce storeowner, as she will not have to worry about hosting or growing the database as the store grows.

But cloud databases have risks. For one, a storeowner must ensure that the third party is trustworthy since it is holding your company and customer data. A storeowner should also make sure that the host is a viable business and will be around as long as the store is.

An interesting thing about cloud databases is that they can mix and match multiple database types — pure relational databases, pure document databases, and features from both to create something unique. Cloud databases can therefore work for all types of ecommerce data.

Amazon Web Services, for example, offers cloud databases. It has a Relational Database Service and, also, DynamoDB, a document database.

4. API Databases

Another type of database is an application programming interface — API. This is where your ecommerce site connects to a third-party company via its API to store and retrieve data.

Like cloud databases, API databases are not really a separate type. But depending on the API, they can replace the need for a database entirely.

API databases are common on SaaS platforms like Shopify and BigCommerce, where merchants can connect to the API to customize how their store functions.

A simple example is using an API to determine which products are best sellers and then saving that to a best sellers’ page in your store.

The biggest limitation of an API database is the API supplier. You can only use and work on data that the supplier exposes in its API. If it doesn’t expose order data, you can’t do anything with orders.

Database Types Are Not Clearly Defined

In practice, there are not clear lines around the different database types. Sometimes the features of one type are borrowed by another.

For example, PostgreSQL is a popular relational database system but it also works like a document database. And there are hosting providers, such as Amazon Web Services and Heroku, that combine cloud versions of PostgreSQL with additional features like automatic scaling and web interfaces.

Using Multiple Databases

An ecommerce store can simultaneously use multiple databases, each for different purposes.

For example, a Shopify merchant might have the following.

• Shopify’s relational database for the storefront (which merchants cannot access directly).

• A custom app using Shopify’s API that stores data in its own relational database from Amazon Web Services.

• A document database that syncs order data nightly for historic reporting.

• An API database with the merchant’s payment gateway that holds customer payment details and subscriptions.