Invoices database structure

[Melch]

I am creating an invoicing database and am being told that in order to keep the database from becoming too unwieldy and to keep reporting fast it is best to have an invoices database and a separate line items database underneath it.

The issue that I am having is that all of the information we need for reporting is all held in the line items database, so despite setting up the invoicing databases in a hierarchic structure, we still have to run reports on every single line item, which doesn't seem like it would cut down on run times for reports.

On top of that, with the product that we sell, it makes the most sense for each invoice to contain only one line item, because each product (i.e. line item) is delivered at a different date, and therefore payments are due per line item instead of per invoice. That being the case, it seems like it would make the most sense for each invoice to contain its own line item and to then get rid of the line items database altogether. Then the reporting would be run out of the invoices database, which would be equally slow as if we had to run them out of the line items database, so thats not really a loss. But what we would gain is an immense amount of simplicity for both our sales reps and our clients.

What I am looking for are any reasons why it would be a problem to structure the program with essentially a single invoices database that is basically a flat file with one invoice per sale. Right now we are creating about 200 invoices a month, and I do not perceive that going above 1000 any time soon, so that means that if we are archiving stuff over 3 years old, the max number of records to be running reports on is about 36,000 (probably will be closer to 15,000 realistically). Is that enough records to make filemaker start choking when running reports.

Thanks in advance for any input!

[cjaeger]

if this is your company's way to write invoices - fine with me. Should be faster than having to pull the data from a line item file. However, depending on your report needs, you may add a "virtual" line items file, that is a relation to the invoices file based on item# or SKU. 500,000 records should be no problem, unless you choose to include tech drawings directly in the invoice file - they should go elsewhere in related files.

This assumes you have a fairly recent computer (P3,G4), enough memory(256 MB+) and a fast hard disk.

If for some reason you decide to combine several items into a product, you can always go back to the hierachical format.

I know someone who puts the line items into repeating fields, which is really no problem as all the aggregate functions, summaries and relations work on repeated fields equally well. I would not recomment it, but it works and still is backward compatible to their original database made with FileMaker 2 a decade ago... (structurewise, as they use FM 5 now...)

[cjaeger]

if this is your company's way to write invoices - fine with me. Should be faster than having to pull the data from a line item file. However, depending on your report needs, you may add a "virtual" line items file, that is a relation to the invoices file based on item# or SKU. 500,000 records should be no problem, unless you choose to include tech drawings directly in the invoice file - they should go elsewhere in related files.

This assumes you have a fairly recent computer (P3,G4), enough memory(256 MB+) and a fast hard disk.

If for some reason you decide to combine several items into a product, you can always go back to the hierachical format.

I know someone who puts the line items into repeating fields, which is really no problem as all the aggregate functions, summaries and relations work on repeated fields equally well. I would not recomment it, but it works and still is backward compatible to their original database made with FileMaker 2 a decade ago...

[Ugo DI LUCA]

Hi,

In fact what you need to do is get rid of the Invoice File and work in the Line Item Invoice File.

As all the Invoices comes from that file, reporting should be OK.

And when you will need to add a line for your Invoice, you will re-crate your Invoice File without getting rid of the Line Item.

Now, because there are lots more of sense to use a line item, and very little probability that your business wouldn't evolve, I'd personnaly keep the line item.

Finally, what exactly do you mean by "setting up the invoicing databases in a hierarchic structure".

Are there previous steps before the Invoice ?