> > All other figures should ...
> Please explain further. How many other figures?
Based on the current design status, the database will have to provide about 1500 figures, most of them calculated, i.e. non-basic figures. Most calculated figures actually base on other calculated figures, so you could think of it as a hierarchy of figures, or more precise: a figure-dependancy hierarchy.
The main idea behind the setup is: Basic figures such as costs and profits on a very low organizational level are aggregated to more higher-level calculated figures which again are aggregated to even higher levels. (By the way: The highest dependancy hierarchy depth is around 7 or 8 IIRC.) Unfortunately, lots of those calculations/aggregations (or 'calculation rules' as I call them) involve conditions and cannot be calculated with 'normal' SUM-like aggregate functions. Besides the examples in my original post, another typical example for a figure calculation rule would be: "if administrative costs for sale are not provided, use 20% of the total administrative costs for further calculations" or:
actual_sales_admin_costs = IIF(sales_admin_costs IS NULL, 0.2*admin_costs, sales_admin_costs)
Concerning the number of basic figures: I'd have to look up how many of all figures are actually basic figures, i.e. figures that don't need a calculation rule. But I think there are "only" about 300 of them.
> Will you use any of them in WHERE clauses? Could
> they be thrown into a single BLOB column of 'other
> stuff'? (I recommend JSON; XML is another
> possibility.)
Yes, I have to filter out figures with a NULL value quite often. I also have to use ORDER BY quite a lot. So just dumping them into a BLOB wouldn't work for me.
And wouldn't it make actually harder to implement those calculation rules?