Multi-to-multi relation with auto refreshable views

[mpretorian]

Hi,

I have a data model like here:
Contact: contact_id(pk), contact_name ( many instances )
Role: role_id(pk), role_name ( few instances ~ 10)
ContactRole: contact_role_id(pk), contact_id(fk), role_id(fk) (many-to-many mapping)

I need to implement several views:
1) one form to edit a Contact and its ContactRoles relations
2) one form to search for Contacts based on Contact details and ContactRoles relations
3) one search result grid to display per one row a Contact and in one column also its multiple ContactRoles (comma separated role names)
4) one read only form to display the selected row in grid 3) displaying the Contact details and its multiple ContatctRoles (comma separated role names)
5) one grid to display per row one ContactRole including the contact_name and role_name

When a Contact is edited in the form 1) and in he same time is displayed in the grid 3) and form 4) and its ContactRelations are displayed in the grid 5) I would like that:
- the related grid 3) row and form 4) to be automatically refreshed with the new Contact details and ContactRoles relations
- the related grid 5) rows to be deleted if that ContactRole was deleted in the edit form 1) or new rows added if new ContactRole was added in form 1)

The question is how to create the datasources and what is the best way supported by the smartgwt framework to use them to achieve all requirements.

Starting with some datasources with the basic definition like here:
Contact: contact_id, contact_name
Role: role_id, role_name
ContactRole: contact_role_id, contact_id, role_id
I tried several approaches to cover all requirements but each of them has some blocking issues.

Approach I):
************
1) The edit form is based on Contact datasource but with one special field based on ContactRole datasource and performs the operations add/update on both datasources ( I think that requires some additional coding)
- uses Role as an option datasource
- all operations are done in a queue

2) The search form is based on Contact datasource
- and uses Role as option datasource to support the "search by Role relation"
Search by Role requires Contact datasource to include role_id from ContactRole datasource, so
Contact: contact_id(pk), contact_name, role_id_filter includeFrom ContactRole

3) The grid performs fetch on Contact datasource
- but requires to display multiple ContactRoles as comma separated role names in one row/column, so the
Contact datasource should
3a) - either have a new field "roles" with multiple values of type ContactRole and the grid will use perhaps a custom formatter to display them, so
Contact: contact_id(pk), contact_name, role_id_filter includeFrom ContactRole, roles as ContactRole type
3b) - or have a new field "role_ids" with multiple values and the grid will use Role as option datasource to transform the ids into values, so
Contact: contact_id(pk), contact_name, role_id_filter includeFrom ContactRole, role_ids
I both cases 3a),3b) the Contact/fetch server implementation should support adding multiple values per row.
I've seen the smartgwtee support for 3a) using Hibernate/JPA, but not for SQL datasources.
Q - Exists any smartgwt server support for 3a) and SQL datasources?
Q - Exists any smartgwt server support for 3b)?
Q - Is this grid 3a)/3b) automatically updated when form 1) updates a Contact and its ContactRoles?
IMO not because two datasources are updated but the grid uses only Contact datasource.
But I'm not sure about 3a) case where ContactRole datasource is included in Contact datasource.

4) The read only form uses the Contact datasource as the grid 3).
For the 3a) case, using a ResultSet on Contact and another one on ContactRole the form 4) could be automatically updated when form 1) performs some updates operations on the Contact/ContactRole displayed by form 4).
Q - Is this going to work for the form 4)? If yes, is this suitable also for the grid 3a)?
But for 3b) case I think not because in 3b) case the PK contact_role_id is missing in Contact datasource, so a ResultSet cannot receive the notifications.
Q - Is any solution for 3b) case?

5) The grid uses the ContactRole datasource that includes contact_name and role_name from Contact and Role datasources, so
ContactRole: contact_role_id(pk), contact_id(fk), role_id(fk), contact_name includeFrom Contact, role_name includeFrom Role
This grid is supposed to be auto refreshed when form 1) adds/updates ContactRoles.
Q - Is this correct?

Q - In this approach the Contact datasource includes a field from ContactRole
and ContactRole includes one field from Contact. Is this going to work such a circular dependency?

Approach II):
*************
Using these datasources:
Contact: contact_id(pk), contact_name, role_id_filter includeFrom ContactRole, roles as ContactRole type
Role: role_id, role_name
ContactRole: contact_role_id(pk), contact_id(fk), role_id(fk), contact_name includeFrom Contact, role_name includeFrom Role

