I need an EXPERT opinion!

[RUBlind]

Hi all!

I have a general FM 12 design question that I am hoping some of the experts here can give me their opinion on.

I have a FM solution that I have been developing since 1999 with FM 4.0. It is currently being used with FM 11v3 on a Windows system. It originally started in 1999 as being a small database for driver contacts and it has GROWN to be much more over the past 13 years.

I work in the Transportation Department for the movie industry. My "Trans" program, as it has become known, is used to run the Transportation Department for many TV shows and movies. I use it myself to run our Transportation Department on Criminal Minds.

I have been reading much about the new FM 12 and I have played with the demo a bit and I like what I see in the design area of the new FM12. I think I would like to make the upgrade to FM 12 but my concern is that my 13 year old program has become bloated and sometimes unpredictable and the CONVERT from 11 to 12 seems to be where there is a lot of problems.

So, I have decided to create Trans 2.0 with FM12 from the ground up. But ever since FM added the ability to have multi tables in one DB I have never been clear on if and when that was the best way to build your solution.

Here is the question... Do I build it the same as before with MANY outside databases or use just one database and make MANY tables inside of the one DB?

As it is now the "Trans" program has MANY databases built around the MAIN file called Trans.fp7 which is the original file I made in 1999 and it is used for the Driver Contact List and through Trans you can open the other databases. The other databases consist of...

Budgets.fp7 1500+ records

Timecards.fp7 1400+ records

Purchase Orders.fp7 500+ records

Petty Cash.fp7 20+ records

Vendor List.fp7 1100+ records

Picture Cars.fp7 300+ records

Callsheet.fp7 10,000+ records

And a few more smaller support databases.

And the main Trans.fp7 has about 1400+ records.

As you can imagine, I have hundreds of relationships, links, lookups etc etc between all these files.

I do not use the stand alone apps from the developer addition, all the users of my program have Filemaker installed on their systems.

At the end or a SEASON or when a movie is over, all the files get "RESET" and all the records are deleted with the exception of the Trans file Driver Database of 1400 records and growing and the Vendor Database at 1100 records and growing. Because on the next show you will still use the same drivers and vendors.

I use my solution on a Windows machine but I do have others who use it on a Mac either native or they use parallels and run it in windows.

Before I start this daunting task I would love to get some of your opinions on what has been you experience of building muilti file DB's or using the multi table option.

Any input you have in this discussion it greatly appreciated!

Robert Dulys

[Ocean West]

Robert

Welcome to the forums! - (btw I enjoy Criminal Minds)

The deciding factor of building all tables in one file vs having many files vs having one UI & Data file is not an easy decision - but keep this in mind that if you START as a single file it is much EASIER to split it out in to separate files if future requirements dictate. Merging multiple files into one is much more daunting.

Also too the context of the permission levels and intended audience is also something to consider - if a set few people never need access to a particular file, a specific interface file could be used to present options only available to them - in concert with established permissions.

With FMP 12 you may be able to take advantage of the new functions for Execute SQL this may eliminate many relationships for driving user interface elements or

other reporting requirements, speeding up your redevelopment efforts.

And please don't discount the use of plugins - there are many out there that will solve a task that FMP can't do natively - or does it more effectively. And can shave days of trying to build a native work around.

I assume you didn't take the time to rebuild when the last file conversion happened at v7. So you have the golden opportunity to touch areas of your solution that work but could be enhanced to make your life and that of your end user easier -

From a user standpoint interfacing with a single file is easier work with - and will also play much nicer with FMGo should you deploy for that environment.

Cheers

Stephen

[genevieve charbon]

There's no problem converting 11 to 12, aside the problem that 12 is slow as hell no matte what, and buggy, so you should stay at 11 untill FMP12 v2 perhaps or wait 13.

as for files, I'll continue to have seperate files, but work in one only : one big file on which you put all the other files as reference (so you'll buil the relationship in that file), so you'll have the est of the 2 worlds, one code for all, but less risk of data corruption

[Ocean West]

granted that a converted solution may not perform as well - especially one that has been around for 13 years - but a freshly developed one in FMP 12 containing NO legacy gremlins should be pretty solid -

Waiting for 13 is just a tad bit conservative - because that argument would be the same when 13 came out (wait for 14) .... between 11 and 12 its been 25 months. I would anticipate that a vRev will address major issues with in a few months.

