Design Question: What is a Good Model?

[Benjamin Reed <ra...@gmail.com>]

I'm extremely new to CouchDB, and to NoSQL and map/reduce in general.  I
was wondering, what is the best way to design what I'm trying to accomplish?

I'm writing an app, which will have a collection of (calendar) events,
created by users.  Users will be able to mark events created by other users
as favorites.

In a traditional database, I'd have at least 2 tables:
- an event table, with a column representing the user that created the event
- a "favorites" table, which maps a user to an event that he has favorited
- optionally, a user table with info about the user, and a unique ID that
can be used in lieu of username in the first 2 tables when making foreign
references

In the (web) app I'm writing, I've got the events in couchdb:

[{
   _id: whatever,
   type: 'event',
   summary: 'foo',
   description: 'bar',
   createdBy: 'RangerRick'
}]

But what is the best way to retrieve the "favorites" associated with a
specific user?  Do I just make a doc with an ID set to
"RangerRick-favorites" and retrieve it directly?  Do I just put individual
username -> _id entries in for each favorite?

I currently am doing the latter, which didn't seem like a big deal until I
had to pull just the list of events that match the favorites, and can't
figure out how to make a map/reduce that does it efficiently.

Do I just map anything that's a type=favorite or type=event and then...
reduce it somehow by putting them together?

-- 
Benjamin Reed a.k.a. Ranger Rick
Fink, KDE, and Mac OS X development

Blog: http://www.raccoonfink.com/
Music: http://music.raccoonfink.com/

Re: Design Question: What is a Good Model?

[Benjamin Reed <ra...@gmail.com>]

Thanks for all the suggestions everyone.  Between you guys and #couchdb I
got things working nicely.

Given a fixture like this (please excuse my fanciful test data <g>):

[{
'_id': '1',
'type': 'event',
'username': 'official',
'summary': 'Murder',
'description': 'You will be murdered.',
'isPublic': true
},
{
'_id': '2',
'type': 'event',
'username': 'ranger',
'summary': 'Dying',
'description': 'I will be dying.',
'isPublic': true
},
{
'_id': '3',
'type': 'event',
'username': 'bob',
'summary': 'Living',
'description': 'I am totally going to continue living.',
'isPublic': true
},
{
'_id': '4',
'type': 'event',
'username': 'ranger',
'summary': 'Private',
'description': "It's a priiiivate event, dancin' for money, do what you
want it to do.",
'isPublic': false
},
{
'type': 'favorite',
'username': 'ranger',
'eventId': '3'
}]

...I'm able to get the set of documents I made + favorited with just a map
of:

map: function(doc) {
if (doc.type === 'event') {
emit(doc.username, {'_id': doc._id, 'type': doc.type});
} else if (doc.type === 'favorite') {
emit(doc.username, {'_id': doc.eventId, 'type': doc.type});
}
}

and include_docs true, using username as the key.

I think map/reduce is finally clicking.  :)



On Thu, Oct 10, 2013 at 6:34 AM, Mark Deibert <ma...@gmail.com>wrote:

> @Florian: It's not that simple. Pirate Ben's question was...
>
> "But what is the best way to retrieve the "favorites" associated with
> a specific
> user?".
>
> This will require 2 tables, perhaps 3. 2 if you go with the flatter
> approach of putting the favorites in an array attrib in the user. 3 if you
> want a favorites linking doc with user-2-event. I think it will be 2
> queries to get "user's favorite events" info regardless of whether you do 2
> or 3 docs. I think I'd opt for the 2 table version.
>
>
> On Wed, Oct 9, 2013 at 8:03 PM, Filippo Fadda <
> filippo.fadda@programmazione.it> wrote:
>
> > If you just want return the user favorites of course, you can just emit
> > userId as key and eventId as value, but if you want show them ordered by
> > timestamp (events belong in time), you must include the timestamp in the
> > key: emit([userId, timestamp], eventId). That because in CouchDB the
> query
> > results are _always_ ordered key.
> >
> > -Filippo
> >
> > On Oct 9, 2013, at 11:23 PM, Florian Westreicher Bakk.techn. wrote:
> > > Quick question: why would a complex key be required? Could we not emit
> > (userid, eventid) and be happy?
> >
>



-- 
Benjamin Reed a.k.a. Ranger Rick
Fink, KDE, and Mac OS X development

Blog: http://www.raccoonfink.com/
Music: http://music.raccoonfink.com/

Re: Design Question: What is a Good Model?

