The Separation Model – Part 3
In this Part 3 about the Separation Model, we head into the actual structure of the data. I start to address the issues you're faced with when trying to keep the data file as clean of "extra stuff" as possible. One REALLY good thing to ALWAYS remember is that adding more stuff to your FileMaker file means more to manage (and re-figure out) later on.
When it comes to using the separation model, you've always got a few things to worry about. The first of which is creating related records. As with all things, there's always more than one way to tackle something. Certainly, you can use FileMaker's default method of adding related records by simply throwing a portal onto the layout and connecting a data table occurrence with another data table occurrence. You could also continuously add fields until your tables have thousands of them (hint, not a good idea).
Within this project, I've opted to use FileMaker's Obligatory Create (yes, it's my own made up term for what FileMaker does). This is in contrast to adding a more generic record creation script within the data file and having that script report the id of the newly created record. The later approach is a method which I first saw at Pause on Error in Portland where the MVC (model/view/controller) was the topic of focus.
Using a Global Transfer method, in conjunction with an Obligatory Create, you gain a number of advantages. You can pre-validate your data, ensure requirements have been fulfilled and apply any data formatting prior to actual storage.
These are the topics discussed in this video, plus anything else related to moving the solution forward. One interesting thing to note is that nothing is permanent within your solution. When you start to investigate the files, try to find where things aren't quite working and see if you can figure out how you would solve them. Hint, the counts for items within the filtered portals on the Edit tab are not functioning properly. They'll need to be fixed.... which is coming up in the next video in this series.
Comments
Data file Filemaker Pro 10 version?
Hello, Matt.
Fantastic series. Thank you. I've wanted a clear explanation of the Separation Model ever since first hearing of it.
Is there any chance of getting a Filemaker 10 version of the KarateData file?
Thanks again for a great resource.
Reb
FileMaker 11 files will open in FileMaker 10
While the series is focused on working within FileMaker 11, using such features as merge variables, there are ways to resolve such differences.
The good news is that pretty much any file created in the .fp7 format can be opened by (theoretically) any version going back to FileMaker 7. The most likely candidates, however, are 9, 10 and 11. For this series, 9 won't cut it though, you REALLY need the script triggers introduced in FileMaker 10.
So just open the file within 10 and you should be good. If something like merge variables is looking funny on the layout then you'll need to adjust the technique being used.
Fortunately, future videos will address my current use of merge variables (as of this part in the series)
Matt
-- Matt Petrowsky - ISO FileMaker Magazine Editor
No luck
Thank you very much for answering, Matt.
KarateApp.fp7 opens in FM10, but KarateData.fp7 won't open with it as a related file. The alert reads, 'This is verion 10.0. "KarateData.fp7" was created by version 11.0 of FileMaker and cannot be opened by this version. Please use version 11.0 to view this file.'
Trying to open the file KarateData.p7 by itself directly from FileMaker gives the same message:
Any other suggestions?
I'm using Filemaker Pro Advanced 10.0v3.
Attributes table for multiple uses?
Matt, one thing I am trying to sort out is the use of the Attributes table for multiple entity tables. I love the Attributes table idea and you've done a good job making it clear how to use it. However, in my current project, I need to have Attributes for several entities; for example, I need Attributes for both people and books. In this case, how do you deal with the foreign key fields for best readability? I see a couple primary options:
* use a generic foreign key field name, such as id_Entity...perhaps pre-/suff-ixed with something that will indicate it's a generic use field..maybe id_ForeignEntity?
* create a separate foreign key field for each other table to which I'm connecting. In this case I'd then have id_People and id_Books in there and just use the appropriate field for each connection, ignoring the other.
I couldn't find anything at filemakerstandards.org that addresses this situation. If I missed it, I apologize and will gratefully accept a pointer. If this conversation should be moved over there, let me know and I'll be happy to post it there.
Thank you!
--
Matthew Miller
experientia docet
Attributes table - just found it!
I just carefully re-read the Field Naming conventions, not believing that you hadn't already addressed this, and found the relevant section (Shared Keys, in http://filemakerstandards.org/display/cs/Field+naming). Sorry to be a bother - I'll try re-reading _first_ next time! :)
--
Matthew Miller
experientia docet
You can always qualify an attribute via categorization
In the case where an Attributes table is being used for multiple entities, such as for People and for Events, then you can use one foreign key field named something like id_Any. You would then add an additional field to the relationship - the relationship which allows creation.
Something like the table name would work - like tableName field. You then have the key from the foreign table and you know which table the data belongs to.
Of course, using one single Attributes table for many other tables is simply one design approach. There is little cost to having a unique attributes table for each table which needs one. The advantages should be thought through when it comes to using one single table. Unfortunately, this typically comes down to the design of the database and there are no hard and fast rules to follow.
-- Matt Petrowsky - ISO FileMaker Magazine Editor
Attributes table vs. Checkboxes table?
Another question - I have a large number of Boolean values needed in one of my tables (eg: for a student, whether we have permission to administer medicine, whether we can use photos of that student, etc). I am thinking that I should probably create a separate table for these, rather than use the standard Attributes table, so they can be stored as numeric boolean values, for storage efficiency. This would mirror the Attributes table structure, but use a Numeric "Value."
Is this reasonable? Are there any gotchas I'm missing with this?
--
Matthew Miller
experientia docet
Data can be structured however you wish
The ramifications of how you structure something aren't always obvious up front. The more experience you have, the better you have a good sense of what will and won't work.
The best approach is to ask as many questions as you can. For example, "Is the setting of 'Administer medicine' specific to this person only? Will I be accessing this data from this table? Does it need to be stored in its own table? Why? What is the cost of storing the data 'Administer medicine' versus 1?"
In the case of storing the bits of an address into a Attributes table it makes sense because it's possible for any given person to have more than one address - therefore allowing for an unlimited number of addresses. In the case of a singular true/false attribute, it doesn't make as much sense.
In some future videos of the KarateApp, I'm going to be showing a few different methods of classifying data and using things like data mapping associations (essentially an associative array) and other tricks that avoid potential pitfalls with default "assumed" FileMaker development.
In your situation, I would add these as simple Boolean fields within the table. Meaning the fields would be named something like the following (within the People table)...
canAdministerMedicine
photoUseAllowed
Follow up questions might deal with data analysis.
"Are we ever going to search for students who have these attributes of x, y and z?"
It always boils down to "What am I putting in, and what (and how) do I want to get out?". Fortunately, it's very easy to change the way data is being stored and to migrate from an older model.
Yes, it may be a hassle, but it's certainly doable.
-- Matt Petrowsky - ISO FileMaker Magazine Editor