One issue with multiple files is permissions making sure that the permissions are set in all files unless you have a hosted solution and are using External Authentication, then you are managing groups instead of individuals.

[RUBlind]

Thank you both for you responses.

I am positive I want to start a new version using FM 12. As Ocean West said, it will clear out some gremlins (I like that term)

As for permissions, that is a very big deal. There is just 1 password for FULL access that I use and one other password for all users.

I am not sure what to make about the data corruption... I have not really ever lost data with FM. The recover utility has always done fine if the file was close improperly.

Ocean West: I do like the idea of one file, but do you think it is fine handling 15000+ records in one file???

BTW: I am not waiting for version 13 - I will start to build it with this release and I am sure the bugs will get fixed, but covert my old version to FM 12 is not an choice for me.

Time to start fresh!

Thanks again to both of you for the replies... Anyone else have an opinion???

Robert

[Ocean West]

Robert, there is no issue with 15000 records - many have database with over a million records.

[Tom R.]

Hi Robert -

Your project sounds like an interesting one. I don't know how much time you have to devote to the project, but you might consider signing up at filemakermagazine.com, which has screencasts of FMP tutorials. They are all mostly between 20-40 minutes, and I've gotten into the habit of watching a video on my lunch break 2 or 3 times a week. What I've learned there is easily worth the $25.00 per quarter subscription fee. I've read a couple of books and learned a fair amount, but find I understand things better when explained by an expert (either in person or via screencast).

The project I'm working on is based on a single file (but multi-table) solution that's about 6 years old. Because I want to deploy it in a multi-user environment, I decided to base it on the Data Separation model, where there is one file for data only, and a second interface file with user layouts, scripts and so forth. That way, you can work on the user interface part of the solution and when you have an update, you just give the new interface file to the users and you don't have to take the whole system down or worry about importing data from the old version to the new version of the database.

[Vaughan]

Multi-user alone is not a good reason to use the separation model.

Separation is useful for *multiple deployments* of the solution: that is, you've sold the solution to multiple clients who each run their own version. Separation model makes performing updates to the interface simpler because you swap out the interface file and leave the data file alone, thus no need to do imports or data migrations. (Assuming the interface file has absolutely no client data in it including custom value lists etc.)

If the updates include frequent changes to the data file then there is NO advantage to separation, in fact the separation model is less efficient than using a single file.

[Rick Whitelaw]

Genevieve,

I understand what you're trying to say regarding keeping separate files but only working on one, but this ends up with less normalization. To maintain speed advantages it's often necessary to open the external files and make changes on them. Otherwise I would recommend merging the tables from all separate files into one file. It might be a performance hit. I'm not sure.

If a person is going to work only on the main file, I.e. refer to tables from external files and not make changes to the external files, I think a single file solution is preferable. By the way, I run my business on a complex 7 file solution.

RW.

[RUBlind]

Thanks All for the many replies. I am getting a lot of ideas on which way to take this development

As for the separation model talked about above, that doesn't really apply to me because I am usually not just making interface changes. If I am adding something new to the system, it will involve at least 1 new field or calc or layout etc...

Right now I am leaning towards a 1 file many table solution.

The only thing I lose by doing that is my ability to fix something in one file like bugets.fp7 and email the fix to a user.

By not offering this FIX anymore it will require the user to bring me his/her laptop so I can update the file. MOST of my users do this already and this will just FORCE a few to have to do it if they want the problem fixed.

Thank again for the replies everyone!

[RSGC]

By not offering this FIX anymore it will require the user to bring me his/her laptop so I can update the file. MOST of my users do this already and this will just FORCE a few to have to do it if they want the problem fixed.

One if many sites that might eliminate some running around with computers:

https://join.me/

Bob

[bcooney]

"By not offering this FIX anymore it will require the user to bring me his/her laptop so I can update the file. MOST of my users do this already and this will just FORCE a few to have to do it if they want the problem fixed."

Is hosting not an option? Apparently, each user has a copy of the file on their laptop? How do they backup? You could use a hosting service.

Hosting would allow you to make a change to one file and everyone has the change. How else would you manage fixes? Also, they could connect using FMGo--no demand for iOS access...yet?

btw, I vote for a single file, multi-table solution.