[Mark Deibert <ma...@gmail.com>]

@Florian: It's not that simple. Pirate Ben's question was...

"But what is the best way to retrieve the "favorites" associated with
a specific
user?".

This will require 2 tables, perhaps 3. 2 if you go with the flatter
approach of putting the favorites in an array attrib in the user. 3 if you
want a favorites linking doc with user-2-event. I think it will be 2
queries to get "user's favorite events" info regardless of whether you do 2
or 3 docs. I think I'd opt for the 2 table version.


On Wed, Oct 9, 2013 at 8:03 PM, Filippo Fadda <
filippo.fadda@programmazione.it> wrote:

> If you just want return the user favorites of course, you can just emit
> userId as key and eventId as value, but if you want show them ordered by
> timestamp (events belong in time), you must include the timestamp in the
> key: emit([userId, timestamp], eventId). That because in CouchDB the query
> results are _always_ ordered key.
>
> -Filippo
>
> On Oct 9, 2013, at 11:23 PM, Florian Westreicher Bakk.techn. wrote:
> > Quick question: why would a complex key be required? Could we not emit
> (userid, eventid) and be happy?
>

Re: Design Question: What is a Good Model?

[Filippo Fadda <fi...@programmazione.it>]

If you just want return the user favorites of course, you can just emit userId as key and eventId as value, but if you want show them ordered by timestamp (events belong in time), you must include the timestamp in the key: emit([userId, timestamp], eventId). That because in CouchDB the query results are _always_ ordered key.

-Filippo

On Oct 9, 2013, at 11:23 PM, Florian Westreicher Bakk.techn. wrote:
> Quick question: why would a complex key be required? Could we not emit (userid, eventid) and be happy? 

Re: Design Question: What is a Good Model?

["Florian Westreicher Bakk.techn." <st...@meredrica.org>]

Quick question: why would a complex key be required? Could we not emit (userid, eventid) and be happy? 

Filippo Fadda <fi...@programmazione.it> wrote:
>Mark is right, you need 3 different document types. The Favorite
>structure will have: _id, _rev, userId, eventId, timestamp (it's always
>a good idea to store the timestamp because you might use to sort data
>eventually).
>
>If you have a list of events and you want show a 'star' on every event
>starred by the current user, you have to make a query (one for each
>event unfortunately) using a complex key [eventId, userId]. 
>
>This is the best approach to avoid conflicts, because your events and
>your users will never change when a new event has been added to the
>user favorites.
>
>Bye
>
>-Filippo
>
>On Oct 9, 2013, at 7:36 PM, Mark Deibert wrote:
>
>> To follow on: sounds like you need 3 docs "events", "favorites" and
>"users"
>> or just use the built in "_users" which may be better. Do your best
>to keep
>> your docs denormalized as much as possible/sensible. Also keep
>something in
>> mind, with no-sql dbs, you will frequently have to query multiple
>views in
>> a sequence to effect relationships/joins. And definitely lookup views
>with
>> compound keys. This is how you will "join".
>> 
>> 
>> On Wed, Oct 9, 2013 at 1:23 PM, Mark Deibert <ma...@gmail.com>
>wrote:
>> 
>>> The reduce function is for aggregating data in a view. You'll need a
>view
>>> with a compound key to "join" two types of documents. Lookup
>compound keys
>>> in views. I'll post again later if you need more help.

-- 
Sent from Kaiten Mail. Please excuse my brevity.

Re: Design Question: What is a Good Model?

[Filippo Fadda <fi...@programmazione.it>]

