Relational vs Non-Relational Data Modeling

Question

I have a user database with the following properties for each user:

• user
• id
• name
• zip
• city

In a relational database I would model it in a table user:

• user
• id
• name
• location_id

and have a second table called location:

• location
• id
• zip
• city

and location_id is a foreign key (reference) to an entry in the location table.

The advantage is, if the zip code for a certain city changes I only have to change one entry.

Let's go to the non-relational database, say Google App Engine. Here I would model it like in the specifications. I have a kind user:

class User(db.Model):
    name = db.StringProperty()
    zip = db.StringProperty()
    city = db.StringProperty()

The advantage is that I don't need to join two tables, but the disadvantage is that if the zip code changes I have to run a script that goes through all user entries and updates the zip code, correct?

There is another option in Google App Engine, which is to use ReferenceProperties. I could have two kinds, user and location:

class Location(db.Model):
    zip = db.StringProperty()
    city = db.StringProperty()

class User(db.Model):
    name = db.StringProperty()
    location = db.ReferenceProperty(Location)

I have exactly the same model as in the relational database.

Is what I just did wrong?

Does that destroy all the advantages of a non-relational database?

In order to get the value of zip and city I have to run I second query. But in the other case, to make a change in the zip code I have to run through all existing users.

What are the implications of these two modeling possibilities in a non-relational database like Google's datastore?

What are typical use cases for both of them--when should I use one and when the other?

Answer 1

In my experience, the biggest difference is that non-relational datastores force you to model based on how you'll query, because of the lack of joins, and how you'll write, because of the transaction restrictions. This of course results in very denormalized models. After a while, I started to define all the queries first, to avoid having to rethink the models later.

Because of the flexibility of relational db's, you can think about each data family in separate, create relations between them and in the end query how you wish (abusing joins in so many cases).

Answer 2

Imagine that GAE has two modes for the Datastore: RDMS-mode and non-RDMS-mode. If I take your ReferenceProperty example with the aim of "list all the users and all their zip codes" and write some code to print all of these.

For the [fictional] RDMS-mode Datastore it might look like:

for user in User.all().join("location"):
    print("name: %s zip: %s" % (user.name, user.location.zip))

Our RDMS system has handled the de-normalisation of the data behind the senes and done a nice job of returning all the data we needed in one query. This query did have a little bit of overhead as it had to stitch together our two tables.

For the non-RDMS Datastore our code might look like:

for user in User.all():
    location = Location.get( user.location )†
    print("name: %s zip: %s" % (user.name, location.zip))

Here the Datastore cannot help us join our data, and we must make an extra query for each and every user entity to fetch the location before we can print it.

This is in essence why you want to avoid overly normalised data on non-RDMS systems.

Now, everybody logically normalizes their data to some extent wether they are using RDMS or not, the trick is to find the trade off between convenience and performance for your use case.

† this is not valid appengine code, I'm just illustrating that user.location would trigger a db query. Also no-one should write code like my extreme example above, you can work around the continued fetching of related entities by say fetching locations in batches upfront.

if in a non-relation database I can model exactly the same what I can model in a relational database, why should I use a relational database at all?

relational-DB's excel at storing thousands-and-millions of rows of complex inter-related models of data, and allowing you to perform incredibly intricate queries to reform and access that data.

non-RDB's excel at storing billions+ rows of simple data and allowing you to fetch that data with simpler queries.

The choice should lie with your use-case really. The simpler structure of the non-relational model and design restraints that come with it is one of the main ways that AppEngine is able to promise to scale your app with demand.

Answer 3

Your understanding of the concept of the relational database is flawed. Relational databases organize their data in relations which contain a set of tuples of the same type. To rephrase, data is stored in tables with each row containing the same number of fields with the same types in the same order.

The example you provided which utilizes a foreign key demonstrates database normalization. This is a concept that can apply to relational as well as other types of databases.

Sorry, I can't answer your questions about Google's storage system, but hopefully this will clarify your understanding enough to find out.