Now comes the moment where I get confused by the choices: This process will generate 50,000+ invoices per year. Nobody wants a to browse a folder with 50,000 entries. Can I reasonably use transparent folders to group all those invoices by day, week, month, and so on into a tree heirarchy? No doubt it could be done, but should I?
Probably not, but I wouldn't want to browse a SQL database of 50,000+ entries either. The structure sounds fine, the problem is the presentation method. Take the records and use ZCatalog to do the presentation.
Would the invoices still be 'as one collection' (thanks to the transparency of their grouping folders) if I wanted to write python scripts joining Zcatalog results that took the place of my current sql queries? Sometimes you're thinking of the invoices as grouped by dates, other times you're looking for the aggregate of other properties, such as CustomerID. I need to be able to create things like "show all year 2001 invoices for customers in the 90210 zip code" as well as, "Show the average invoice amount for the the top 10 customers."
Ugh, this might be a killer because that sort of thing can be hard to do in ZCatalog, check that you can do all the queries you want before you go down that path. -- Andy McKay