Mark is right, you need 3 different document types. The Favorite structure will have: _id, _rev, userId, eventId, timestamp (it's always a good idea to store the timestamp because you might use to sort data eventually).

If you have a list of events and you want show a 'star' on every event starred by the current user, you have to make a query (one for each event unfortunately) using a complex key [eventId, userId]. 

This is the best approach to avoid conflicts, because your events and your users will never change when a new event has been added to the user favorites.

Bye

-Filippo

On Oct 9, 2013, at 7:36 PM, Mark Deibert wrote:

> To follow on: sounds like you need 3 docs "events", "favorites" and "users"
> or just use the built in "_users" which may be better. Do your best to keep
> your docs denormalized as much as possible/sensible. Also keep something in
> mind, with no-sql dbs, you will frequently have to query multiple views in
> a sequence to effect relationships/joins. And definitely lookup views with
> compound keys. This is how you will "join".
> 
> 
> On Wed, Oct 9, 2013 at 1:23 PM, Mark Deibert <ma...@gmail.com> wrote:
> 
>> The reduce function is for aggregating data in a view. You'll need a view
>> with a compound key to "join" two types of documents. Lookup compound keys
>> in views. I'll post again later if you need more help.

Re: Design Question: What is a Good Model?

[Mark Deibert <ma...@gmail.com>]

I was just thinking more about your "models" question. If this were me, I'd
skip the "Favorites" linking table all together. Add a "Favorites"
attribute to the "User" doc. In this Favorites attribute, put an array of
Event UUIDs. That flattens things out more and greatly simplifies your
views and queries.


On Wed, Oct 9, 2013 at 1:36 PM, Mark Deibert <ma...@gmail.com> wrote:

> To follow on: sounds like you need 3 docs "events", "favorites" and
> "users" or just use the built in "_users" which may be better. Do your best
> to keep your docs denormalized as much as possible/sensible. Also keep
> something in mind, with no-sql dbs, you will frequently have to query
> multiple views in a sequence to effect relationships/joins. And definitely
> lookup views with compound keys. This is how you will "join".
>
>
> On Wed, Oct 9, 2013 at 1:23 PM, Mark Deibert <ma...@gmail.com>wrote:
>
>> The reduce function is for aggregating data in a view. You'll need a view
>> with a compound key to "join" two types of documents. Lookup compound keys
>> in views. I'll post again later if you need more help.
>>
>>
>> On Tue, Oct 8, 2013 at 4:31 PM, Florian Westreicher Bakk.techn. <
>> stuff@meredrica.org> wrote:
>>
>>> I'm also new but I would just use a view for that. You can emit user
>>> names as key and favorites as values and later query it with key=username
>>>
>>> function map(doc) {
>>> if(doc.type === 'favorite') {
>>> emit(doc.user, doc.favorite_id);
>>> }
>>> }
>>>
>>> Cheers,
>>> Florian
>>>
>>> (coding on mobile is hard)
>>>
>>> Benjamin Reed <ra...@gmail.com> wrote:
>>> >I'm extremely new to CouchDB, and to NoSQL and map/reduce in general.
>>> >I
>>> >was wondering, what is the best way to design what I'm trying to
>>> >accomplish?
>>> >
>>> >I'm writing an app, which will have a collection of (calendar) events,
>>> >created by users.  Users will be able to mark events created by other
>>> >users
>>> >as favorites.
>>> >
>>> >In a traditional database, I'd have at least 2 tables:
>>> >- an event table, with a column representing the user that created the
>>> >event
>>> >- a "favorites" table, which maps a user to an event that he has
>>> >favorited
>>> >- optionally, a user table with info about the user, and a unique ID
>>> >that
>>> >can be used in lieu of username in the first 2 tables when making
>>> >foreign
>>> >references
>>> >
>>> >In the (web) app I'm writing, I've got the events in couchdb:
>>> >
>>> >[{
>>> >   _id: whatever,
>>> >   type: 'event',
>>> >   summary: 'foo',
>>> >   description: 'bar',
>>> >   createdBy: 'RangerRick'
>>> >}]
>>> >
>>> >But what is the best way to retrieve the "favorites" associated with a
>>> >specific user?  Do I just make a doc with an ID set to
>>> >"RangerRick-favorites" and retrieve it directly?  Do I just put
>>> >individual
>>> >username -> _id entries in for each favorite?
>>> >
>>> >I currently am doing the latter, which didn't seem like a big deal
>>> >until I
>>> >had to pull just the list of events that match the favorites, and can't
>>> >figure out how to make a map/reduce that does it efficiently.
>>> >
>>> >Do I just map anything that's a type=favorite or type=event and then...
>>> >reduce it somehow by putting them together?
>>>
>>> --
>>> Sent from Kaiten Mail. Please excuse my brevity.
>>>
>>
>>
>

Re: Design Question: What is a Good Model?

[Mark Deibert <ma...@gmail.com>]

To follow on: sounds like you need 3 docs "events", "favorites" and "users"
or just use the built in "_users" which may be better. Do your best to keep
your docs denormalized as much as possible/sensible. Also keep something in
mind, with no-sql dbs, you will frequently have to query multiple views in
a sequence to effect relationships/joins. And definitely lookup views with
compound keys. This is how you will "join".


On Wed, Oct 9, 2013 at 1:23 PM, Mark Deibert <ma...@gmail.com> wrote:

> The reduce function is for aggregating data in a view. You'll need a view
> with a compound key to "join" two types of documents. Lookup compound keys
> in views. I'll post again later if you need more help.
>
>
> On Tue, Oct 8, 2013 at 4:31 PM, Florian Westreicher Bakk.techn. <
> stuff@meredrica.org> wrote:
>
>> I'm also new but I would just use a view for that. You can emit user
>> names as key and favorites as values and later query it with key=username
>>
>> function map(doc) {
>> if(doc.type === 'favorite') {
>> emit(doc.user, doc.favorite_id);
>> }
>> }
>>
>> Cheers,
>> Florian
>>
>> (coding on mobile is hard)
>>
>> Benjamin Reed <ra...@gmail.com> wrote:
>> >I'm extremely new to CouchDB, and to NoSQL and map/reduce in general.
>> >I
>> >was wondering, what is the best way to design what I'm trying to
>> >accomplish?
>> >
>> >I'm writing an app, which will have a collection of (calendar) events,
>> >created by users.  Users will be able to mark events created by other
>> >users
>> >as favorites.
>> >
>> >In a traditional database, I'd have at least 2 tables:
>> >- an event table, with a column representing the user that created the
>> >event
>> >- a "favorites" table, which maps a user to an event that he has
>> >favorited
>> >- optionally, a user table with info about the user, and a unique ID
>> >that
>> >can be used in lieu of username in the first 2 tables when making
>> >foreign
>> >references
>> >
>> >In the (web) app I'm writing, I've got the events in couchdb:
>> >
>> >[{
>> >   _id: whatever,
>> >   type: 'event',
>> >   summary: 'foo',
>> >   description: 'bar',
>> >   createdBy: 'RangerRick'
>> >}]
>> >
>> >But what is the best way to retrieve the "favorites" associated with a
>> >specific user?  Do I just make a doc with an ID set to
>> >"RangerRick-favorites" and retrieve it directly?  Do I just put
>> >individual
>> >username -> _id entries in for each favorite?
>> >
>> >I currently am doing the latter, which didn't seem like a big deal
>> >until I
>> >had to pull just the list of events that match the favorites, and can't
>> >figure out how to make a map/reduce that does it efficiently.
>> >
>> >Do I just map anything that's a type=favorite or type=event and then...
>> >reduce it somehow by putting them together?
>>
>> --
>> Sent from Kaiten Mail. Please excuse my brevity.
>>
>
>

Re: Design Question: What is a Good Model?

[Mark Deibert <ma...@gmail.com>]

The reduce function is for aggregating data in a view. You'll need a view
with a compound key to "join" two types of documents. Lookup compound keys
in views. I'll post again later if you need more help.


On Tue, Oct 8, 2013 at 4:31 PM, Florian Westreicher Bakk.techn. <
stuff@meredrica.org> wrote:

> I'm also new but I would just use a view for that. You can emit user names
> as key and favorites as values and later query it with key=username
>
> function map(doc) {
> if(doc.type === 'favorite') {
> emit(doc.user, doc.favorite_id);
> }
> }
>
> Cheers,
> Florian
>
> (coding on mobile is hard)
>
> Benjamin Reed <ra...@gmail.com> wrote:
> >I'm extremely new to CouchDB, and to NoSQL and map/reduce in general.
> >I
> >was wondering, what is the best way to design what I'm trying to
> >accomplish?
> >
> >I'm writing an app, which will have a collection of (calendar) events,
> >created by users.  Users will be able to mark events created by other
> >users
> >as favorites.
> >
> >In a traditional database, I'd have at least 2 tables:
> >- an event table, with a column representing the user that created the
> >event
> >- a "favorites" table, which maps a user to an event that he has
> >favorited
> >- optionally, a user table with info about the user, and a unique ID
> >that
> >can be used in lieu of username in the first 2 tables when making
> >foreign
> >references
> >
> >In the (web) app I'm writing, I've got the events in couchdb:
> >
> >[{
> >   _id: whatever,
> >   type: 'event',
> >   summary: 'foo',
> >   description: 'bar',
> >   createdBy: 'RangerRick'
> >}]
> >
> >But what is the best way to retrieve the "favorites" associated with a
> >specific user?  Do I just make a doc with an ID set to
> >"RangerRick-favorites" and retrieve it directly?  Do I just put
> >individual
> >username -> _id entries in for each favorite?
> >
> >I currently am doing the latter, which didn't seem like a big deal
> >until I
> >had to pull just the list of events that match the favorites, and can't
> >figure out how to make a map/reduce that does it efficiently.
> >
> >Do I just map anything that's a type=favorite or type=event and then...
> >reduce it somehow by putting them together?
>
> --
> Sent from Kaiten Mail. Please excuse my brevity.
>

Re: Design Question: What is a Good Model?

["Florian Westreicher Bakk.techn." <st...@meredrica.org>]

I'm also new but I would just use a view for that. You can emit user names as key and favorites as values and later query it with key=username

function map(doc) {
if(doc.type === 'favorite') {
emit(doc.user, doc.favorite_id);
} 
}

Cheers, 
Florian

(coding on mobile is hard)

Benjamin Reed <ra...@gmail.com> wrote:
>I'm extremely new to CouchDB, and to NoSQL and map/reduce in general. 
>I
>was wondering, what is the best way to design what I'm trying to
>accomplish?
>
>I'm writing an app, which will have a collection of (calendar) events,
>created by users.  Users will be able to mark events created by other
>users
>as favorites.
>
>In a traditional database, I'd have at least 2 tables:
>- an event table, with a column representing the user that created the
>event
>- a "favorites" table, which maps a user to an event that he has
>favorited
>- optionally, a user table with info about the user, and a unique ID
>that
>can be used in lieu of username in the first 2 tables when making
>foreign
>references
>
>In the (web) app I'm writing, I've got the events in couchdb:
>
>[{
>   _id: whatever,
>   type: 'event',
>   summary: 'foo',
>   description: 'bar',
>   createdBy: 'RangerRick'
>}]
>
>But what is the best way to retrieve the "favorites" associated with a
>specific user?  Do I just make a doc with an ID set to
>"RangerRick-favorites" and retrieve it directly?  Do I just put
>individual
>username -> _id entries in for each favorite?
>
>I currently am doing the latter, which didn't seem like a big deal
>until I
>had to pull just the list of events that match the favorites, and can't
>figure out how to make a map/reduce that does it efficiently.
>
>Do I just map anything that's a type=favorite or type=event and then...
>reduce it somehow by putting them together?

-- 
Sent from Kaiten Mail. Please excuse my brevity.

Re: Design Question: What is a Good Model?

[Cameron Jacobson <ca...@datashovel.com>]

On 10/08/2013 10:10 AM, Benjamin Reed wrote:
> I'm extremely new to CouchDB, and to NoSQL and map/reduce in general.  I
> was wondering, what is the best way to design what I'm trying to accomplish?
>
> I'm writing an app, which will have a collection of (calendar) events,
> created by users.  Users will be able to mark events created by other users
> as favorites.
>
> In a traditional database, I'd have at least 2 tables:
> - an event table, with a column representing the user that created the event
> - a "favorites" table, which maps a user to an event that he has favorited
> - optionally, a user table with info about the user, and a unique ID that
> can be used in lieu of username in the first 2 tables when making foreign
> references
>
> In the (web) app I'm writing, I've got the events in couchdb:
>
> [{
>    _id: whatever,
>    type: 'event',
>    summary: 'foo',
>    description: 'bar',
>    createdBy: 'RangerRick'
> }]
>
> But what is the best way to retrieve the "favorites" associated with a
> specific user?  Do I just make a doc with an ID set to
> "RangerRick-favorites" and retrieve it directly?  Do I just put individual
> username -> _id entries in for each favorite?
>
> I currently am doing the latter, which didn't seem like a big deal until I
> had to pull just the list of events that match the favorites, and can't
> figure out how to make a map/reduce that does it efficiently.
>
> Do I just map anything that's a type=favorite or type=event and then...
> reduce it somehow by putting them together?
>


I don't believe the following option will be directly relevant to this
project in particular, but when dealing with highly related data, where
entities may have hundreds or thousands of relationships to other
entities, I find it quite nice to rely on a separate "relationships"
*type*, and compose views off of that in order to be able to pull
relationships of a particular entity or set of entities.  I have a small
project (not worth promoting here) where I basically tested this concept
out.  With the right composition of your keys in your views you can get
a useful subset of relational functionality (think in terms of a Graph
Database) where you can compose a number of levels of granularity into
your queries to get as much or as little information about your entity
relationships as you want.

At a certain point, if relationships grow more horizontally than
vertically (ie. multiple joins are needed to extract the relationships
as opposed to individual entities having many types of relationships to
other entities) it likely can become important to weigh the option of
incorporating a relational database to handle certain functions of the
system.