After our recent discussion about JOINs I looked over the manual pages about ODBC and ODC, especially the page at https://infoqubeim.com/drupal5/index.php/node/865 (Sync with a fictitious SQL Server database) and I just want to see how well I'm understanding ODBC sync.  Mainly I'm trying to understand how changes/additions/deletes in the remote database would sync to the IQ database.

In this example it seems like the SELECT query joins several tables in the remote database and generates a table with the fields specified (all fields in the Companies table, and from other tables, steward_no2, first_name, last_name, title, telephone_no, telephone_ext, email_address).

I think the values in each of these these fields are synced to IQ fields as specified in the field mapping section of the SQLSync*.xml sync configuration file(s).

I'm guessing that each of the rows in the remote database query results is synced to a single IQ item.  Field values from the remote database are synced to the corresponding fields in the IQ item.  

I guess there has to be one-to-one relation between each remote database query row and each IQ item being synced.  Each remote database query row is synced to an item identified by the field specified in the <RecordIDField> field of the config file.  In this example the RecordIDField is "IDSQL".  And the Field Mapping shows that remote field [company_id] field is mapped to an IQ field named IDSQL.  (I wonder why "CompanyID" is not used as the RecordIDField and the IQ field mapped to the remote company_id field.)  So, after the sync, there is an IQ item that has an IDSQL field that has the same value as the [company_id] field in the remote database.  I'm also guessing that the there will be only one IQ item with that IDSQL value.   So there will a one-to-one relationship between an IDItem item number and the IDSQL value.  

As a result, anytime a row in the remote query results table changes, those changes will sync to the appropriate fields for the IQ item established by the [company_id] -> IDSQL -> IDItem link.  And I'm guessing that if the remote query results contain a new row with a different company_id not already in the IQ database, a new IQ item will be created with that IDSQL value, and that new item's fields will be filled in from the corresponding row in the remote query results table.

If a [company_id] gets deleted from the remote query results, I guess the corresponding IQ item would be deleted from the IQ database?

Now assume we have a different query in the configuration file, to use a typical example, a query to generate a list of orders for each company.  In this case, the orders table would have an order_id as a primary key, and company_id would be a foreign key in the orders table.  Our new SELECT query will generate a list of orders and there may be multiple orders for each company.  In this results table each order_id will appear only once, but there may be multiple rows with the same company_id.  There is a one-to-many relationship between the companies and orders.  In this case, maybe we would change this row in the IQ sync config file:

[company_id] IDSQL

to 

[order_id] IDSQL

Or maybe use a different IQ field than "IDSQL"?  Because while there would be no duplicate company IDs or duplicate order IDs in the remote database, you could a company ID that was the same as an order ID.

Also the field mappings would be changed as appropriate for syncing an orders list as opposed to a companies/contacts list.

Also I guess that a different IQ Y/N value would be set, for example "Orders".  I guess this would be done by including this line in the Field Mappings: "1 Orders", just as "1 Contacts" was used in the example.

Finally, am I right in thinking that this syncing is one way only, from remote database to IQ, and not from IQ to the remote database?

And if the syncing is Remote to IQ only, would it make sense to make the synced records/fields read-only on the IQ side?  Or maybe this is already in place?  I think you would want the ability to add fields to these synced records on the IQ side, and to edit the values in these added fields from IQ.

I hope I have gotten at least some of this right!

The reason I'm thinking about ODBC is, I'm wondering whether some types of data might be better put in external databases (defined tables joined as needed) and synced to IQ as needed, rather than entered into IQ from the start.  I haven't thought very far along those lines though.