Creating a separation model from an existing solution

[eyeguy]

It is with great excitement that I plan to re-create my existing solution (For my medical practice) using the separation model concept.

I have looked at Matt Petrowski's tutorials but they assume much knowledge and don't give step by step instructions.

What I don't get is do I need to keep the relationships in the data file or can I delete them all leaving only pure data tables?

Aren't the relationships between these tables established in the UI file?

My approach would be:

1.Duplicate the file

2.Take one and save as clone- becomes the UI file

3.Take the other and delete everything from the relationship graph

4.Delete all layouts except the basic one which pertain to the tables

5.Point the IU file to the Data file and re-link all the table occurrences

Am I right here?

Can anyone point me to an article/video or book on this subject?

Joseph

[Vaughan]

What benefits do you expect to get from the separation model?

The relationships need to be in both the data and interface files. Calculations in the data file still need the relationships there to do their thing, and they cannot see anything in the interface file. (This is where the "ideal" of the separation model as implemented in FMP begins to show some cracks.)

Make 2 full copies of the original solution -- name them interface and data. The data file can stay as it is.

Start the interface file by adding an external file reference that points to the data file. In the relationship graph in the interface file, change each TO from the "local" tables to the table in the data file. Make sure all the fields used in the relationship remain the same -- they should because the field IDs are the same.

Check the value lists and make sure they point to the correct file. (Decide which value lists should be in the data file and which in the interface file.)

Once this is done, delete the "local" tables from the interface file.

Make backups at every step, especially before deleting the local tables. It's likely that you will find something has a broken reference, indicating that it should have been changed before deleting the local tables.

The"old" interface can be removed from the data file at some time.

[LaRetta]

The relationships need to be in both the data and interface files. Calculations in the data file still need the relationships there to do their thing, and they cannot see anything in the interface file. (This is where the "ideal" of the separation model as implemented in FMP begins to show some cracks.)

I would suggest a slight modification to this portion, Vaughan, only SOME of the relationships are necessary in the data file. You will need a table occurrence group of your primary tables connected, e.g., Contacts -> Invoices -> LineItems <- Products to supply over 99% of your calculations and auto-enters - most calculations and auto-enters use the primary relationships. There should be little need for additional occurrences in the data file.

And with summary fields, conditional formatting and layout variables, you can eliminate many other calculations. It is amazing how complex a structure can be accomplished with very little structure in the data file. :)

[eyeguy]

Thank you for your responses.

My reasons are three fold.

I need to remotely log in to the system as I have 3 country locations and the file is hosted in my office in Melbourne on filemaker server running on an Xserver.

This at present is painfully slow. So my first reason is for speed.

Secondly we have 7 users, therefore I can work on the interface file at any time without waiting for downtime.

Finally, its an opportunity to "clean" everything up.

I plan to try to avoid/minimize any calculations on the data side. Given this do I really need relationships on the data side?

Joseph

[LaRetta]

Hi Joseph,

Separation model won't help with speed (that I know of) unless you place the interface file on local stations. Second reason? Agreed. And that alone is reason enough in my book ;-)

I do not think you can or should do away with calculations entirely. If you plan on replacing calcs with scripting only, that would not be wise. Calculations are not bad things and relationships in the data file aren't either. It is just that you will probably only need primary relationships.

[Vaughan]

My reasons are three fold.

So my first reason is for speed.

Secondly we have 7 users, therefore I can work on the interface file at any time without waiting for downtime.

Finally, its an opportunity to "clean" everything up.

I'll say that NONE of these is a good reason to use the separation model.

[eyeguy]

Interesting!

I would have thought that running the UI file on a local machine which is in the country and accesses the Data file on the server (in the city) would result in a tremendous performance benefit

[dansmith65]

Interesting! I would have thought that running the UI file on a local machine which is in the country and accesses the Data file on the server (in the city) would result in a tremendous performance benefit

I thought (and still think) it would too, but haven't tested it yet.

Hi Joseph, Separation model won't help with speed (that I know of) unless you place the interface file on local stations.

This seems to confirm that you would see a performance increase.

I'll say that NONE of these is a good reason to use the separation model.

Would you mind elaborating on that? I thought these were valid reasons (considering the UI file would be on client computer).

Granted, I think there are likely better ways to imporove performance, but that isn't to say there wouldn't be a performance increase by using seperation method, and putting the UI file on the client.

[Vaughan]

There is a large amount of work implementing the separat ion model, especially to convert an existing one.

If the main aim is to improve performance then separation may not help at all.

