You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@couchdb.apache.org by Mohammad Prabowo <ri...@gmail.com> on 2012/06/23 08:47:34 UTC
A few questions about View Collation and Linked Documents
I have a few questions about view collations and linked documents. Suppose
i have this schema:
http://i.imgur.com/0xof6.png
The red-bordered tables are what i'm interested in. I
have successfully convert it into document:
{
"id": "employees_n",
"emp_no": ..,
"birth_date": ..,
"first_name": ..,
"last_name" : ..,
"gender": ..,
"hire_date": ..,
"type" : "employees"
}
{
"id": "dept_emp_n",
"emp_no": ..,
"dept_no": ..,
"from_date": ..,
"to_date" : ..,
"type" : "dept_emp"
}
{
"id": departments_n,
"dept_no": ..,
"dept_name": ..,
"type" : "departments"
}
Where n is ascending from 1 to count(*) of related tables
Questions:
1. I want to do view collations from doc.type = employees to doc.type =
departments. In SQL, i can easily JOIN these tables using something like
"SELECT * FROM employees e JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no"
Can i do this using view collations? I've been stuck after writting this
map function:
function(doc) {
if (doc.type == 'employees') {
emit([doc.emp_no, 0], doc);
} else if (doc.type == 'dept_emp ') {
emit([doc.emp_no, 1, doc.dept_no], doc);
} else if (doc.type == 'departments' ) {
//What should i emit?
}
}
2. I've been reading about linked documents, and it seems that it can only
be used with "_id" : some_id". Can i use another field like "dept_no" :
"some dept_no" so i can get all docs with that dept_no ?
Re: A few questions about View Collation and Linked Documents
Posted by Mathias Leppich <ml...@muhqu.de>.
Hi Mohammad,
I just use the document type as a prefix for my doc._id. You don't have to do that… but having the doc type in your Ids brings a few benefits. e.g. if you delete a document in couchdb, you will see the deletion in the changes feed. But as you can't get the type attribute of a deleted document you will not know of which type this deleted document was. This becomes a problem when you use filtered replication. If you want to replicate all documents of a specific type to another couchdb, you also have to replicate the deletions… but you can only find out which deleted documents you need to replicate if the type is still present, e.g. in the document id as a prefix.
- mathias
On Jun 26, 2012, at 13:52 , Mohammad Prabowo wrote:
> Hi Mathias,
>
> I looked into your sample, and examined your views. But i think your way
> doesn't work in my case, since my dept_emp id is just a monotonic number,
> so i can't use substr() method
>
> On Mon, Jun 25, 2012 at 1:26 PM, Mathias Leppich <ml...@muhqu.de> wrote:
>
>> Hi Mohammad,
>>
>> there is nothing wrong with the way how you want to store the data in
>> couch, it might just become necessary to store data redundant to make
>> bigger use of it inside the views.
>>
>> I took your example and put a little sample on my iris couch. see it here:
>> http://muhqu.iriscouch.com/_utils/database.html?emp_dep/_all_docs
>>
>> There are 2 views:
>> dep-emp: To see all employees of a given department:
>>
>> http://muhqu.iriscouch.com/_utils/database.html?emp_dep/_design/sample/_view/dep-emp
>>
>> http://muhqu.iriscouch.com/emp_dep/_design/sample/_view/dep-emp?include_docs=true&startkey=[%22dep/001%22]&endkey=[%22dep/001%22,[]]
>>
>> emp-dep: To see all departments a given employee is/was in:
>>
>> http://muhqu.iriscouch.com/_utils/database.html?emp_dep/_design/sample/_view/emp-dep
>>
>> http://muhqu.iriscouch.com/emp_dep/_design/sample/_view/emp-dep?include_docs=true&startkey=[%22emp/001%22]&endkey=[%22emp/001%22,[]]
>>
>>
>> - mathias
>>
>> On Jun 23, 2012, at 8:47 , Mohammad Prabowo wrote:
>>
>>> I have a few questions about view collations and linked documents.
>> Suppose
>>> i have this schema:
>>>
>>> http://i.imgur.com/0xof6.png
>>>
>>> The red-bordered tables are what i'm interested in. I
>>> have successfully convert it into document:
>>>
>>> {
>>> "id": "employees_n",
>>> "emp_no": ..,
>>> "birth_date": ..,
>>> "first_name": ..,
>>> "last_name" : ..,
>>> "gender": ..,
>>> "hire_date": ..,
>>> "type" : "employees"
>>> }
>>>
>>> {
>>> "id": "dept_emp_n",
>>> "emp_no": ..,
>>> "dept_no": ..,
>>> "from_date": ..,
>>> "to_date" : ..,
>>> "type" : "dept_emp"
>>> }
>>>
>>> {
>>> "id": departments_n,
>>> "dept_no": ..,
>>> "dept_name": ..,
>>> "type" : "departments"
>>> }
>>>
>>> Where n is ascending from 1 to count(*) of related tables
>>> Questions:
>>>
>>> 1. I want to do view collations from doc.type = employees to doc.type =
>>> departments. In SQL, i can easily JOIN these tables using something like
>>> "SELECT * FROM employees e JOIN dept_emp de ON e.emp_no = de.emp_no
>>> JOIN departments d ON de.dept_no = d.dept_no"
>>>
>>> Can i do this using view collations? I've been stuck after writting
>> this
>>> map function:
>>>
>>> function(doc) {
>>> if (doc.type == 'employees') {
>>> emit([doc.emp_no, 0], doc);
>>> } else if (doc.type == 'dept_emp ') {
>>> emit([doc.emp_no, 1, doc.dept_no], doc);
>>> } else if (doc.type == 'departments' ) {
>>> //What should i emit?
>>> }
>>> }
>>>
>>>
>>> 2. I've been reading about linked documents, and it seems that it can
>> only
>>> be used with "_id" : some_id". Can i use another field like "dept_no" :
>>> "some dept_no" so i can get all docs with that dept_no ?
>>
>>
Re: A few questions about View Collation and Linked Documents
Posted by Mohammad Prabowo <ri...@gmail.com>.
Hi Mathias,
I looked into your sample, and examined your views. But i think your way
doesn't work in my case, since my dept_emp id is just a monotonic number,
so i can't use substr() method
On Mon, Jun 25, 2012 at 1:26 PM, Mathias Leppich <ml...@muhqu.de> wrote:
> Hi Mohammad,
>
> there is nothing wrong with the way how you want to store the data in
> couch, it might just become necessary to store data redundant to make
> bigger use of it inside the views.
>
> I took your example and put a little sample on my iris couch. see it here:
> http://muhqu.iriscouch.com/_utils/database.html?emp_dep/_all_docs
>
> There are 2 views:
> dep-emp: To see all employees of a given department:
>
> http://muhqu.iriscouch.com/_utils/database.html?emp_dep/_design/sample/_view/dep-emp
>
> http://muhqu.iriscouch.com/emp_dep/_design/sample/_view/dep-emp?include_docs=true&startkey=[%22dep/001%22]&endkey=[%22dep/001%22,[]]
>
> emp-dep: To see all departments a given employee is/was in:
>
> http://muhqu.iriscouch.com/_utils/database.html?emp_dep/_design/sample/_view/emp-dep
>
> http://muhqu.iriscouch.com/emp_dep/_design/sample/_view/emp-dep?include_docs=true&startkey=[%22emp/001%22]&endkey=[%22emp/001%22,[]]
>
>
> - mathias
>
> On Jun 23, 2012, at 8:47 , Mohammad Prabowo wrote:
>
> > I have a few questions about view collations and linked documents.
> Suppose
> > i have this schema:
> >
> > http://i.imgur.com/0xof6.png
> >
> > The red-bordered tables are what i'm interested in. I
> > have successfully convert it into document:
> >
> > {
> > "id": "employees_n",
> > "emp_no": ..,
> > "birth_date": ..,
> > "first_name": ..,
> > "last_name" : ..,
> > "gender": ..,
> > "hire_date": ..,
> > "type" : "employees"
> > }
> >
> > {
> > "id": "dept_emp_n",
> > "emp_no": ..,
> > "dept_no": ..,
> > "from_date": ..,
> > "to_date" : ..,
> > "type" : "dept_emp"
> > }
> >
> > {
> > "id": departments_n,
> > "dept_no": ..,
> > "dept_name": ..,
> > "type" : "departments"
> > }
> >
> > Where n is ascending from 1 to count(*) of related tables
> > Questions:
> >
> > 1. I want to do view collations from doc.type = employees to doc.type =
> > departments. In SQL, i can easily JOIN these tables using something like
> > "SELECT * FROM employees e JOIN dept_emp de ON e.emp_no = de.emp_no
> > JOIN departments d ON de.dept_no = d.dept_no"
> >
> > Can i do this using view collations? I've been stuck after writting
> this
> > map function:
> >
> > function(doc) {
> > if (doc.type == 'employees') {
> > emit([doc.emp_no, 0], doc);
> > } else if (doc.type == 'dept_emp ') {
> > emit([doc.emp_no, 1, doc.dept_no], doc);
> > } else if (doc.type == 'departments' ) {
> > //What should i emit?
> > }
> > }
> >
> >
> > 2. I've been reading about linked documents, and it seems that it can
> only
> > be used with "_id" : some_id". Can i use another field like "dept_no" :
> > "some dept_no" so i can get all docs with that dept_no ?
>
>
Re: A few questions about View Collation and Linked Documents
Posted by Mathias Leppich <ml...@muhqu.de>.
Hi Mohammad,
there is nothing wrong with the way how you want to store the data in couch, it might just become necessary to store data redundant to make bigger use of it inside the views.
I took your example and put a little sample on my iris couch. see it here:
http://muhqu.iriscouch.com/_utils/database.html?emp_dep/_all_docs
There are 2 views:
dep-emp: To see all employees of a given department:
http://muhqu.iriscouch.com/_utils/database.html?emp_dep/_design/sample/_view/dep-emp
http://muhqu.iriscouch.com/emp_dep/_design/sample/_view/dep-emp?include_docs=true&startkey=[%22dep/001%22]&endkey=[%22dep/001%22,[]]
emp-dep: To see all departments a given employee is/was in:
http://muhqu.iriscouch.com/_utils/database.html?emp_dep/_design/sample/_view/emp-dep
http://muhqu.iriscouch.com/emp_dep/_design/sample/_view/emp-dep?include_docs=true&startkey=[%22emp/001%22]&endkey=[%22emp/001%22,[]]
- mathias
On Jun 23, 2012, at 8:47 , Mohammad Prabowo wrote:
> I have a few questions about view collations and linked documents. Suppose
> i have this schema:
>
> http://i.imgur.com/0xof6.png
>
> The red-bordered tables are what i'm interested in. I
> have successfully convert it into document:
>
> {
> "id": "employees_n",
> "emp_no": ..,
> "birth_date": ..,
> "first_name": ..,
> "last_name" : ..,
> "gender": ..,
> "hire_date": ..,
> "type" : "employees"
> }
>
> {
> "id": "dept_emp_n",
> "emp_no": ..,
> "dept_no": ..,
> "from_date": ..,
> "to_date" : ..,
> "type" : "dept_emp"
> }
>
> {
> "id": departments_n,
> "dept_no": ..,
> "dept_name": ..,
> "type" : "departments"
> }
>
> Where n is ascending from 1 to count(*) of related tables
> Questions:
>
> 1. I want to do view collations from doc.type = employees to doc.type =
> departments. In SQL, i can easily JOIN these tables using something like
> "SELECT * FROM employees e JOIN dept_emp de ON e.emp_no = de.emp_no
> JOIN departments d ON de.dept_no = d.dept_no"
>
> Can i do this using view collations? I've been stuck after writting this
> map function:
>
> function(doc) {
> if (doc.type == 'employees') {
> emit([doc.emp_no, 0], doc);
> } else if (doc.type == 'dept_emp ') {
> emit([doc.emp_no, 1, doc.dept_no], doc);
> } else if (doc.type == 'departments' ) {
> //What should i emit?
> }
> }
>
>
> 2. I've been reading about linked documents, and it seems that it can only
> be used with "_id" : some_id". Can i use another field like "dept_no" :
> "some dept_no" so i can get all docs with that dept_no ?
Re: A few questions about View Collation and Linked Documents
Posted by Simon Metson <si...@cloudant.com>.
Hi,
Right, you can't get the two bits of information you want from the schema you have. If you go for a denormalised structure you can get it in a single request, without even using a view. It means you have to store more data, but you have everything in one place.
That said, making two requests isn't so bad. CouchDB is designed for lots of requests hitting it at once, again its about what resource you trade for what feature.
Cheers
Simon
On Saturday, 23 June 2012 at 07:11, Mohammad Prabowo wrote:
> Well, i thought that by using view collation, we can join those three
> different schema. It seems that i must make two different GET request to
> database: one for view collation between employees and salaries, and one to
> lookup the dept_no by key...
>
> Thanks for the response!
>
> On Sat, Jun 23, 2012 at 6:01 PM, Simon Metson <simon@cloudant.com (mailto:simon@cloudant.com)> wrote:
>
> > Hi Mohammad,
> > I think you need to rethink your document structure - doing a 1:1 port of
> > a SQL schema is seldom a good idea, since so many of the underlying
> > assumptions are different.
> > It looks like you're trying to model an employees history as they move
> > through departments, is that right? If so you could do that in a single
> > document:
> >
> > {
> > "id": "employees_n",
> > "emp_no": ..,
> > "birth_date": ..,
> > "first_name": ..,
> > "last_name" : ..,
> > "gender": ..,
> > "hire_date": ..,
> > "dept" : [{"name": …, "start": … "end": …}, {"name": …, "start": … "end":
> > …}]
> > }
> >
> >
> > Then use a view to pick out current dept. etc.
> > Cheers
> > Simon
> >
> >
> > On Saturday, 23 June 2012 at 02:47, Mohammad Prabowo wrote:
> >
> > > I have a few questions about view collations and linked documents.
> > Suppose
> > > i have this schema:
> > >
> > > http://i.imgur.com/0xof6.png
> > >
> > > The red-bordered tables are what i'm interested in. I
> > > have successfully convert it into document:
> > >
> > > {
> > > "id": "employees_n",
> > > "emp_no": ..,
> > > "birth_date": ..,
> > > "first_name": ..,
> > > "last_name" : ..,
> > > "gender": ..,
> > > "hire_date": ..,
> > > "type" : "employees"
> > > }
> > >
> > > {
> > > "id": "dept_emp_n",
> > > "emp_no": ..,
> > > "dept_no": ..,
> > > "from_date": ..,
> > > "to_date" : ..,
> > > "type" : "dept_emp"
> > > }
> > >
> > > {
> > > "id": departments_n,
> > > "dept_no": ..,
> > > "dept_name": ..,
> > > "type" : "departments"
> > > }
> > >
> > > Where n is ascending from 1 to count(*) of related tables
> > > Questions:
> > >
> > > 1. I want to do view collations from doc.type = employees to doc.type =
> > > departments. In SQL, i can easily JOIN these tables using something like
> > > "SELECT * FROM employees e JOIN dept_emp de ON e.emp_no = de.emp_no
> > > JOIN departments d ON de.dept_no = d.dept_no"
> > >
> > > Can i do this using view collations? I've been stuck after writting this
> > > map function:
> > >
> > > function(doc) {
> > > if (doc.type == 'employees') {
> > > emit([doc.emp_no, 0], doc);
> > > } else if (doc.type == 'dept_emp ') {
> > > emit([doc.emp_no, 1, doc.dept_no], doc);
> > > } else if (doc.type == 'departments' ) {
> > > //What should i emit?
> > > }
> > > }
> > >
> > >
> > > 2. I've been reading about linked documents, and it seems that it can
> > only
> > > be used with "_id" : some_id". Can i use another field like "dept_no" :
> > > "some dept_no" so i can get all docs with that dept_no ?
> > >
> >
> >
>
>
>
Re: A few questions about View Collation and Linked Documents
Posted by Mohammad Prabowo <ri...@gmail.com>.
Well, i thought that by using view collation, we can join those three
different schema. It seems that i must make two different GET request to
database: one for view collation between employees and salaries, and one to
lookup the dept_no by key...
Thanks for the response!
On Sat, Jun 23, 2012 at 6:01 PM, Simon Metson <si...@cloudant.com> wrote:
> Hi Mohammad,
> I think you need to rethink your document structure - doing a 1:1 port of
> a SQL schema is seldom a good idea, since so many of the underlying
> assumptions are different.
> It looks like you're trying to model an employees history as they move
> through departments, is that right? If so you could do that in a single
> document:
>
> {
> "id": "employees_n",
> "emp_no": ..,
> "birth_date": ..,
> "first_name": ..,
> "last_name" : ..,
> "gender": ..,
> "hire_date": ..,
> "dept" : [{"name": …, "start": … "end": …}, {"name": …, "start": … "end":
> …}]
> }
>
>
> Then use a view to pick out current dept. etc.
> Cheers
> Simon
>
>
> On Saturday, 23 June 2012 at 02:47, Mohammad Prabowo wrote:
>
> > I have a few questions about view collations and linked documents.
> Suppose
> > i have this schema:
> >
> > http://i.imgur.com/0xof6.png
> >
> > The red-bordered tables are what i'm interested in. I
> > have successfully convert it into document:
> >
> > {
> > "id": "employees_n",
> > "emp_no": ..,
> > "birth_date": ..,
> > "first_name": ..,
> > "last_name" : ..,
> > "gender": ..,
> > "hire_date": ..,
> > "type" : "employees"
> > }
> >
> > {
> > "id": "dept_emp_n",
> > "emp_no": ..,
> > "dept_no": ..,
> > "from_date": ..,
> > "to_date" : ..,
> > "type" : "dept_emp"
> > }
> >
> > {
> > "id": departments_n,
> > "dept_no": ..,
> > "dept_name": ..,
> > "type" : "departments"
> > }
> >
> > Where n is ascending from 1 to count(*) of related tables
> > Questions:
> >
> > 1. I want to do view collations from doc.type = employees to doc.type =
> > departments. In SQL, i can easily JOIN these tables using something like
> > "SELECT * FROM employees e JOIN dept_emp de ON e.emp_no = de.emp_no
> > JOIN departments d ON de.dept_no = d.dept_no"
> >
> > Can i do this using view collations? I've been stuck after writting this
> > map function:
> >
> > function(doc) {
> > if (doc.type == 'employees') {
> > emit([doc.emp_no, 0], doc);
> > } else if (doc.type == 'dept_emp ') {
> > emit([doc.emp_no, 1, doc.dept_no], doc);
> > } else if (doc.type == 'departments' ) {
> > //What should i emit?
> > }
> > }
> >
> >
> > 2. I've been reading about linked documents, and it seems that it can
> only
> > be used with "_id" : some_id". Can i use another field like "dept_no" :
> > "some dept_no" so i can get all docs with that dept_no ?
> >
> >
>
>
>
Re: A few questions about View Collation and Linked Documents
Posted by Simon Metson <si...@cloudant.com>.
Hi Mohammad,
I think you need to rethink your document structure - doing a 1:1 port of a SQL schema is seldom a good idea, since so many of the underlying assumptions are different.
It looks like you're trying to model an employees history as they move through departments, is that right? If so you could do that in a single document:
{
"id": "employees_n",
"emp_no": ..,
"birth_date": ..,
"first_name": ..,
"last_name" : ..,
"gender": ..,
"hire_date": ..,
"dept" : [{"name": …, "start": … "end": …}, {"name": …, "start": … "end": …}]
}
Then use a view to pick out current dept. etc.
Cheers
Simon
On Saturday, 23 June 2012 at 02:47, Mohammad Prabowo wrote:
> I have a few questions about view collations and linked documents. Suppose
> i have this schema:
>
> http://i.imgur.com/0xof6.png
>
> The red-bordered tables are what i'm interested in. I
> have successfully convert it into document:
>
> {
> "id": "employees_n",
> "emp_no": ..,
> "birth_date": ..,
> "first_name": ..,
> "last_name" : ..,
> "gender": ..,
> "hire_date": ..,
> "type" : "employees"
> }
>
> {
> "id": "dept_emp_n",
> "emp_no": ..,
> "dept_no": ..,
> "from_date": ..,
> "to_date" : ..,
> "type" : "dept_emp"
> }
>
> {
> "id": departments_n,
> "dept_no": ..,
> "dept_name": ..,
> "type" : "departments"
> }
>
> Where n is ascending from 1 to count(*) of related tables
> Questions:
>
> 1. I want to do view collations from doc.type = employees to doc.type =
> departments. In SQL, i can easily JOIN these tables using something like
> "SELECT * FROM employees e JOIN dept_emp de ON e.emp_no = de.emp_no
> JOIN departments d ON de.dept_no = d.dept_no"
>
> Can i do this using view collations? I've been stuck after writting this
> map function:
>
> function(doc) {
> if (doc.type == 'employees') {
> emit([doc.emp_no, 0], doc);
> } else if (doc.type == 'dept_emp ') {
> emit([doc.emp_no, 1, doc.dept_no], doc);
> } else if (doc.type == 'departments' ) {
> //What should i emit?
> }
> }
>
>
> 2. I've been reading about linked documents, and it seems that it can only
> be used with "_id" : some_id". Can i use another field like "dept_no" :
> "some dept_no" so i can get all docs with that dept_no ?
>
>