Preserving invoice client history...archiving? duplication?

[Jean-Philippe Valois]

Hello all,

I am somewhat new to FM, but I do have intermediate database experience. I am currently using FM10 to implement a simple invoicing solution.

My question is: what would be the best way to preserve an invoice's related client data once the invoice is closed (billed, archived)? For example, if the client's address changes, I'd like to keep a record of where that client was located when the invoice was issued.

I guess this might be a broader data archiving design question, but for a very simple system, what would you do?

I guess there is some sort of industry standard way of doing this? I am thinking of two solutions : A) keep all client records in the same table and mark the "historical" ones as such (hiding them from the client management form) or copy "current" "client" data to a "client archive" table (new table would have same format a client table).

The idea of a huge flat invoice table also crossed my mind, but I'd prefer avoiding that (would seem awkward).

Technically, implementing solution A would seem the easiest to me (only need to duplicate record), but solution B, if not for the ugly duplicated table, seems cleaner. However, if solution B is recommended, I'd love some pointers on how to copy data from table to table (import into table? temp variables?).

Thanks for any help (and a *huge* thanks to all FMForums members for being such an invaluable resource).

Jean-Philippe Valois

[bruceR]

Typically there isn't even a question of this being a problem.

The invoice data is populated using lookups or auto-enter calcs, and remains unedited after the invoice is closed.

Which brings up the question of your design. Can you post a copy or clone?

[Jean-Philippe Valois]

Hello Bruce,

Thanks for your reply. Are you saying that you would simply duplicate all client data directly into the invoice? Then I'd have to do the same with all other related invoice data (there's lots of it), which would bring me back to a huge flat invoice table (which I was hoping to avoid).

For the sake of argument, my design can be simplified as one invoice table and one client table. Currently, invoice data does not include any kind of client data, hence my concern for keeping a trace of it somewhere.

Regards,

JP

[Jean-Philippe Valois]

I've been reading some more on DB design topics and came across this Technet article (database denormalization) on this exact question.

They recommended denormalizing all client data into the invoice...

jp

[bruceR]

Hello Bruce,

Thanks for your reply. Are you saying that you would simply duplicate all client data directly into the invoice? Then I'd have to do the same with all other related invoice data (there's lots of it), which would bring me back to a huge flat invoice table (which I was hoping to avoid).

For the sake of argument, my design can be simplified as one invoice table and one client table. Currently, invoice data does not include any kind of client data, hence my concern for keeping a trace of it somewhere.

Regards,

JP

Have you looked at the Invoice example file that comes with Filemaker? Yes, I see the article you link to show the usual design. Hardly seems flat.

[Jean-Philippe Valois]

I'm afraid I might have misused the term "flat". My current design is not flat at all. It very much looks like both the one illustrated in the Technet article mentioned above as well as the one which appears in the FM11 sample invoice database.

As a matter of fact, the FM11 invoice sample precisely illustrates the problem: No client data is saved inside the invoice table. Therefore any change that is made to a client record will immediately show up as a change in every single existing invoice...

So the best solution I see, as recommended in the Technet article, is to A) keep current client data in a nicely normalized client table but , denormalize all required historical client data into the invoice table.

jp