Id suggest going to separation after all other optimisation efforts have been implemented (minimise graphics, minimise data transfervetc) because these won't be wasted.

Eg, say the solution stores and displays images. Going to sep won't help. Creating a reduced res image for display will help.

[Rick Whitelaw]

I've been very tempted by the Separation Model just from a theoretical point of view, because I really don't need it. Every time I take a close look at some of my routines, especially older ones, I see many performance gains to be achieved even without going anywhere near the SM.

The big advantage I can see is "in the enterprise" . . . Any situation where UI updates need to be distributed without disturbing the data. Then again, scripting imports is a very common practice.

My goal, if designing a system using the SM, would be to literally make the data file consist of raw input only . . . Keystrokes, no calculations. Does anyone do that?

[Vaughan]

I've been very tempted by the Separation Model just from a theoretical point of view, because I really don't need it. Every time I take a close look at some of my routines, especially older ones, I see many performance gains to be achieved even without going anywhere near the SM.

The big advantage I can see is "in the enterprise" . . . Any situation where UI updates need to be distributed without disturbing the data. Then again, scripting imports is a very common practice.

My goal, if designing a system using the SM, would be to literally make the data file consist of raw input only . . . Keystrokes, no calculations. Does anyone do that?

This is the thing... You cannot make "pure" data separation. Also, I have rarely had any update situation that hasn't also required a change to data -- especially in the early to mid stages of development.

[LaRetta]

No, you can't have pure separation. And yes, you might have to make more changes in the data file if your solution is still young (and occassionally throughout) but that is easily off-set by time saved not migrating. I've designed some large systems using both separation and not. Here are the reasons that *I* prefer using separation and these reasons probably aren't the top reasons at all - but they are important to ME:

Transfer of Data (the best solution is NOT moving data)

If you use single file and design in another version (so Users can continue working in the served version) you have to migrate the data from the served file to your file and that can take hours even when scripted. Every time you move the data, you risk something going wrong and the likelihood increases each time you migrate.

With separation, the data file remains untouched.

Import Mapping (unsafe and time consuming)

Scripting the automatic export and import on every table in the single file solution takes time as well. Every export and import script must be opened and checked because you have added (and probably deleted) fields. You can make a mistake here. And even if you don’t, the 'spontaneous import map' bug might get you. It is very time-consuming and difficult, dragging a field around in the import maps (FM making it very difficult with the poor import mapping design).

No data export/import or mapping with separation.

Data Integrity (and User Confidence at risk)

When working in a single served solution, you risk records not being created when scripts run (if you are in field options calculation dialog), triggers firing based upon incorrect tab order that you just changed on a layout, and a slew of insidious underground breaks most that you will never see until much later. By then, all you will know is that it broke but not why. You risk jangled nerves and lost faith of your Users who might experience these breaks, data loses, screen flashes and other oddities. A User’s faith in your solution is pure gold and it should not be risked needlessly.

External Source Sharing (provide ‘fairly’ clean tables for integration needs)

