Can anyone offer advice on building large database systems with FileMaker? By large, I'm talking about over 100,000 records to begin, climbing to a million over the next few years. The largest system I've built before this was about 30,000 records, and I'm wondering what issues are going to come up with this extra level of magnitude. Any advice or tips would be appreciated, such as pros and cons of archiving, speed issues, replacing data vs. looping data, etc.

Thanks,

Chuck

Get a really, really fast hard drive.

Seriously, when you're talking 100,000+ records, there are other DB software packages that work faster (searches, indexing, replacing, etc). While they won't be as delightful to program, they are designed to be more robust.

Even to Filemaker 100,000 records is NOTHING. The trick is to work with found sets. The default in Filemaker is all records, quit using that paradigm. Every other RDBMS explicitly works with found sets (Queries) only, and functions are almost never allowed on the entire database.

Indexed finds are fast, as is GTRR, regardless of the total number of records in the DB.

I would basically start looking elsewhere only when you get to the point of 1,000,000+ records and growing to 10s of millions, or when you need more than 250 simultaneous users accessing the same files.

To piggyback on Capt Kurt's post, eliminate every calculation field you can and replace it with a script. Avoid summary calculations, especially. Doing such provides you with exquisite control over when and what gets updated, and permits you to index more fields for quick searches and sorts. And as Kurt noted, make good use of going to the related records instead of using Find.

Work only on clones with a small amount of data when you tweak your system, unless you like waiting for a single change to a calc to chew up 20 minutes while the entire system updates.

Think pure thoughts and put your self in the hands of the Attorney General.

Many of our databases exceed 50,000 records and we have no problems - as CaptKurt described it's how you use it.

If you are expecting large amounts of data make sure you have a good backup system in place - CD's and DVD's take too long for daily use and don't have the capacity ...... this of course would still apply whichever RDBMS system you chose to use.