When your table has keys, why are they not specified in the masterfile? or are the keys supposed to be listed first regardless of the order of fields?

For a flat file, or a Focus database file, SUFFIX=S[n] or SH[n] identifies the sort keys of a segment.

When support for relational database tables was added, it was apparent that SEGTYPE=S3 (when the primary key has 3 components) is inappropriate, since relational database tables generally have no declared order. So the convention is to leave segtype=S0, and introduce KEYS as an attribute in the access file, along with other SUFFIX-specific attributes.

To answer your other question -- the fields (columns) need not be listed in their order in the underlying database; and the [n] components of the primary key must be listed first, to allow WebFOCUS to determine which ones the KEYS=[n] declaration refers to.

WebFOCUS uses the information about primary keys to determine whether a Join is one-one or one-many.

For the case of performing TABLE requests on relational databases: Is there a solution for fields that have a non-unique index over them?

It would appear that MFDs (and therefore JOINs as well) are only able to see key fields that are (part of) the primary key, if the table even has such a thing as a PK. Some types of tables don't, such as financial balance tables.

If WF can't see a suitable key, in our experience it has a tendency to draw in the entire table from the DB and perform the JOIN on the focus side. Not fun with those balance tables I mentioned earlier, there are millions of records in there...

The approach WF took for marking key fields seems a little shortsighted, to say the least?

Of course, you are free to create a suitable alternate synonym that lies about the key structure ...