1) The edit form is based only on Contact datasource that includes the ContactRoles, so only one add/update operation on Contact datasource.

2) same as 2) from the approach I)

3) same as 3a) from approach I)
Q - same question about auto refresh of the grid when form 1) does an update on Contact and ContactRole together

4) same as 4) from approach I), but the auto refresh should work with only one ResultSet on Contact datasource
Q - Is it correct?

5) same as 5) from approach I)
Q - Will this grid be auto refreshed when form 1) adds/updates the Contact and ContactRoles together?

Aproach III):
*************
Same as II), but
5) The grid uses the Contact datasource but it displays multiple rows per Contact. I'm not sure how can that be achieved, unless the grid is filled manually with rows from an external ResultSet.
Q - Is this a possible solution?
That external ResultSet could observer the changes of Contact datasource and update the grid.


I hope that I'm not the first one that has such requirements and there is a easy and supported solution in smartgwt(ee).
Please add your answers under my questions (see all the Qs) using some prefix, like [ISO].
And if you think that the requirements are too difficult to be supported together please propose which one should be dropped.
I'm looking to use the auto refresh mechanism based on ResultSet, but maybe is too difficult for this case.
Unfortunately without auto refresh those views become out of sync and have to be manually refreshed in many cases.
Q - Even if the auto refresh of these views is achieved, so for example the user cannot see a row in grid 5) that was deleted by form 1), there is still the possibility that another user does that change in the database using other browser and the first user will receive an error when he tries to do something with that row. What kind of error should the client receive and how should be generally handled?

Thank you in advance for the patience to look to such a long post,
Mihnea Pretorian

[Isomorphic]

This really goes well beyond Support as you're basically asking us to design a mini-application based on a set of requirements. To have Isomorphic do this, consider a Consulting engagement.

As far as Support, what we can tell you is:

1. you seem to be treating ResultSet auto-refresh as a kind of mystical black box, but it's really a straightforward mechanism where you have complete control.

When changes are saved, whatever "fetch" operation is designated as the cacheSyncOperation is called, and whatever it returns is used as the new record.

2. if you save a list of IDs to a field intended as a many-many relation, no, the SQLDataSource will not auto-update a join table. It actually does not have the information necessary to do so if you look at the declarations you provide - an additional declaration would be necessary so that we know the intervening table is intended purely as a join table.

If you want to have a field where you can save a list of IDs and a join table gets updated, you currently need to add server logic to do this. This would be a valid area for sponsorship if you prefer that route.

3. for the same reason, modifying a join table will not auto-refresh related records. Use the server-side API dsResponse.addRelatedUpdates() to cause this refresh to be automatic

[mpretorian]

I posted a design question about how to use a multi-to-multi relation in smartgwt client/server framework, and of course that the requirements are real and more complex then in the showcase.

I also didn't wait you to design everything, but I posted 3 approaches and I expected at least answers to my particular questions, even with a yes/no.
But I've got some answers, that might be good, but I can hardly relate to my questions.
Please be more precise in your answers to my questions so I can move forward with one approach. Still don't know which one is better or feasible at least.

Thank you,
Mihnea
PS: Btw, that mistical black box had recently a fundamental bug in smartgwt. See http://forums.smartclient.com/showthread.php?t=27001

[Isomorphic]

Asking us to design a screen by asking a long list of open-ended questions, each of which would lead to a long discussion of tradeoffs, is still asking us to design a screen :) Again, Consulting is the right avenue for that level of help.

In a nutshell all of your approaches can work. Which one you want to go with depends on a lot of factors - how much data volume there is, how likely you are to reuse data from sub-objects such as ContactRole elsewhere in this screen or related screens, how often the data changes, etc.

To guide you to the right implementation given all the requirements of your project would be a lengthy interactive design process, hence, it's consulting.

What we responded with should have clarified the mechanics of how you can implement these approaches - if it didn't, please ask specific questions about *one particular* approach and how it could be done.

[Isomorphic]

Just to clarify this once more - the various questions you are asking, for which you want at least Yes/No answers, do not actually have Yes/No answers. For several of them the correct answer is "it depends" and would require a lengthy explanation of application design tradeoffs to answer - and these tradeoffs aren't even specific to SmartGWT.

If you want that kind of discussion, you should use our Consulting services.

If you want to know how to implement a particular screen design that you have already decided upon after evaluating your project's unique requirements, that's fine - that's a specific how-to question.