I think the problem with Modeler isn't this particular workflow, it's generally too complicated.
Maybe it would be easier on users if they could edit relationships directly, instead of having to jump around between the tables on the two side of the relationship. Here's a simplified workflow for the standard case, just as an example. * Select a table. * Say "relationship with other table". * Have a four-column display. Columns 1 and 4 show the left and right table's fields, with PK fields first. * Let the user drag-and-drop columns from 1 to 2, and from 4 to 3. * Columns 2 and 3 specify the fields that go into the JOIN. The JOIN would be over equality between column values. * Columns 2 and 3 could be pre-filled with the PK fields of the two tables. This would more-or-less automatically Do The Right Thing in most cases. * Have checkboxes that make the relationship both ways, just left-to-right, or just right-to-left. * The one-to-many stuff should not be specified directly but inferred from uniqueness constraints. Those can be partly retrieved in the database schema, but views aren't usually reported, so the user must be able to specify additional uniqueness constraints. (A side is "to-many" if there is no uniqueness constraint with all of its fields in columns 2 resp. 3.) Potential complications: * Flattened relationships. Not sure whether a six-column display or a separate editing dialog for flattening would be easier to use. * Flattened relationships that use an "octopus" table (i.e. a join table with more than two to-one relationships). * JOINs that use computed expressions. Maybe columns 2 and 3 should be editable text fields, and dropping a field in it just edits it to contain that field name. (Uniqueness constraints would need to be expressed over computed expressions as well. If you do SQL parsing you can infer these, at least partially.) * Whether all this table-based relationship specification stuff should actually work from the Java class level, too. Some people prefer to specify on the Java level, some on the database level; in both approaches, specifying stuff on one level should automatically infer the equivalent stuff on the other. Note that while this looks like a full specification, I'm pretty sure it won't be doable that way. I don't know enough about what Cayenne supports to have a chance at writing a complete spec for anything. It's more an example demonstration of the abstraction level at which I as an application programmer am at home, in the hope of giving the Modeler developers an idea what kind of improvements would be most useful. Thanks for reading, Jo
