Search this site

Relational Databases, part 1

Consider a social bookmarking application like del.icio.us . del.icio.us stores each user's bookmarks in a shared, on-line environment.

A non-relational database might store all bookmarks in a flat file:
url note sumbitterFirstName sumbitterLastName email
 gswarriors.com A cool site
 Monta Ellis monta@warriors.com
 party.com Where I hang out
 Stephen    Jackson jack@warriors.com
 montaellis.com all about me
 Monta     Ellis monta@warriors.com

Such a scheme is wasteful of space, as a user's information is duplicated with every bookmark record. It's also inefficient to query.

A relational design would instead define the following two tables:

Account Table
 key firstName       lastName email
 1Monta   Ellis monta@warriors.com
 2Stephen     Jackson jack@warriors.com

Bookmark Table
 keyurl    
 note submitter_key
 1gswarriors.com 
 A cool site
 1
 2party.com 
Where I hang out
 2
 3montaellis.com
 all about me
 1

Note that each table has a column named key. This is a system-generated unique id that is provided for every table you create.

Each bookmark record now only stores a key reference into the Account table, not an entire user's account information. If the  submitter_key field  is 1, the submitter was Monta Ellis. If it is 2, the submitter was Stephen Jackson.

We then use an account key to get all the bookmarks of a particular user. For instance, all Monta's bookmarks could be retrieved with:

"Select * from Bookmark where submitter_key=1"

Of course the "1" would probably be a variable in a real query.

When keys are used to relate the tables of data, we call the database a "relational database".

Relational Database with Google's Appengine

With App Engine, classes are used to define tables and a special class called ReferenceProperty is used to define relations.

 class Account(db.Model):
          firstName=db.StringProperty()
          lastName=db.StringProperty()
          user=db.UserProperty()
 class Bookmark (db.Model)
        url=db.UrlProperty()
        note=db.StringProperty()
        submitter=db.ReferenceProperty(
The field key is not explicitly defined in either class. App Engine takes care of adding this field automatically to each table.

Instead of recording an email for each user, we record their entire Google user account.

The submitter is defined as a ReferenceProperty. Beneath the hood, a key representing a record in the Account table is stored, but the App Engine programmer can treat the submitter as an Account object.

Now consider the query to find all the bookmarks of a particular user. Above, we described the following query:

    "Select * from Bookmark where submitter_key=1"

And such a query would be used in some systems and could be used in App Engine.

However, with App Engine's higher-level API, you need not explicitly refer to a key. Instead, a query such as the following can be used:

    user = users.get_current_user()
    accountQuery = db.GqlQuery("Select * from Account where user=:1",user)
    account = accountQuery.get()  # get the one and only result
    bookmarks = db.GqlQuery("Select * from Bookmark where submitter=:1",account)

Even though the submitter field in the class Bookmark is a ReferenceProperty, the system knows that that reference is to an Account, so it allows for the comparison. You can also set the submitter field directly to an account. For instance, when a bookmark is entered, the controller might do the following:

    user = users.get_current_user()
    accountQuery = db.GqlQuery("Select * from Account where user=:1",user)
    account = accountQuery.get()  # get the one and only result
    bookmark=Bookmark()
    bookmark.url=self.request.get('url')
    bookmark.note=self.request.get('note')
    bookmark.submitter=account
    bookmark.put()

Extending the Example: Tags on Bookmarks

Del.icio.us allows users to specify tags on each bookmark. For instance, Monta might tag montaellis.com as "basketball" and "personal". How would you modify the database to allow for tags?

Recent site activity