Table Design Suggestions

[randerson]

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:

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

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

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)

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

[tamilselvan]

In oracle, this can be easily done.

The Fund table looks like:
FUND_NAME varchar2(25)
PARENT_FUND_NAME varchar2(25)
rest of the columns.........

And CONNECT BY PRIOR will give you the hierarchical structure.

Tamil