I need some EXPERT opinion!
I have a general FM 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 know 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!