Database construction Theory Question

[Baylah]

Hi all,

I have been using FMP for many years, and am self taught to what I always though was at least an advanced level. Recently I started studying for the FMP Certification exam to lend some credibility to my credentials and I found out that in my DataBase Construction Models I may have been doing something fundamentally wrong, all along. But, I am not sure if it is just that there are always at least 2 ways of doing something, or if I am just flat out wrong in my thoughts.

To start I have to admit an embaressing fact. Until very recently I did not know that you could have multiple tables within a single file. No laughing please! Whenever I have had a multiple table Database I always set up seperate files and then made all of the relationships accordingly.

My question is, that has always worked, so was it wrong? Stupid? Sloppy? Uninspired, or just another way of getting to the same reult?

Is there a reason that it is superior form to have seperate tables all residing in the same file architecure?

One thing I have continuously learned with FMP is that the more that I learn, the more I realize I have to learn. I have developed many databases where the functionality all works seemlessly, but I bet that if a professional developer looked at my structure, he would chuckle and wonder what the heck I was up to! One of the ahzards of being completely self taught.

Thanks for any inspired insight.

Steve (humbly studying for the FMP Test) Freeman

[The Shadow]

That feature was new to FM7, previous versions required you to use different files - one for each table.

By keeping some tables together, you simply the design of your solution - for instance, you only need to setup user accounts and privileges once for each file. If your solution is small enough to fit into a single file, then it becomes much easier for your users to install and use it.

[Baylah]

<<That feature was new to FM7, previous versions required you to use different files - one for each table.>>

That makes me feel somewhat better! I didn't think I had ever seen that functionality in earlier version. I started with FMP on version 4. As I have developed new solutions in 7, I never went backwards, so I just kept designing new structures that way I always had.

As I was stumpbling through one day, I ran across the multimpe tables and relizaed that my many file systems could be contained in one DB. What a concept! What I wonder now is if I will continue to develop as I have in the past or build solutions around the new architecture. It will probably end up being a combination of both.

Thanks,

Steve

[CobaltSky]

Hello Baylah

There are many new features in FileMaker 7, some are subtle refinement, while others are a radical rethinking of the way the application works.

One thing is for sure. If you keep working exactly the same way as you have done in previous versions, your solutions will not be optimal and will not make best use of the power and the rich featuree set available in the current version.

I'd encourage you to explore the documentation and the application afresh. You'll find some other surprises in there that are at least as 'interesting' as the new data architecture.

[Inky Phil]

Hello everyone,

Sorry to jump in here but I am also currently converting my brain from 5 to 7.

I have a load of questions about best practice in 7 but the first one I have to face is 1 file or many. The documentatation says I can have as many tables as I like in 1 file (within size limitations). My solution could certainly be contained within the 1 file. Other than the protection of data in a crash is there any other reason for not keep everything within one file?

Apologies if this is either too simple or indeed too complex to be covered here

TIA

Phil

[Ender]

There are some good reasons not to put your entire solution into one file. Two that come to mind are modularity and maintenance.

Modularity meaning having the ability to add or change a whole module at a time. This might be especially useful if you wish to sell solution modules to your clients in pieces. Maybe they need an order tracking/inventory system now, but want the ability to add a contact manager in a year. Or you might upgrade an entire module at once. For a large solution, it would be easier to swap out the module's one file with 5 tables rather than the entire solution's one file with 50 or 100 tables.

For smaller solutions, maintenance may not be a big issue, but if you have tables with hundreds of thousands of records, then there are several maintenance steps that are easier with separate files. Namely purging large data sets. If you wish to purge a lot of old records on an annual basis, this is usually faster to do by cloning the file and importing the records you wish to keep. Deletion of large numbers of records tends to take a long time. Another maintenance step that you might wish to do only on specific tables is File Maintenance (available in Developer,) a tool that compresses the file and/or optimizes the file. This may only need to be done on tables where there's a lot af activity (adding and deleting records.) It may take a long time to run File Maintenance on an entire one file solution.

I also seem to recall Harry Glos noting some performance issues on his one file solution verses his modular solution:

http://www.maclane.com/ubbthreads/showflat.php?Cat=0&Number=496925

The FM7 Foundation and Migration Methodologies tech brief also had some discussion about this topic.

[chemparrot]

I have found the TechBrief "Migration Foundations and Methodologies" to be very helpful. You can download the PDF at

http://www.filemaker.com/downloads/pdf/techbrief_fm7_foundations.pdf

[Inky Phil]

Thanks Ender and chem

Phil

[Baylah]

Thanks for all of the good information! I have begun going back through many of my old practices and have found some amazing new tidbits I was not aware of.

At times I ahve found the version upgrade a little sloppy and cumbersome, but once some of the tweeks are worked through it is amazing rewrite and add much more depth to an already excellent application.

Steve

[The Mad Jammer]

One other point that I would like to make. FileMaker server can only host 125 files at a time. After that you have to buy another FMP server license. Now that FMP 7 allows you to have all of the tables inside the file, your are able to host more applications under the same server. Where I work this is particularly cogent because we have a lot of departments that all want their own referral system. This usually means that there are anywhere from 3 - 7 files that need to be created for each system, depending on the users needs.

If each user needed 7 files, the most departments that can be hosted with one FMP Server is 17, with a couple of files left over.

We have maxxed out the FMP server file hosting allotment on one box already and are pushing the 125 file limit on the second box. Both are FMP Server version 5.5 but FMP server ver 7 has the same limit on file hosting. I am administering over 50 departments on these 2 boxes and there are dozens more departments that would like to have their own customized referral system built in the future, so you can see how having all the tables in one file would be helpful as far as FMP Server hosting is concerned.

I agree with Ender about the modularity issue and this speaks to the business model one is pursuing. The referral system solution I explained above is basically self contained and I can upgrade the files on the fly at any time. But sending out updated modules for installation is another thing entirely.

The Mad Jammer