FM7 and Normalized db design

[Ugo DI LUCA]

Hi,

While FM7 brought some new relationships optimization tools, and a better Relational Model, it seems to me that we'd need to break the rules by scripts even more than with prior versions.

What do you think ?

[Kurt Knippel]

I do not think so...why do you say this?

[Ugo DI LUCA]

Well,

The more I go into developing with v7, the more I need scripts to make sure data was committed. Some lookups (back to main based on the data entered through portal in related) aren't working at all.

So my idea was to "surely" use the new model, but to include it in optimized script steps.

Though I have no idea how the new version indexing works, how "active" each relation is, and therefore how time consuming related fields and unstored calcs may happen to be.

I had this habit to script any update when any entry was made, so to keep the more index as possible.

[Fenton]

It is certainly true that whereas with 6 you "should" commit records (Exit Record), with 7 you "must." You notice this especially, as you said, with related records, where you set something, usually in a script, then come back to the original table, and the related record is obviously not committed properly.

This happens especially in converted files, where you simply didn't have to commit in 6, so it's not there.

I would recommend as required reading the excellent chapter by Ilyse Kazar in the big Migration Foundations tech paper at the FileMaker site. It even has charts.

It is also true that several scripts steps which you might want to run straight from a button must have a full script, simply because you want a Commit Record at the end. Maybe someday such an option will be available in the button dialog, "Commit Records at end"; but then it would need the "full access" and "skip validation" options also, kind of cumbersome for that little dialog.

I don't know exactly how "indexing" and "related fields" work in 7, but anything to do with related fields is much faster than 6. It removes much of the burden of always having to either stop people from ever searching on unstored fields. Or compensating by running complex operations behind the scenes to speed them up; though in huge files and common script Finds one may still want to do that; but you don't have to worry so much about every related field anywhere.

[Ugo DI LUCA]

Hi Fenton,

Anything to do with related fields is much faster than 6. It removes much of the burden of always having to either stop people from ever searching on unstored fields. Or compensating by running complex operations behind the scenes to speed them up; though in huge files and common script Finds one may still want to do that; but you don't have to worry so much about every related field anywhere.

I had a rule in v6 :

Never check the "Allow creation of Related records" if this new record could be referenced in a calculation in another Table/File.

So, concretely, in a solution running invoices and inventory stock count, you now wouldn't script the 'Quantity on hold' or other Sums you need in the Product File, based on the Transaction File.

You would only rely on the relationships and cross Table calculations without any big loss in speed.

That would be good news.

[Fenton]

Well, it's still nowhere near as fast as an indexed field. It appears to me to be several times faster than version 6 to search on an unstored field. But that's still several times slower than an indexed field; kind of in the middle.

So it's not a paradigm shift. It's more that you can, in lightweight and mediumweight tables (relative terms) allow people to do the odd Find on related fields, without wading in molasses.