How Do You Model This?

["Welch, Matt" <Ma...@compuware.com>]

Ok, forgive my table structured background. I'm trying to see the light,
here, but I need help converting an SQL table based model into something
more CouchDB-ish. In an old-fashioned relational database it would look
similar to this: a users table and a permissions table.

Users:

Name	Group
-----------------
Alice	Developers
Bob	Developers
Carol	Developers
Eve	Managers
Frank	Managers

Permissions:

Group		Directory
---------------------
Developers	Source
Developers	Binary
Managers	Binary
Managers	Planning

Then I want to look up what directories Alice has permissions to, or
even make a master list of all the users and their permissions like:

Alice	Source
Alice	Binary
Bob	Source
Bob	Binary
...
Frank	Binary
Frank	Planning

I need to do the equivalent of adding and removing users, adding and
removing directories, and change group and permission assignments. None
of that can be static. 

So how do I set up my data and views in CouchDB to do this kind of
thing? Or is this just not the sort of thing CouchDB will be good at
modeling?

Thanks in advance for your help.


The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

RE: How Do You Model This?

["Welch, Matt" <Ma...@compuware.com>]

Thank you. This was really helpful. 

My actual data isn't users/groups/permissions, but it similar in
structure. Especially in that there could be lots of users and
permissions but only a few groups. Skipping a group document and
defining them implicitly in the users and permissions is the trick, I
think. I will only have a few groups and they won't change much, so a
little extra effort to manage them won't be too big an impact. Thank
you.


The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.

From: Leo Simons [mailto:mail@leosimons.com] 
Sent: Tuesday, October 27, 2009 7:04 PM
To: user@couchdb.apache.org
Subject: Re: How Do You Model This?

On Tue, Oct 27, 2009 at 8:07 PM, Welch, Matt <Ma...@compuware.com>
wrote:
> [sample users, groups, directory]

Data model (some of it summary, some of it assumptions):
* A user is identified by a name.
* A user is a member of 0 or more groups.
* A group is identified by a name.
* A directory is identified by a name.
* A group has access to 0 or more directories.

There are probably more directories and more users than there are
groups. Adding and removing directories is probably the most frequent,
following by adding and removing users, followed by adding and removing
groups.

Turn into documents:
* Make a document per user.
* The user document lists the user's groups.
* Make a document per directory.
* The directory document lists the groups that have access to it.
(It seems like you wouldn't need a document for a group, a group simply
exists as long as the name exists in any document as a string.)

Implications:
* Adding and removing users is easy. Adding and removing users from
groups is easy.
* Adding and removing directories is easy. Adding and removing
permissions to directories is easy.
* Adding groups is implicit (which you may not like). Removing groups is
hard (which you may like even less).

Querying:
* To find all the things that a user has access to, first get the user's
document and the list of groups from it. Then, do a map/reduce over all
directories, where you emit only directories that have at least on group
that is in the users group list. You can make that a view :)
* To find a full list of permissions, do a two-step map/reduce. I think
you can also make that a view in couch :)

Eventual consistency considerations:
* in a distributed scenario, you may wish to have something like a
group_delete_list, which is a document that is used as a special filter
for groups that are pending deletion. You'll have to make your views
respect it, or do that inside application code.

> Then I want to look up what directories Alice has permissions to, or 
> even make a master list of all the users and their permissions like
...
> I need to do the equivalent of adding and removing users, adding and 
> removing directories, and change group and permission assignments. 
> None of that can be static.

That seems possible with the above.

> So how do I set up my data and views in CouchDB to do this kind of 
> thing? Or is this just not the sort of thing CouchDB will be good at 
> modeling?

Frankly, this kind of model is something that a relational database is
*exceedingly* good at. If you need to do it on a really large scale, a
tree structure (LDAP...) is the way to go. I'm sure CouchDB can be made
to work for this kind of thing just fine, but some of it will be more
awkward.

ciao,

Leo

Re: How Do You Model This?

[Leo Simons <ma...@leosimons.com>]

On Tue, Oct 27, 2009 at 8:07 PM, Welch, Matt <Ma...@compuware.com> wrote:
> [sample users, groups, directory]

Data model (some of it summary, some of it assumptions):
* A user is identified by a name.
* A user is a member of 0 or more groups.
* A group is identified by a name.
* A directory is identified by a name.
* A group has access to 0 or more directories.

There are probably more directories and more users than there are
groups. Adding and removing directories is probably the most frequent,
following by adding and removing users, followed by adding and
removing groups.

Turn into documents:
* Make a document per user.
* The user document lists the user's groups.
* Make a document per directory.
* The directory document lists the groups that have access to it.
(It seems like you wouldn't need a document for a group, a group
simply exists as long as the name exists in any document as a string.)

Implications:
* Adding and removing users is easy. Adding and removing users from
groups is easy.
* Adding and removing directories is easy. Adding and removing
permissions to directories is easy.
* Adding groups is implicit (which you may not like). Removing groups
is hard (which you may like even less).

