IndexedDB and many-to-many Relationships

Question

How are you all handling many-to-many relationships in IndexedDB?

For example, say I have a Blog object to hold a blog post and a Tag object for a tag/label of the blog post. One Blog can have many Tags and one Tag can be used by many Blogs.

I would create a blog store and tag store (though I'm open to suggestions) to house the two types of objects:

// ...
var blogStore = db.createObjectStore("blog", {keyPath: "blogId", autoIncrement: true});
blogStore.createIndex("title", "title", {unique: true});
var tagStore = db.createObjectStore("tag", {keyPath: "tagId", autoIncrement: true});
tagStore.createIndex("label", "label", {unique: true});

Off hand I can think of two ways to link the two:

1. have a Blog.tags which would be an array of BlogTag objects which holds blogId and tagId (and would also be in the store for retrieval) or
2. have a Blog.tags which would be an array of tagIds that could be used to look up the Tags.

The first way seems longer-winded but is how this would be tackled in SQL. Is that just SQL-baggage that I should leave behind?

I suppose a 3rd way would be to have Blog.tags be an array of Tags. This seems simplest but then I couldn't query for Tags or reuse tags across blogs (or could I?).

Has anyone else handled such a situation with indexedDB? If so, what did you end up doing? What were some pitfalls?

Answer 1

I'm working on an IndexedDB-backed JS neural network implementation and faced this very problem.

We don't have joins in IndexedDB so you're looking at at least two object store hits unless you're doing some sort of memoization/caching.

From experience I've found that a document-oriented style is best with IndexedDB objects (store everything in the same store), but a secondary store is needed to house relations.

Here's what I'm doing.

Say you want to have a local store of actors and movies -- something like IMDB. This and most any many-to-many relationship can be modeled with IndexedDB using two tables: Objects and Relationships.

Here are the two tables. You'd want key lookups* on almost everything. Anything that doesn't say unique can be non-unique.

Objects object store:

type_id*
whatever*..

Relationships object store:

id* (unique, auto-incrementing)
from_type*
to_id*

An actor/movie example would be two records in the Objects table and one in the relationship table:

var actor1 = {
    id: 'actor_jonah_goldberg',
    display: 'Jonah Goldberg',
};

var actor2 = {
    id: 'actor_michael_cera',
    display: 'Michael Cera'
};

var movie1 = {
    id: 'movie_superbad',
    display: 'Superbad',
    year: 2007
};

var movie2 = {
    id: 'movie_juno',
    display: 'Juno',
    year: 2007
};

//relationship primary key ids are auto-inc

var relationship1 = {
    from_id: 'actor_jonah_goldberg',
    to_id: 'movie_superbad'
} 

var relationship2 = {
    from_id: 'actor_michael_cera',
    to_id: 'movie_superbad'
} 

var relationship3 = {
    from_id: 'actor_michael_cera',
    to_id: 'movie_juno'
} 

Psuedo-code for getting Michael Cera's movies:

IndexedDBApp( { 'store': 'relationships', 'index': 'from_id', 'key': 'actor_michael_cera', 'on_success': function( row ) {...} );
// Would return movie_superbad and movie_juno rows on_success

Psuedo-code for getting all movies from a given year:

IndexedDBApp( { 'store': 'objects', 'index': 'year', 'key': 2007, 'on_success': function( row ) {...} );
// Would return movie_superbad and movie_juno rows on_success

Psuedo-code for getting a movie's actors:

IndexedDBApp( { 'store': 'relationships', 'index': 'to_id', 'key': 'movie_superbad', 'on_success': function( row ) {...} );
// Would return actor_jonah_goldberg and actor_michael_cera on_success

Psuedo-code for getting all actors:

IndexedDBApp( { 'store': 'relationships', 'index': 'id', 'cursor_begin': 'actor_a', 'cursor_end': 'actor_z', 'on_success': function( row ) {...} );
// Would return actor_jonah_goldberg and actor_michael_cera on_success