Concatenated keys vs. multiplle table occurrances

[jtice]

I am rewriting a database in v.8 that has evolved through the years up to v.6. I made extensive use of contatenated keys to store similar data in one file, relate it back to the parent in a number of ways while keeping the number of files reasonable. For example, one file called Action was the child of Client and contained faxes, letters and phone call records. All had the foreign key "ClientID" plus a "type" field which was set by script to one of three values... fax, letter, or call. This was combined by a calc with the foreign key and made the one file do the work of three. I used the same tactic in invoice line items to distinguish service from expense line items, etc., etc.

Now, as I try to organize relationships and recreate scripts in v.8 I am finding it cumbersome. My current issue is that the mulit-criteria relationships that replace concatenated keys generate lots of TO's. Then, each TO must have it's own layout before I can create a script step from that context. Then, the script ends up with so many context sensitive steps that it's hard to just change the keys and make it work for another relationship.

So my question is, do I have a solution where the concatenated keys are simply more efficient than the new tools in v.8, or am I failing to properly get my head around the advantages offered by the new model? Is there any point in even trying to be more efficient by combining tables now that one file contains multiple tables? Any thoughts, general or specific, are greatly appreciated.

John

[Søren Dyhr]

My current issue is that the mulit-criteria relationships that replace concatenated keys generate lots of TO's. Then, each TO must have it's own layout before I can create a script step from that context.

There is somthing wrong in that assumtion, you can make a multicriteria relation between two TO's....

my question is, do I have a solution where the concatenated keys are simply more efficient than the new tools in v.8

Again this seems to be wrong as well, because the real struggle for databases is the indexes ...with concatanated keys will the number of units two smaller indexes far outweight the speed it is to maintain one single with more length to organize.

Try to re-read and pay attention to "...is not an ER":

http://www.filemaker.com/downloads/pdf/techbrief_fm7_foundations.pdf

One thing you have to consider is that you can have several multilinekey fields in one table ...If you should do it in the old fasoin way, should you make a carthesian product of every individual line in both multiline ...well it's possible, but is it healthy to the swiftness, you switch from jive to foxtrot, remember it's a usually a large calc'field that needs to resolve!

To me is it not an either/or but rather a freedom to choose wich is best in the given situation. There was a point in non equijoins under fm7 but Andy LeCates showed at Devcon that it's now a solved issue - they are now almost, if not just as quick as the equijoins.

--sd

[Fenton]

My current issue is that the mulit-criteria relationships that replace concatenated keys generate lots of TO's. Then, each TO must have it's own layout before I can create a script step from that context.

If this is the case, then it sounds as if you have your relationships "reversed", ie., you've switched the target and originating fields. This will indeed cause you to have to create more layouts to work; because layouts need to be tied to the originating field's TO.

[jtice]

Maybe what I'm doing wrong is assuming that I have to actually go to the related table/layout rather than staying on the parent and doing it all through the relationship. So, if I need to create a new record in a child via a script from the parent and then set the foreign key(s) in the child, do I need to actually go to the child TO by means of a layout or can I accomplish all this from the parent just as if I were doing it through a portal? I've been setting the key in a global in the parent and switching to the child by Go to Layout step, creating a new record, setting the foreign keys and going back to the parent. Is all this moving around unnecessary?

Thank you for the responses.

Edited November 4, 2005 by Guest

[Fenton]

Well, the moving around may not be necessary, and there are other methods, using a temporary key and "allow creation of related records", to create child records from a parent table; but I usually do it your way, just because it's simpler and more straightforward (sometimes I use a script parameter instead of a global field, but that's basically the same method).

Yes, you go normally go to a child layout to create the record. But that child layout does not have to be tied to the table occurrence that the relationship from the parent is pointing to. It can be any layout belonging to the child table (AFAIK). So you don't have to create a special layout just for this purpose.

However, calculations, auto-enter calculations, and lookups evaluate according to which table occurrence they are specified to evaluate on (read on). Because the table occurrence is the origin of any other relationships, where the relational line starts from. It is also possible to specify what origin you want to use. By default it is the default first table occurence for that table (created automatically by FileMaker).

But you can override this by specifying "Evaluate this calculation in the context of:", followed by a drop-down list of the base table's occurrences. That is what it's there for. You don't often need it, but it is good to be aware of. This is especially useful (necessary) when you have separate table occurrence groups; which you will if you create anything complex.

So, you can use existing layouts, you don't need to create a specific one. But you need to aware of these other factors.