Does Attached Relationship Graph look OK?

[Peter1]

Hi There

I am merrily continuing with my database project, and although all my relationships and what I require them to do are working, I thought I should attach a PDF of my relationship graph to see if anyone can see some alarming errors or possibly a better structure.

I am only 10% complete so there may be parts missing at this stage.

Thanks, Peter

graph.pdf

[Ender]

Hi Peter,

A couple comments:

I would recommend changing your color scheme so that each table's TOs use the same color, and tables that are part of the same module use the same color family. This has helped in organizing my relationship graphs quite a bit.

You have different independant groupings of tables, and this may work as long as you don't need data from a table not included in the current layout's table's grouping. However, I have found it easier to link everything together through the primary relationships (adding extra TOs for various alternate relationships of course.) This lets me easily drop a field of a distant table onto a layout, and as long as the path to that table is correct, see the related data from that table (no need to monkey with the graph.)

For table names, I like to name the TO in the primary relational path with the table name, then name additional TOs by how they are linked. You might use "Issues by Lost Sales" instead of "Issues LOST_Actual". This naming convention is helpful when you are in a dialog trying to choose the right TO for a a field you are adding.

Finally, it looks like you have multiple tables for similar tasks. An example being "Sales" and "Lost Sales". It is generally better to combine similar tables, especially in a case like this when a sale might start out as a "Sale", and later might become a "Lost Sale". Rather than moving the record to a different table, why not combine them and use a Status field to differentiate between 'Active' and 'lost' sales?

[Peter1]

Thanks for your comments Mike

I am going to experiment with the color and renaming suggestions you made - I have been doing it this way and my mind has become in tune with this style of setup. However, this is the first project in FMP7 I am doing so it is worth trying alternatives, as the core of one's "stylistic/naming" approach will become embedded in one's approach to new projects. Better adapt earlier than later, if required.

The reason why I have so many additional references to tables is quite simply I want to have the most direct access to the relevant data. I don't think this is a failure to convert my FMP5 approach to a FMP7 approach.

Take for example "Sales" (Sales is Contract Line Items in effect): the relevant Staff member could be accessed through the "Contracts" table. I have chosen to rather have an additional Staff table directly linked to the Sales table. This way the sale must find a match in 8 staff records. By going "through" the Contracts table the sale would first have to match with one of several thousand Contract records and then get the data from the Staff table linked directly to Contracts. Now I know I am going to be viewing portal lists of sales in various parts of the database, which will include a Staff field. I want this portal to read data as fast as possible. I feel extra tables warrant even the slightest improvement in data reading speed.

The situation mentioned above is prevalent in many situations in my database; largely because many of my "reports" are available on the fly with the use of multi-keys and portals. As users are accessing these screens several times per day, I try, where possible, to make them as fast as possible.

The Lost Sales issue is a little tricky as some "Sales" can be reduced in value/size etc. The Sales table stores genuine sales, while the Lost Sales table stores any adjustments to sales. Not only that, when I report on previous sales periods I want to know exactly what the future sales where at that time. To do this the Lost Sales database also has to calculate any offsets in sales that were not an offset an an earlier time. I hope this makes sense.

Thanks again for your input. Peter