I am looking for ideas/guidance on developing a flexible, extensible database schema for a sales reporting system. Any ideas on this would be appreciated. The requirements are as follows:

Background and requirements
BACKGROUND
1) My client has an existing accounting system that keeps track of sales.
2) For a given sale there may be the following conditions associated with it:
a)Invoice date b) Item number and/or item product line c)Target Market that product was sold to d) salesperson, salesmanager who sold the product
e) any other data conditions associated with the sale that they may want to track in the future
REQUIREMENTS
1)The client wants to compare actual sales against predefined goals and report on it.
2) Currently the majority of the reporting is based upon a fiscal year's worth of data, e.g. jan06 sales goal is a feb 06 is b. march 06 is c,
quarterly goal is a+b+c. yearly goal is (a+b+c)+(d+e+f)+(g+h+i) + (j+k+l)
weekly goal is aw1+aw2+aw3+aw4
Goals are a) companywide b) per sales person c) per salesperson/per product line e) per salesperson/per productline f) per salesperson per product or product line/per target market

It is straightforward to hardcode A FIXED SET OF sql statements to give them reporting based on the yearly sales, broken out by month, or even further.

BUT - what is not so evident to me is how to design a system that is flexible and extensible for any scenario. I want to define a set of data driven 'sales goal records', but, not only that I want to be able to express the relationship between the records via some sort of parent child relationship or linked pointers. I am struggling with trying to come up with a flexible schema to do this.

Here's an example of what I've got so far:

PARENT RECORD for years worth of data

GOAL ID SALES GOAL PARENTID (PLANNED)
CAROL_2006_ALL_PRODLINE_X 1000000 NONE (??)
(sales goal for this record could be left null - just the sum of the child records - or stored, and checked, i.e. that the sum of the children = the parent.)



sales goal header record:
GOAL ID SALES GOAL PARENTID (PLANNED)
CAROL_2006_01_PRODLINE_X 100000 CAROL_2006_ALL_PRODLINE_X

sales goal detail record:
GOAL ID CONDITION OPERATOR VALUE
CAROL_2006_01_PRODline_X INVOICEDATE >= 20060101
CAROL_2006_01_PRODLINE_X INVOICEDATE <= 20060131
CAROL_2006_01_PRODLINE_X SALESPERSON = CAROL
CAROL_2006_01_PRODLINE_X ITEMID IN SELECT ITEM FROM PRODUCT LINE TABLE

I wrote code to loop through this table and define a where clause to retrieve the appropriate records from the invoicing system. This is pretty flexible.

The part I am struggling with is how to define relationships between goals, so I don't have to 'redefine' goals that are subsets of another goal.
So the yearly goal would be the sum of the 12 monthly goals.
(actually I would have to string together the monthlies by building a where clause that "or's" them together) The quarterly would the combination of three monthlies.

Tentatively I am thinking that I just need the parent ID column in the sales goal header record.. trying to look at how a dbc is constructed.. and see what type of relationships that expresses.. and whether it would be applicable here. So far I've only got what I have above less the parent concept.

All ideas welcomed!

Thanks!