| Test 4 [message #219497] |
Wed, 14 February 2007 11:36  |
randerson@viops.com
Messages: 8
Registered: February 2007
|
Junior Member |
|
|
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).
[Updated on: Wed, 14 February 2007 11:37] Report message to a moderator |
|
|
|
| Re: Test 4 [message #222354 is a reply to message #219497] |
Sat, 03 March 2007 04:39  |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
This is a TEST forum. Forum members usually don't pay much attention to messages people post here.
Your message seems to be a "real" one. Do you want us to transfer it to another place (where it will be noticed and, probably, answered) or was it really just a test message?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
