As an example, suppose we are dealing with a simple problem of persons' favorite books. So I have a Person table, a Book table and a Favorite table containing the (PersonId, BookId) pairs.

To manage the favorite, i.e., add a book to a person's favorite, remove a book from a person's favorite etc., I can see two solutions:

1. Have two classes Person and Book, and add a favorites collection attributes to the Person class, so the add/remove will be operations on the collection.
The part that I don't like about this approach is that now we need to create the full Book objects for the add/remove, although they only need to work against the Favorite table where ids are enough. This could be a performance hit if the Book class is complicated and involves other tables etc.

2. Have three classes Person, Book and Favorite, so that the add/remove can work with Favorite directly.
The problem with this is that the Favorite table will generate a composite-id containing the id pairs. To support the operations such as getting the list of ids of a person's favorite books, it seems native SQL queries are needed, unless a surrogate key is added to the Favorite table, which I really don't like.

Did I miss anything in the above solutions? Are there any better solutions to the problem?

Hi,

In option 2 for listing a person´s favorite books no native SQL is needed everything can be done with HQL and Criterias for Example



Hope this helps....

Hi,

Your relation within tables sounds like Many-To-Many to me. Like, many person will have many favourate books. Going by this relation, you need to have a link(intermediate) intermediate table, which in your case is Favourate.

Cosidering this your solution 1 will not be applicable and solution 2 is the best choice, which you can implement using association as well as HQL.

Sudhir

I guess I'm still trying to resolve in my head the difference between objects and db entity/relations.

Think about an object called process that has an attribute called status. In the object world, you'll have Process which contains a property of type Status that you can get/set. In the relational world, you'll have a process table, with a FK reference (status_id) to a status table. Now I want to set the status of the Process object to a particular status corresponds to a status_id value. It seems to me that it will have to first load the Status object using the status_id, and then set the Process object's status attribute to this Status object. This would seem to require both a load from db (to get the full Status object) and an update to save the new status, while what's really needed for the underlying db is a simple update of the FK reference column.

Is this performance penalty expected, or am I missing something here?