Table Design Suggestions

I would like to request some design assistance/recommendations. We are using Oracle 9i (soon to go to 10g) with both a Delphi Client/Server front end and a VS .Net Web Application. Here is the scenario:

We have a contracts application used by the Air Force and they need to add much more detailed funding data about the contracts. Basically a contract can be funded by one or more funds types (imagine different checking accounts). These funds types can be broken out in a hierarchical manner. Here is an example:

Code:

Parent Fund Child Fund1 Child Fund 2 Child Fund 3 AFCEE AF BRAC Overhead Technical Assistance Program Management AF BRAC Project Base ABC Base DEF Base GHI AF Restore Act ERPIMS Technical Assistance Air Force 11WG AF O&M BA01 BA02 AF BRAC Project Base ABC Base DEF Base GHI ACC AF O&M BA01 BA02 BA03 BA04 AF BRAC Overhead AF BRAC Project Base ABC Base DEF Base GHI

Using the information above, Contract XYZ can receive Technical Assistance funding from the AFCEE/AF BRAC Overhead funding pool and also from the AFCEE/AF Restore Act funding pool.

When the Project Manager enters the data, initially they may only know they are getting AFCEE funds and later on find out exactly what kind it is. Also Sr. Management is going to want reports that aggregate the dollars by fund type and as you can see in the example above, there are 3 AF BRAC Project funds types each with a different parent. I am going to meet to get a few more details on the Sr. Management report requirements. By the way, there are just under 3000 fund type combinations currently identified but it could easily go over 5000.

I am considering two possible table designs for the lookup table to store this information.

DESIGN 1

Code:

Fund_Type_Seq NUMBER PK Fund_Type_Code VARCHAR2(25) Not Null Parent_Fund_Type_Code VARCHAR2(25) Null Fund_Type_Desc VARCHAR2(500) Null

DESIGN 2

Code:

Fund_Type_Seq NUMBER PK Fund_Type_Root VARCHAR2(25) Not Null Fund_Type_Child1 VARCHAR2(25) Null Fund_Type_Child2 VARCHAR2(25) Null Fund_Type_Child3 VARCHAR2(25) Null

DATA TABLE DESIGN (Design of table holding Fund Type data for a contract)

Code:

Contract_Seq NUMBER PK Fund_Type_Seq NUMBER PK Funded_Dollars NUMBER(12,2) Null And a couple other columns always in data tables in our system.

The reason for the DESIGN 2 is that from a UI perspective, just showing “AF BRAC Project” 3 times without showing its lineage (so to speak) will not be useful to the PM and I am not sure of how to display a fund types entire lineage using design 1 (Possibly using a tree view and using the Connect By Prior / Start With SQL command structure).

So...Any suggestions, recommendations (and telling them that they are insane, while tempting, is unfortunately, not an option).

Thanks
Richard Anderson