Querying:
* To find all the things that a user has access to, first get the
user's document and the list of groups from it. Then, do a map/reduce
over all directories, where you emit only directories that have at
least on group that is in the users group list. You can make that a
view :)
* To find a full list of permissions, do a two-step map/reduce. I
think you can also make that a view in couch :)

Eventual consistency considerations:
* in a distributed scenario, you may wish to have something like a
group_delete_list, which is a document that is used as a special
filter for groups that are pending deletion. You'll have to make your
views respect it, or do that inside application code.

> Then I want to look up what directories Alice has permissions to, or
> even make a master list of all the users and their permissions like
...
> I need to do the equivalent of adding and removing users, adding and
> removing directories, and change group and permission assignments. None
> of that can be static.

That seems possible with the above.

> So how do I set up my data and views in CouchDB to do this kind of
> thing? Or is this just not the sort of thing CouchDB will be good at
> modeling?

Frankly, this kind of model is something that a relational database is
*exceedingly* good at. If you need to do it on a really large scale, a
tree structure (LDAP...) is the way to go. I'm sure CouchDB can be
made to work for this kind of thing just fine, but some of it will be
more awkward.

ciao,

Leo

RE: How Do You Model This?

["Welch, Matt" <Ma...@compuware.com>]

This is close, but I don't think it would scale up well. I'm looking at
doing the equivalent of adding many hundreds of permissions. Building a
big long list doesn't seem very efficient. But maybe it is in the
CouchDB world. I don't know.

Also, it requires a two step process to work. When you add a new user
you would have to create the new document for the user and then update
the group document to add them to the list. It seems cleaner to me to
just create the user with their group specified in the user document.
But that may just be a personal preference.

-----Original Message-----
From: Sebastian Cohnen [mailto:sebastiancohnen@googlemail.com] 
Sent: Tuesday, October 27, 2009 4:10 PM
To: user@couchdb.apache.org
Subject: Re: How Do You Model This?

Without having read all your post (sry), I found this a few days ago:
http://kore-nordmann.de/blog/couchdb_a_use_case.html

On 27.10.2009, at 21:07, Welch, Matt wrote:

> Ok, forgive my table structured background. I'm trying to see the 
> light, here, but I need help converting an SQL table based model into 
> something more CouchDB-ish. In an old-fashioned relational database it

> would look similar to this: a users table and a permissions table.
>
> Users:
>
> Name	Group
> -----------------
> Alice	Developers
> Bob	Developers
> Carol	Developers
> Eve	Managers
> Frank	Managers
>
> Permissions:
>
> Group		Directory
> ---------------------
> Developers	Source
> Developers	Binary
> Managers	Binary
> Managers	Planning
>
> Then I want to look up what directories Alice has permissions to, or
> even make a master list of all the users and their permissions like:
>
> Alice	Source
> Alice	Binary
> Bob	Source
> Bob	Binary
> ...
> Frank	Binary
> Frank	Planning
>
> I need to do the equivalent of adding and removing users, adding and
> removing directories, and change group and permission assignments.  
> None
> of that can be static.
>
> So how do I set up my data and views in CouchDB to do this kind of
> thing? Or is this just not the sort of thing CouchDB will be good at
> modeling?
>
> Thanks in advance for your help.
>
>
> The contents of this e-mail are intended for the named addressee  
> only. It contains information that may be confidential. Unless you  
> are the named addressee or an authorized designee, you may not copy  
> or use it, or disclose it to anyone else. If you received it in  
> error please notify us immediately and then destroy it.
>

Re: How Do You Model This?

[Sebastian Cohnen <se...@googlemail.com>]

Without having read all your post (sry), I found this a few days ago: http://kore-nordmann.de/blog/couchdb_a_use_case.html

On 27.10.2009, at 21:07, Welch, Matt wrote:

> Ok, forgive my table structured background. I'm trying to see the  
> light,
> here, but I need help converting an SQL table based model into  
> something
> more CouchDB-ish. In an old-fashioned relational database it would  
> look
> similar to this: a users table and a permissions table.
>
> Users:
>
> Name	Group
> -----------------
> Alice	Developers
> Bob	Developers
> Carol	Developers
> Eve	Managers
> Frank	Managers
>
> Permissions:
>
> Group		Directory
> ---------------------
> Developers	Source
> Developers	Binary
> Managers	Binary
> Managers	Planning
>
> Then I want to look up what directories Alice has permissions to, or
> even make a master list of all the users and their permissions like:
>
> Alice	Source
> Alice	Binary
> Bob	Source
> Bob	Binary
> ...
> Frank	Binary
> Frank	Planning
>
> I need to do the equivalent of adding and removing users, adding and
> removing directories, and change group and permission assignments.  
> None
> of that can be static.
>
> So how do I set up my data and views in CouchDB to do this kind of
> thing? Or is this just not the sort of thing CouchDB will be good at
> modeling?
>
> Thanks in advance for your help.
>
>
> The contents of this e-mail are intended for the named addressee  
> only. It contains information that may be confidential. Unless you  
> are the named addressee or an authorized designee, you may not copy  
> or use it, or disclose it to anyone else. If you received it in  
> error please notify us immediately and then destroy it.
>