Make join query among kinds in GAE

[Bruce Aloe]

Hello,

In a simple case, suppose i have two kinds (tables) stored in GAE
datastore:

Employee (EmployeeId, Name, Salary, StartDate, DepartmentId)
Department(DepartmentId, DepartmentName)

For Employee kind, EmployeeId is the unique key and DepartmentId is
the join attribute for Employee to make join with Department kind. For
Department kind, DepartmentId is the unique key. Both Employee and
Department kinds have quite a lot of tuples, let us say, more than 3
million tuples.

There is a need to make join query over both Employee and Department
kinds in order to answer the query concerns the data from both kinds.
For example a query could be "List all employees's Id, name, salary
and their department name for the ones earns more than 8000 US
dollar".

How can i make join query among different kinds to answer the query
concerns different kinds?

Of course, there could be more than just two kinds stored in GAE
datastore. If one wants to make join query among more than two kinds,
how to handle that?

Thank you for your help!

Bruce

[Ikai Lan (Google)]

It's not clear to me why you need a join here. If you're just doing it by Department type, why not make the "Department" field a String?

Ikai Lan 
Developer Programs Engineer, Google App Engine

Blog: http://googleappengine.blogspot.com 

Twitter: http://twitter.com/app_engine

Reddit: http://www.reddit.com/r/appengine

--
You received this message because you are subscribed to the Google Groups "Google App Engine" group.
To post to this group, send email to google-a...@googlegroups.com.
To unsubscribe from this group, send email to google-appengi...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-appengine?hl=en.

[Bruce Aloe]

The reason i need to join these two kinds is because i need to know
the department name that the employee works at as you can see the
query "List all employees's Id (EmployeeId), name (Name), salary
(Salary)
and their department name (DepartmentName) for the ones earns more

than 8000 US dollar".

In SQL, i could do the query like this:
select e.EmployeeId, e.Name, e.Salary, d.DepartmentName
from Employee e, Department d
where e.Salary > 8000

That is why i need to join these two kinds.

Bruce

> >http://groups.google.com/group/google-appengine?hl=en.- Hide quoted text -
>
> - Show quoted text -

[Barry Hunter]

I think Ikai means, that you dont really need a 'Department' table.

Just store "DepartmentName" directly as a string on your Employee model.

In a RDBS you traditionally 'normalize' - mainly to reduce database size.

In appengine and the datastore, you denormalize. Disk-space is 'cheap'
so duplicating the string in every record is not really an issue.

http://en.wikipedia.org/wiki/Denormalization

Appengine is based on the premise of write once, read many, to making
your queries 'lightweight' is important too. Completely eliminating a
join makes the query much quicker to run.

(unless you need to query on DepartmentName then you should make it as
not-indexed, otherwise it will eat up space in the indexes)

[Renan Mobile]

Hi Barry Hunter!
If the user wants to change the Department?  How should it be? Change the name from all Employee?

Thanks very much for this discussion!

[Barry Hunter]

You mean rename a department?

Yes you will have to loop though all employees in the department and
change the records.

But I imagine its a pretty rare event, how often does a department
change? So performance isnt a issue. Just run a batch job. Maybe a
task, using a cursor to fire a continuion task if still more.

[Renan Mobile]

I didn't know that is the best practice. Thanks.

But If two users change the same Department name in the same time. A transaction with cursor in a task will ensure a consistent change?

In my application i have assign the Department id (key) to the employee instead of its name. When i retrive all the employeers, i get each Department object by its id assigned to the employeer in a for loop. Then i assign Department 's name to the Employee transport object that will be send to the interface. Did u understand? Is it correct if Department's name is change a lot ( suppose that is)?  Or there is a better way?

Thanks for the fast response and clear answer.

[Robert Kluin]

Denormalizing does not mean you can't have a Department kind.  Personally when I've got something like a department I use an entity to store the canonical name.  I denormalize things like names to make queries perform well, but I also keep a reference property referencing the canonical department entity. it makes updating the department name super easy.

Robert

On Jun 17, 2011 9:45 PM, "Renan Mobile" <renan...@renanmobile.com> wrote:

[Renan Mobile]

I agree with you Robert. This way is much easier to update. I'm happy that someone do like i do.

2011/6/18 Robert Kluin <robert...@gmail.com>

Denormalizing does not mean you can't have a Department kind.  Personally when I've got something like a department I use an entity to store the canonical name.  I denormalize things like names to make queries perform well, but I also keep a reference property referencing the canonical department entity. it makes updating the department name super easy.

Robert

On Jun 17, 2011 9:45 PM, "Renan Mobile" <renan...@renanmobile.com> wrote:

--

You received this message because you are subscribed to the Google Groups "Google App Engine" group.
To post to this group, send email to google-a...@googlegroups.com.
To unsubscribe from this group, send email to google-appengi...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-appengine?hl=en.

--
Atenciosamente,

-----
Presidente Renan Franca
____
Renan Mobile ltda: http://renanmobile.com - Soluções em Dispositivos Móveis (Smartphones) com integração via web.
Soluções em:
        Android (Smartphones);
        Google Web Toolkit (Web);