Given a hierarchy of files



Let us try to place the field “Interest(s)”

If we placed it in the Org file, what do we do when two different people belonging to the same Organization have different interests? Simply put we cannot store that information.

If we place it in the Activity file, we find that although this may work well logically as we only discover the interest when we do an activity with them (be that a phone call, a meeting or whatever) it still gives rise to some issues. What say you have 10 phone conversations (activities) with that person, all centered around the same interest? You would end up with a truckload of redundant data.

The end result is clear here: Place interests in the Person file. What may still be less clear is “why?”

The simple answer is to talk through the structure as we just did. You want a more scientific answer?

In what situations, I hear you say? Hey, we’re just returning to the un-implementable Many-to-Many (M2M) relationship.


A new balance has to be struck here: You want as much as possible squeezed down into the M2M file, but at the same time you want to keep it as light as possible. Let me elaborate:

Order
OrderDate
Client Details
Order Number

Product
Product Code
Product Description
Price

OrderLine
This becomes the simple product of the two above. However, it will naturally acquire a few fields on its own:
Quantity

• In Product: Sum(OrderLine::Line Total) will give us total sales of that product
  
  
• In Order: Sum(OrderLine::Line Total) will give us the total for any given order.

Field storage of data from other files.
Let’s digress for a moment: Storage of field values. Did you notice that the Product Unit price is stored in the OrderLine file as a looked up value? Why not simply define a related calculation like “Line Total = Quantity * Product file::Product Price”? Well, what if the price changes? Would you like all your past orders to change? Most likely not!

It is important for every field that is “transported” from one file to another to ask yourself how that should be transferred and stored. There are essentially 3 choices:

• A lookup, which will transfer the value from one file to another and store it in the second file as well. The transfer of value(s) can be triggered by a relookup which will transfer fresh values, so be careful not to do this if you would like to preserve the old values.
• An auto-enter calculation. This will also transfer the value from one file to another but cannot be refreshed. Furthermore, the transfer is a once-off. If for instance in our example above you selected one product and that fetches the price and subsequently you changed your mind and changed the product, the Auto-Enter field would still retain the price of the first product. Not a convenient feature in this case.
• A related calculation field. This is simply a calculation of the type “Line Total = Quantity * Product file::Product Price”. Whenever the related value changes the field changes. This is great if the data must be current in both places. It is also good if it is simply a reference. However, as outlined above some data should be frozen in time, and in that case this solution does not work.

Storage footnote: Bear in mind that lookups and auto-enter calculations store the actual data in the file they exist in. However, a related calculation field will add no data to the file it is in, and this has impacts on search speeds and on the size of the file.

How fields can wreck your Structure
Shipping Date: If we assume that all lines of an Order will always be shipped at the same time the Shipping Date belongs on the Order. However, in real life part-deliveries, back-fills, back-orders and so on are commonplace. If you think that real-life situation may also occur for your system, the Shipping Date needs to be on the OrderLine!

But it gets worse: Consider that the consignment note that goes with each shipment and details what is in the shipment has a note field. The operator can enter any useful details about the order/shipment.

By most people’s logical reasoning this is a field on the Order, you’d probably call it order notes. But as above, what if you need different notes on each physical part-shipment of that order?

This is where design-wise you’d need to make an important choice: Should there really be an extra entity or file inserted in your system?



My answer is probably yes if every single order has many shipments. Your system will quickly become centered around tracking shipments which obviously in this kind of business is important.

But what say the Order ever only has more than one shipment in 5% of cases? There is another solution!

Again, this is not a textbook solution but it is a practical FileMaker based one. After all, you do develop this in FileMaker Pro.

Changing structure to process
The solution is to simply split the order! If you look at the illustration below; the order originally had 5 order lines. After splitting the order (creating a 1st and a 2nd part of that same order number) the first order now has 3 order lines and the second has the remaining two lines.

All it takes to do is a bit of clever scripting and you can do it completely automatically. This is another example of keeping the data structure tight instead of compromising the structure for the sake of something that rarely occurs.



To reiterate the rule: “Place the field where it will be able to accurately represent reality for the vast majority of your dataset and only create a minimum of or no redundancy of data when stored.”


The simple Rules
There can be many more examples like this. Again, this is of course just one example. So what’s the rule, you ask?

• Initially push a field up to either of the two files above it.
  
  
• Check that in real-life situations you can think of, the field can NEVER have different values within the same record (in the example above, within the same Order).
  
  If it could possibly have different values in the record above push it down again, into the M2M file.
  
  

Avoiding fields in the “Wrong” Places and fixing it up when it happens
Back to reality: Fields do end up in the wrong places, so how to fix it up, you ask.

Nobody’s perfect and field placement will inevitably be flawed from time to time. The requirements of the system change and suddenly you see the structure is inadequate.

Here is how to minimize the damage in the first place and how to fix it up if it happens:

• Always use a structured development approach like QuickToolKit. This allows for much easier and more flexible restructuring of your system.
  
  
• Ensure you create an entity-relationship or a file-structure diagram before you start.
  
  
• Ensure that whenever you make changes to the system that affects the structure (like adding a new file), update your diagram. Having the diagram to look at when you’re faced with a difficult question like a restructure means it is usually resolved within minutes.
  
  
• Ensure you follow a logical naming protocol (more about that in a later chapter). One thing is finding and moving the data field in question. That is easy. Another thing entirely is finding the 12 fields that depend on that field in some way.

Wrapping up
So as you can see there are a number of considerations concerning field placements in files and ensuring you get the right field types:

• Move fields up or down to test whether data redundancy is reduced while retaining meaningfulness.
  
• Check data field types to make sure you select the right method of transfer (lookup, related, auto-enter).
  
• Remember that for data constructs that happen rarely you may be better off substituting structure with processes.
  
• Watch out for fields that may wreck your structure.
  
• Use a structured development environment (like QuickToolKit) to make it easier if eventually you have to make changes.

What's Next

In the next issue we will focus on relationships in the context of putting together the overall structure of your solution. Relationships is an often misunderstood area in FileMaker which is why we’ll devote some extra time to the area.

Subscribe today to take advantage of this special offer: Normally the article series is $97 per year but for the time being we’re offering you a 6-month subscription (web only) completely free.

As a special thank-you we will immediately send you a free copy of the DeveloperCompanion™ Lite for FileMaker Pro ($19.95 value)! This will allow you to completely plan your FileMaker development project and make it much easier and faster to make your system right the first time, regardless of what you’re developing.

To subscribe to The•Better•FileMaker•Developer simply follow this link: http://bfmd.net


This publication is copyright ©2003 Coretech System Inc. All rights reserved. This document may not be copied in part or full without express written permission from the publisher.