It has been discussed many times … why we have to wade through a hundred table occurrence names (particularly if using separation model anchor-buoy of which I'm not a fan anyway) and also have to view hundreds of calculations (which external sources don’t want). It can be a nightmare.

Much better with a data file – where calculations are kept to minimum and the table occurrences are easily identifiable base relationships and easy to exchange with MySQL, Oracle and others. If FM made calculations another layer (separate from data) then FileMaker would go to the top instantly … rapid development front-end and true data back end. But I digress …

Crashing (potential increases when Developing in live system)

Working in developer mode (scripts with loops, recursive custom functions), means that there is higher likelihood of crashing the solution when designing - admit it. If you crash the solution while in the schema, it is (usually) instant toast but regardless, you SHOULD ALWAYS replace it immediately (run Recover, export the data and import it into a new clean clone).

With separation model, you never design live so it is moot. No trashed file means no recover and no migration. You are designing as we all should … on a Development box.

Transferring Changes (even if careful)

Working in live single file system, a change you think is small might have unseen and unexpected side-effects. That potential danger increases as the complexity of your solution grows. This is why we all should beta test changes before going live. But even if careful and even if you design on Developer copy and test it, document your changes and then re-create those changes into the served file (eliminating the data migration portion completely), you risk mistyping a field name, script variable, not setting a checkbox and so forth.

Separation also can risk error because it can mean adding a field or calculation to the Data file but it is very small percentage of the changes we make (except very early); most changes are layout objects, triggers, conditional formatting…UI changes. It is more difficult to replicate layout work between files. And most changes in the data file occur before the solution is even made live the first time.

Same Developer experience (Developer works in single file regardless)

In separation, all layouts and scripts reside in the UI no matter how many Data files are linked as data sources. The only time you have to even open the Data file is to work in Manage Database. Added ... or set permissions.

Fewer Calculations (means faster solution?)

If one decides to go with Separation, the goal is no calculations or table occurrences in the data file but that goal is not obtainable. Since this is your goal, you try to find alternate methods to achieve the same thing to avoid adding calculations (which you should do anyway). You will use more conditional formatting, Let() to declare merge variables, rearranging script logic to handle some of it … there are many creative options. And once you start down that path, you will find that you need fewer calculations than you ever imagined.

Separation model means I can work safely on the UI aside and quickly replace it while leaving the data intact with the least risk. Some calcs need a nudge to update and privileges must be established in the data file as well – those are the only drawbacks I have experienced with it. I have never placed the UI on local stations. I have heard (from trusted sources) that it increases speed. There was a good discussion on separation model here: https://fmdev.filema...age/65167#65167

Everyone has their opinion. I did not want to go with separation and I was forced into it the first time (by a top Developer working with me, LOL). I am glad I was. Once I understood its simplicity and that everything happens in the UI, I was hooked. Of course if it is a very small solution for one person then I won't separate but for a growing business with data being served, I will certainly use it.

Next time that, in your single-file solution, you go through a series of crashes because of network (hardware) problems and you have to migrate every night, think about separation. Next time you need to work in field definitions but can't because Users are in the system, think about separation. Most businesses are constantly changing and that means almost constantly making improvements to a solution. You can NOT guarantee that changes in Manage Database or scripts will not affect Users in the system.

Risk is simply not in my dictionary when it comes to data. Enough can go wrong even when we take all precautions. I don't dance with the devil nor do I run with scissors.

Joseph, I think it would be easier to start from scratch because it makes you re-think each piece. And, as you learn, you will find better ways of re-creating each portion of it. I would also wait until the next version arrives to take full advantage of its new features. It won't be long now.

[dansmith65]

Very thorough post, LaRetta!

[David Nelson]

WOW. Two wows in one day. I went with the Separation Model because several top people suggested it on various posts. But this is the first time I had this kind of information about the down-and-dirty daily life of design. I rated it earlier but then had to come back and say thank you, LaRetta, for taking the time to share it.

I would love to know about tests putting UI on workstations. That makes a lot of sense too because, from what I understand which isn't much, there would be less calls to the server if the UI is on client system. If anyone knows of such tests, please respond. Joseph and I and apparently Dan would sure like to know.

[Vaughan]

No, you can't have pure separation. And yes, you might have to make more changes in the data file if your solution is still young (and occassionally throughout) but that is easily off-set by time saved not migrating.

Migration is for little girls. REAL developers do it LIVE!

Bwah ha ha ha!

<Just had a really strong coffee, and my client gave me some Caramello chocolate so I'm high on caffeine and sugar. LOL>

And yes, another great post from LaRetta. She. Knows. Her. Stuff. :D

[LaRetta]

Oh, Vaughan, you ARE feelin' good! It (being high on chocolate and caffeine) is becoming on you! :yep:

[Vaughan]

WOW. Two wows in one day. I went with the Separation Model because several top people suggested it on various posts. But this is the first time I had this kind of information about the down-and-dirty daily life of design. I rated it earlier but then had to come back and say thank you, LaRetta, for taking the time to share it.

I would love to know about tests putting UI on workstations. That makes a lot of sense too because, from what I understand which isn't much, there would be less calls to the server if the UI is on client system. If anyone knows of such tests, please respond. Joseph and I and apparently Dan would sure like to know.

Most of the server calls are for data. FMP also caches the data once it's down. A local ui file won't have that much affect on performance unless the ui uses huge graphics... In which case it is suboptimal for wan usage anyway.

[eyeguy]

Wow. What a discussion.

I have decided to have a bet each way.

I will leave the hosted file (single solution) on the server and access as per usual on the local network.

I then plan to duplicate it. save as clone, and access the hosted file from my remote locations.

That way I can see how difficult it is and who performance varies with the two approaches

[Vaughan]

An excellent decision. :)

[LaRetta]

I forgot to mention another reason I like separation model.  There is no need to back up the UI.  The Developer keeps a copy of it aside and it only needs backed up when it changes.  So why waste space and resources backing up the entire thing every time?  With separation, you can back up Data separately.