Multi-file or Not?

[bcooney]

I'm starting a new project. There are two distinct systems, Inventory and Activities, that share two tables: Staff and Sites.

Since FM7, I've never developed a multi-file system.

I'm interested in pros vs cons: All one file vs. multi-file.

I could have this be one file, and based on priv set, navigate to the "proper" main menu. However, maybe it would be a good thing to break it out to Inventory (multi-table, of course), Site (actually also multi-table as this is for a school, and each site has a district), Staff (single table), and Activities (reporting system, multi-table).

Would someone be so kind as to debate the two approaches with me?

(This is the system that had me looking at External Authentication, as the first thing that struck me was maintaining multi-file accounts would require building quite an Accounts module. I've done this in single file systems many times, so it's not too scary, but Ex. Auth. seems less work.)

Facts:

Activities table will get to be about 75K records, each year.

Users do cross-use the systems, but only some will access Inventory.

It all will be served on the same FMS10 box.

I might want to build a PHP-front to Activities in the future.

[IdealData]

I've had to run a multi-file system now for 10 years - I cannot ever imagine my 45 file system will ever integrate to a single file as it would be a re-write, there's massive scripting considerations. Even though I must maintain around 50 accounts in 30 of the files I managed to write a script to automatically update user passwords, but it takes around 20 minutes each time I add a new user.

Since FM7 I have developed single and multi-file solutions, but multi-file typically only where there was going to be a lot of image storage, and consequently large file size. I keep the images in their own file as this allows me to develop the main system via remote methods and I just update the main file as necessary. in some ways I'm doing this to help myself, but I'm also helping the client too.

I tried looking at External Authentication too, but I can't get my head round it and sadly the FM documentation just falls short of explaining how to set up the OD service - if you have any guidance then I'd appreciate it.

[David McQueen]

I am with IdealData on a separate file for graphics. It gets large fast and I also like to keep it away from any processing that goes on. I have one solution like that.

I have a few programs that I have kept concurrent in both the 5-6 family and 7-10 family which are by necessity multi file. I am bringing them down to one file now and it is a PITA. So just because I am enduring this chronic pain right now I would vote against multi file on general terms because times change and a conversion would not be fun later.

I can see a separate file that is used for heavy duty processing as opposed to standard database use. Bring data in, process view and then nuke the data. This is the type of thing where there can be data corruption or file corruption so just isolate it for safety's sake.

By necessity a design with a data file and an interface file is multi file. I do not know if the claimed advantages are really there. Typically when you update an interface you end up adding fields because other needs have also changed. So you do end up working on both files anyway. This type of thing also has deployment problems as in many cases the advantages spring from having data served and the interface local. In those cases, a new interface file leads to multiple work station deployments.

On general terms, the 7-10 FileMaker family appears to be a design sea change for FileMaker inc, changing from multi file to single file. So one question that would nag is "What happens in the next iteration? Is multi file support dropped?". Seeing as we are up to version 10 now and major changes happened between 2 and 3, 3-4 and 5-6 and 5-6 and 7-10, it seems that the next iteration and sea change may be sooner rather than later.

Just a few thoughts.

Dave McQueen

[bcooney]

Thank you both for chiming in.

No graphic storage (and if so, I'd probably use refs).

External Authentication is covered quite well in Wim and Steven's white paper, IdealData. It might be the way to go, but I don't like depending on something outside my system, either. One advantage is that since accounts and pwds are not stored in FM, any new version doesn't force a reset of all passwords.

Oh my, can you imagine them dropping multi-file support?! No way.

I haven't developed using Separation Model. I always add fields in new releases, so I don't understand how it helps.

In previous years, they did nuke the Activity data every year (well, backed it off and started fresh). My new design will not encourage that. In fact multi-year data will be helpful in YTD reports.

OK, that's 2 votes for single file. Anyone else?

[David McQueen]

One advantage is that since accounts and pwds are not stored in FM, any new version doesn't force a reset of all passwords.

As long as you have one full access account in the system, you can automate the recreation of accounts. The downside is that you do have to store the inforamtion on those accounts in a table. I have one solution where there is an anticipated upgrade every few years going out to multiple organization. Account re-establishment was part of the migration schema.

[bcooney]

So you store passwords in your User table? Steven would not be pleased, lol.

We do recreate all accounts from the Staff table. In fact, the only way to create an account is thru our scripted system. But we reset all accounts to a default after any upgrade (since the pwds are in FM).

[mr_vodka]

We use multiple files: User Interfaces, Data, Reports, Emails, and a Triage file.

Well after years of development, I will never go back to single file.

Why?

Yes, you have to work a little harder when using a SM, but still there are plenty of times where the client has just asked for a new layout design etc where no new fields needed to be created. Also, if ever something in your UI file gets corrupt then all you have to do it swap it out with a good copy of it instead of having to migrate all the data into fresh clones. I also store all the system images in the UI file, so it is not a pain like when you have to migrate the container fields in a single file solution. It is also easier when dealing when using ODBC to connect to FMS.

Then I have my reports file. Well 65% of the time when a client asks to make a change, it is for a report. I can modify these reports files rather easily and have an individual one for each separate client, while still maintaining the rest of the system commonly across multiple clients. Furthermore, if you have a client that would like to tweak their own reports, you can just give them access to that file and not the others.

Emails. We have hundreds of emails a day but we keep track of each one sent out. There is no reason to clutter up the data file with extra records that are historical than anything. Also not everyone has access to view the past sent emails.

Triage. We have a triage file that stores some of the information from a MS SQL database and rest of the info ESS is used. Not every records in the SQL db will be used in our system. However, some notes and a status field will have to be updated in this file. There is no point to keeping all this in the main data file as only a select few have access to it and perform the triage.

When I create systems with multiple file, I always ask myself could these files live independently (for the most part) by themselves. Can I sell these other parts as separate modules? Not all customer will want an email module since they have to pay more. Not all customers will need a triage service. Every customer will need their own reporting. It is just much easier maintenance.

We also have another file/module that take customers' pre-existing backends and pushes it into our system but that is a whole other beast.

Just my 2 cents.