Relationship Efficiency/Speed Query

[Peter1]

Hi There

I have a query regarding the relationship structure for my magazine publishing business.

The Setup

I have 4 tables - Customers, Contracts, Sales and Reps.

A Rep is allocated to Customers. Contracts typically include about 6 Sales (ie: Contract line items). A Rep is allocated in Contracts as the salesperson. At present I have about 2,000 Contracts with about 12,000 referenced sales from 5 Reps.

The Question

The Rep table is linked to the Customer table which in turn is linked to the Contracts table and in turn to the Sales table. I view lists of Contracts in a portal on a Titles table form where I like to see the Reps name next to the contract. I could pull this name via the relationship mentioned, but I wonder if it is better to rather create another reference to Reps in the relationship graph, that is a direct relationship to the RepID in Contracts.

My logic suggests that the portal would be much slower to draw as Filemaker must "search/match" through the Contracts table and then through the Customer records to "get to" the relevant Rep. Naturally, if the Contract goes directly to Rep it would be much faster?

The situation I mention is repeated in several other similar situations not mentioned here. If I don't want Rep being accessed through Clients and other tables, I would probably need about 20 separate Rep tables in the graph. I want the fastest possible results with the simplest possible graph structure.

I would appreciate any thoughts on this.

Thanks - Peter

[RalphL]

You might consider using a Table Occurrence Group (TOG) for only 1 or 2 layouts. Your graph will then have several TOG's but they will be less complex than a single TOG.

I just did a 4 table database using 5 TOG's The most complex had 7 TO's

[Peter1]

Hi Ralph

Thanks for your response. What do you mean by a TOG? Do you mean, in my situation, I would have a separate linked set of tables in the graph: Reps with the other 20-odd tables feeding to it?

Thanks - Peter

[RalphL]

Table Occurrence Group (TOG) is group of Table Occurrence's (TO's) that are joined together by relationships. One TOG could be Reps TO with 20-odd TO's linked to it. That sounds like a lot considering you only have 4 tables.

I haven't given much thought to your database. The idea I am suggesting is plan your layout. What do you want to see? What table will provide the most data? Then plan a TOG that will provide the other necessary data. This may involve only 1 or 2 additional TO's.

[Peter1]

Thanks Ralph

I have about 30 tables - just 4 in the one scenario I mentioned. The "Reps" issue is just one of others I know I will encounter as I progress. I think, if I understand correctly, the best option is to set up one TOG for the main interaction between data in the database (and scripts). Then to set up separate TOG's when tables need to "read" data - like get the name of the Rep. Possibly another TOG for all the User's custom settings that will be all over the database (eg: multi keys to portals/reports etc.) and another for all the default settings (icons, labels, company name, etc.).

If what I am suggesting sounds crazy, please let me know.

Peter

[RalphL]

I hope that you could get smaller TOG's based on the needs. As I said earlier, start with the layouts, try to make a TOG for a layout. A report would be a good use of a TOG. You might want to have a table of globals. This doesn't need to have a record. Globals don't need relationships.

You will need to experiment with this.

[Peter1]

I remember now that globals don't need a relationship, which is great for all the default settings. As I have only recently converted to Version 7 (from v5) - 1 week ago - I am going to digest your ideas with other ideas in the forum.

Thanks for your time.

Cheers - Peter