Reg comments3

Which kind of cloud database is right for you?

This way for relationship counselling

Deep Dive In the second of two articles about cloud-based data storage, the focus is on the options available and how to decide on one approach rather than the other. (Part 1 is here).

Find the keys

Not-so-relational databases do not store data in tables – at least, not the sort of tables we associate with relational databases. Unfortunately, Google has chosen to call its underlying storage mechanism BigTable and Microsoft offers Table Storage as an option in Windows Azure.

Joseph Feltz, an architect at the Microsoft Technology Centre in Dallas, says: “When thinking about Windows Azure table storage, I suggest you think of it as object storage.”

The first thing to understand about key/value stores is that they store all related information about a single item, or object, as a single entity, as opposed to having multiple tables in a relational database linked by primary/foreign key relationships.

This means that you eliminate the joins necessary in conventional environments. The items themselves are stored in a “table”, also known as a domain. Completely different sorts of items may be stored within the same domain.

Out of order

The reason why these sorts of databases are usually referred to as key/value stores is because you use keys to inter-relate different items. If two distinct items have the same value for a particular key, then they are related.

But that is it. Because you can put anything into a domain there is no database schema and there are no primary/foreign key relationships and there is no referential integrity.

It is entirely possible to orphan an order because you have deleted the customer

It is entirely possible in a key/value store to orphan an order because you have deleted the customer. You can prevent this but not in the database; you would have to do it in the application code instead.

There is a significant danger that companies will try to exploit key/value stores in the cloud without proper structure. It is a good idea to do some class modelling as a precursor to setting up a key/value database.

If the data is not stored in a formalised way you will have a problem if you ever decide to migrate from your chosen key/value platform, or if you want to archive data from it, or use that information as a part of a master data management project.

Normally, you would use a data profiling tool to discover the relevant business entities but so far there are no data profiling tools that work with key/value stores as opposed to relational databases.

And, in so far as retrieving data rules from application code is concerned (so called program profiling) we know of only one vendor, Rever, that can do this. So the message is: even if key/value stores are more freeform than relational databases, you must still approach your database design with rigour.

Formal attire

There are loads of not-so-relational databases but relatively few are available in the cloud. Among the most notable are Amazon’s SimpleDB; Google’s AppEngine Datastore, which acts as an interface to BigTable; and Microsoft’s Windows Azure.

Microsoft also offers SQL Azure in the cloud, as a cut-down version of SQL Server, while Amazon also offers MySQL and has announced the availability of Oracle.

So the question arises: when should you choose a conventional relational database and when should you prefer a key/value store?

Fundamentally, relational databases are good at handling formally structured data, especially where transactional integrity is a requirement.

This is still true if the application is running with Azure: it will still be appropriate to host structured data within SQL Azure. Further, if you are migrating an application to the cloud, and that application runs against a relational database, then a relational database in the cloud will make most sense.

Using key/value databases may make sense when, for example, you want to use multimedia or other unstructured data, or if you never access more than a single relational table at a time and there are never any join requirements.

Feel the width

A further consideration is commercial. Most vendors impose limits: on the data that can be held within a single partition, or on the number of items or rows that can be retrieved in a single query, or on the amount of time allowed for processing a query, and so on.

So it may be less expensive, for example, to license storage using Windows Azure than SQL Azure, depending on how much storage is needed.

Key/value stores offer advantages and disadvantages over relational databases. Whether in the cloud or not, leaving aside commercial considerations, transaction processing is probably best handled relationally, while other types of application may well be suited to a not so relational approach. ®

Sign up to our Newsletter

Get IT in your inbox daily

Biting the hand that feeds IT © 1998–2017