2 Replies Latest reply on Jan 30, 2012 10:36 AM by techt

    Design Decision - single or multiple tables?


      I am developing a reporting system for a restaurant company. One of the functions of the system will be sales reporting. I am trying to decide if I should have a single monolithic sales table or break the separate units out into separate tables.


      Some of the considerations:

      • Currently 8 locations
      • Sales are imported from the POS system as individual checks, approximately 300 +/- checks per location/day
      • Checks contain information on diffferent cost centers, payment types, etc. that will be reported on


      There are 2 initial primary uses of the system:

      • Reporting sales on a daily/weekly/monthly basis by location as well as by region or in total.
      • Reporting different types of sales (catering vs. in-house dining) for the same criteria as above.


      I started down the path of a single table but am now having second thoughts and can see pros/cons of both strategies.


      Any and all thoughts are appreciated!



        • 1. Re: Design Decision - single or multiple tables?

          A single table is usually your best bet if the info is for the same thing.  Having multiple Sales tables is usually more work, and leads to chaos.


          I would go with a single table.  You can use finds to filter down to the data you want in the report.  And sub-summaries to break out the various categories/time periods.


          Another option is to use the Virtual List technique.  Which I personally love and us often, but it will depend on your level FileMaker expertise and experience.  Not that it is all that diffifult anyways.

          • 2. Re: Design Decision - single or multiple tables?

            While not impossible to report from multiple tables, I've always found it easier to report from a single table, especially if all of the data (fields) are identical. Plus, management of value lists and other controls would be easier, as well as potential navigation and import issues. My experience has been that as soon as they start looking at the reports you can generate, they always want more, and a common follow up to any of that is comparative - they'll want to know how one location fares against another.


            At ~300 records per location per day, you'll be looking at 876,000 records in a year. FMP currently support 64 quadrillion records, so you probably don't need to worry to much about that, but file performance & maintenance might become an issue in a few year.