I am in the beginning of designing a fairly complex system for our business. We’re in the typical 3-ring circus of having a database for our clients, excel spreadsheets for our sales numbers, and then an accounting program to track invoices/receivables.

I am trying to consolidate this process to one central database, and I plan on using the many-to-many model. In other words, invoices would be related to clients, and each invoice will have related line items, and then each sale/invoice will be summarized to it’s respective job (each sale is part of a unique job that we set goals/expecations, and monitor if it’s being exceeded or underperformed)

My concern is the volume of records I would be generating. We average around 8000+ invoices per year, and if each invoice would have 2-3 additional related records to display line items via portel I’m concerned about bogging down the program. Also, each invoice will be associated with around 100+ unique jobs per year, which would be summarize related records associated with that job.

On top of this, we have around 12,000+ records in our current database of clients, which I also want to summarize with their related invoices/sales/jobs.

How I am currently planning on setting up this database is to have a table for clients, a table for sales and a table for jobs. I would set up unique records for jobs, and those records would relate summarized data from the sales records (to track performance) and relate records from the client table to track customers

Any advice would be greatly appreciated on design considerations with the volume I’ll be generating. The last thing I want to happen is invest all this time making the relationships and records talk to each other, just to find out it takes too long to summarize any records. I would also appreciate it if anyone has had prior experience with relating large amount of records to mention what kind of performance problems you’ve encountered.

Thanks

Millions of records and terrabytes of data are no problem, as long as your fundamantal data design is good. (This applies to all database systems not just FMP.)

Host the solution with FM Server on a dedicated server-class box running a server OS (ie, not Win XP Home).

That's good to know it can handle millions of records. We currently host filemaker on a dedicated 10.4 server box, but I'm still concerned about having the ability to finding & summarizing just the related records to a unique job or a unique customer. In other words, can it quickly summarize just 1 invoice record through a portal to 1 unique customer when there is over 10000 clients and 10000+ invoices? Or how about summarizing 4 unique invoice records spread sporadically through 10000+ invoices?

And finally, what about listing all 100 unique jobs for the year, can those records all show the summary for all 10000 sale/invoice records related to each job record, and show them to them in a table all at once?

Am I to be concerned about any other performance implications?

Thanks

All of your concerns depend on how efficient the underlying data sturcture is.

FMP has no problems finding small sets of records from large tables. Again, the speed of the process depends on how efficienly the process has been designed.

Sorry I can't be more specific.

Hello Vaughan:

My solution is getting slower and I suspect that awkward design is the problem. Could you be so king to give some guidelines on the pitfalls to avoid or point me to some documentation.

Thanks

Edited July 27, 200718 yr by Guest

Much of it is understanding about normalisation: on the most simplistic level, normalisation reduces duplication of data. But it does more than that: it allows an enormous amount of control and organisation, it enables "a place for everything and everything in it place" design. Done correctly, the database will refuse to allow anything to be out of place; and everything you need it just a relationship ayaw.