Table Design Suggestions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Table Design Suggestions

  1. #1
    Join Date
    May 2005
    Posts
    3

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width